Am Freitag 26 Januar 2007 12:28 schrieb John Parnefjord:
> 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
> 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:
> -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
Do you use a Opteron with a NUMA architecture ?
You could end up with switching pages between your memory nodes, which slowed
down heavily my server (Tyan 2895, 2 x 275 cpu, 8 GB)...
Try first to use only one numa node for your cache.
> 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
> Of course some values can only be estimated after database has been feed
> data and queries have been run in a production like manner.
> // 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.
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
In response to
- Tuning at 2007-01-26 11:28:19 from John Parnefjord
pgsql-performance by date
|Next:||From: Jim Nasby||Date: 2007-01-26 21:07:45|
|Subject: Re: [HACKERS] how to plan for vacuum?|
|Previous:||From: Dave Cramer||Date: 2007-01-26 14:07:21|
|Subject: Re: Tuning|