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.
