Hi!
I'm planning to move from mysql to postgresql as I believe the latter
performs better when it comes to complex queries. The mysql database
that I'm running is about 150 GB in size, with 300 million rows in the
largest table. We do quite a lot of statistical analysis on the data
which means heavy queries that run for days. Now that I've got two new
servers with 32GB of ram I'm eager to switch to postgresql to improve
perfomance. One database is to be an analysis server and the other an
OLTP server feeding a web site with pages.
I'm setting for Postgresql 8.1 as it is available as a package in Debian
Etch AMD64.
As I'm new to postgresql I've googled to find some tips and found some
interesting links how configure and tune the database manager. Among
others I've found the PowerPostgresql pages with a performance checklist
and annotated guide to postgresql.conf
[http://www.powerpostgresql.com/]. And of course the postgresql site
itself is a good way to start. RevSys have a short guide as well
[http://www.revsys.com/writings/postgresql-performance.html]
I just wonder if someone on this list have some tips from the real world
how to tune postgresql and what is to be avoided. AFAIK the following
parameters seems important to adjust to start with are:
-work_mem
-maintenance_work_mem - 50% of the largest table?
-shared_buffers - max value 50000
-effective_cache_size - max 2/3 of available ram, ie 24GB on the
hardware described above
-shmmax - how large dare I set this value on dedicated postgres servers?
-checkpoint_segments - this is crucial as one of the server is
transaction heavy
-vacuum_cost_delay
Of course some values can only be estimated after database has been feed
data and queries have been run in a production like manner.
Cheers
// John
Ps. I sent to list before but the messages where withheld as I'm not "a
member of any of the restrict_post groups". This is perhaps due to the
fact that we have changed email address a few weeks ago and there was a
mismatch between addresses. So I apologize if any similar messages show
up from me, just ignore them.
