Re: Making the most of memory?

On Jan 22, 2008 11:11 PM, Joshua Fielek <jfielek(at)centriccrm(dot)com> wrote:
> The database itself is not that large -- a db_dump of the sql file as
> text is only about 110MB. I haven't checked the exact size of the actual
> data base, but the entire data directory is smaller than the available
> memory at about 385MB including logs and config files. This is a single
> database with a relatively small number of client connections (50 or so)
> making a fair number of smaller queries. This is not a massive data
> effort by any means at this time, but it will be growing.
>
> We have available currently ~4GB (8GB total) for Postgres. We will be
> moving to a server that will have about 24GB (32GB total) available for
> the database, with the current server becoming a hot backup, probably
> with slony or something similar to keep the databases in sync.

The database is cached in RAM. As soon as the database files are read
for the first time, they will stay cached in the o/s basically forever
(in either o/s file cache or postgresql buffer cache) as long as there
are no other demands on memory...not likely in your case. This also
means extra ram is not likely to help performance much if at all.

I'll give you a little hint about postgresql.conf...tuning shared
buffers rarely has a huge impact on performance...the o/s will

possible issues you might be having:
*) sync issues: asking drives to sync more often they can handle.
possible solutions...faster/more drives or ask database to sync less
(fsync off, or better transaction management)
*) cpu bound issues: poorly designed queries, or poorly designed
tables, bad/no indexes, etc
*) unrealistic expectations of database performance
*) not maintaining database properly, vacuum, etc
*) mvcc issues

maybe post your transaction load, and/or some slow queries you are dealing with.

merlin