Tuning for PostGreSQL Database

Terry Lambert tlambert2 at mindspring.com
Fri Jul 25 00:15:28 PDT 2003

Christopher Weimann wrote:
> So if PostGreSQL uses fsync when writing ( which I think is only true
> on the write-ahead logs at this point ) that data will NOT wind up in
> the cache.  Anything that PostGreSQL reads should wind up in the cache?

No; fsync'ed data pages are in the cache as clean pages containing
data, until they are inactivated, or until you madvise them away,
etc..  One common practice is to madvise(DONTNEED), which basically
moves the pages to the other end of the LRU, so that they will be
stolen and given back to the system first, rather than putting them
on the LRU in access order, which means other pages would be at risk

Probably reading the decriptions in the madvise man page would be a
useful exercise for you, since it talks about the states that the
vmobject_t's and pages in them can be in at any given time, with
specific regard to FreeBSD.

As I said before, there's really no good documentation, except the
source code, and to undestand what top's numbers means, you have
to understand how top gets those numbers in the first place, then
you have to reverse the math to get the statictics values it's using
and their effect on the values displayed by top, and then you have
to look at the VM code that changes each of those statistics (in the
"vmmeter" structure) to see what conditions cause a state change on
a page.

Short of writing better documentation, that's about all I can tell
you about it without defacto writing better documentation.

The problem with writing better documentation is that then some
idiot will understand the code, and, understanding it, either fix
or try to fix any shortcomings described in the documentation, and
fail to update the documentation in the process, at which point
the documentation will be near-instantly out of date.

It's like comparing Linux and FreeBSD: as soon as you do a valid
comparison which honestly shows a difference between the two, an
army of people who don't value accurate documentation rush out to
correct any discrepancies in performance, and then a week after
you've published your comparison, someone's trying to blow up
your car for publishing "lies" about their favorite OS ("That's
not true!  You're a disreputable scoundrel!").

> last pid: 58622;  load averages:  1.96,  1.67,  1.47  up 2+12:59:15    13:31:39
> 130 processes: 6 running, 124 sleeping
> Mem: 348M Active, 2628M Inact, 438M Wired, 155M Cache, 380M Buf, 76M Free
> Swap: 4096M Total, 28K Used, 4096M Free
> The 2628M Inact is likley to be acting as cache?
> Basically I should stop worrying about this :)

Or go read the top sources and the VM code, yes.

-- Terry

More information about the freebsd-performance mailing list