From: Josh Berkus <josh@agliodbs.com>
To: michael.mattox@verideon.com
To: pgsql-performance@postgresql.org
Subject: Re: Performance advice
Date: Tue, 24 Jun 2003 09:04:35 -0700
Message-ID: <200306240904.35923.josh@agliodbs.com> (raw)
In-Reply-To: <CJEBLDCHAADCLAGIGCOOGEJLCKAA.michael.mattox@verideon.com>
References: <CJEBLDCHAADCLAGIGCOOGEJLCKAA.michael.mattox@verideon.com>
Micheal,
> I changed the postgres.conf settings as suggested by several people. I've
> attached it to this email, please let me know if you see anything else I
> can tweak. top still says I have plenty of ram, so should I increase the
> buffers and/or effective_cache even more?
Effective cache, yes. Buffers, no. Even if you have RAM available,
increasing buffers beyond an optimal but hard to locate point decreases
performance. I'd advise you to start playing with buffers only after you are
done playing with other memory-eating params.
I would suggest, though, increasing FSM_relations even more, until your daily
VACUUM FULL does almost no work. This will improve index usage and speed
queries.
> I moved the WAL (pg_xlog directory) to another drive. There are two drives
> in the system, so one has the OS, servers, all files, and the WAL and the
> other has nothing but the data. I think it'd be best to put the WAL on a
> separate drive from the OS but I don't know if I can get another drive
> added just for that due to our limited budget.
A high-speed IDE drive might be adequate for WAL, except that Linux has
booting issues with a mix of IDE & SCSI and many motherboards.
> I learned that I only need to vacuum tables that are changed frequently.
> My app doesn't do any deletes, and only one table changes, the monitor
> table. several times a second. So I only need to vacuum that table.
> Vacuuming the entire database is slow and unecessary. If I only do the
> monitor table, it takes only a few seconds. Much better than the 35
> minutes for the entire database that it was taking this morning.
Increasing FSM_relations will also make vacuums more efficient.
> I want to thank everyone for their input. I've heard Postgres is slow and
> doesn't scale, but now I do it's really just a matter of learning to
> configure it properly and trial & error. I do think the documentation
> could be enhanced a bit here, but I'm sure there are some users who don't
Absolutely. I'm working on it. Look to Techdocs next week.
--
Josh Berkus
Aglio Database Solutions
San Francisco
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: josh@agliodbs.com, michael.mattox@verideon.com
Subject: Re: Performance advice
In-Reply-To: <200306240904.35923.josh@agliodbs.com>
References: <CJEBLDCHAADCLAGIGCOOGEJLCKAA.michael.mattox@verideon.com> <200306240904.35923.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox