Re: Question on hardware & server capacity

On Fri, 3 Jan 2003, Steve Wolfe wrote:

> > Have you optimized your queries to max ?
> >
> > Often one or two of the queries take most of resources and starve
> > others.
>
> I did log a good number of queries and analyze them, and 69% of the
> queries issued are from one particular application, and they consume 78%
> of the total "cost". The developper is looking into optimizations, but it
> doesn't look like there's going to be any low-hanging fruit. It's simply
> a complicated and frequently-used app.
>
> > Could there be some unnecessary trashing between OS and PG caches ?
> > How could this be detected ?
>
> The machine generally has a minimum of a hundred megs free, unused
> memory, so I'm not terribly worried about memory thrashing. I've
> increased the various tuneable parameters (buffer blocks, sort mem, etc.)
> to the point where performance increases stopped, then I doubled them all
> for good measure. I've already decided that the next machine will have at
> least 4 gigs of RAM, just because RAM's cheap, and having too much is a
> Good Thing.

Actually, free memory doesn't mean a whole lot. How much memory is being
used as cache by the kernel? I've found that as long as the kernel is
caching more data than postgresql, performance is better than when
postgresql starts using more memory than the OS. for example, on my boxes
at work, we have 1.5 gigs ram, and 256 megs are allocated to pgsql as
shared buffer. The Linux kernel on those boxes has 100 megs free mem and
690 megs cached. The first time a heavy query runs there's a lag as the
dataset is read into memory, but then subsequent queries fly.

My experience has been that under Liunx (2.4.9 kernel RH7.2) the file
system caching is better performance wise for very large amounts of data
(500 Megs or more) than the postgresql shared buffers are. I.e. it would
seem that when Postgresql has a large amount of shared memory to keep
track of, it's quicker to just issue a request to the OS if the data is in
the file cache than it is to look it up in postgresql's own shared memory
buffers. The knee for me is somewhere between 32 megs and 512 megs memory
to postgresql and twice that on average or a little more to the kernel
file caches.

> Yes, it's BAD if your business grows faster than Moores law ;-p
>
> .. unfortunately, that's been the case. Each year we've done slightly
> more than double the traffic of the previous year - and at the same time,
> as we unify all of our various data sources, the new applications that we
> develop tend to make greater and greater demands on the database server.
> There is always the option of the "big iron", but your
> cost-per-transaction shoots through the roof. Paying a 10x premium can
> really hurt. : )

Can you distribute your dataset across multiple machines? or is it the
kinda thing that all needs to be in one big machine?

Well, good luck with all this.