Thread: Tuning
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.
On 26-Jan-07, at 6:28 AM, John Parnefjord wrote: > > 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? Isn't it possible for this to be larger than memory ? > -shared_buffers - max value 50000 Where does this shared buffers maximum come from ? It's wrong it should be 1/4 of available memory (8G) to start and tuned from there > -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? as big as required by shared buffer setting above > -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. > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster >
Hello ! Am Freitag 26 Januar 2007 12:28 schrieb John Parnefjord: > 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 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 > -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. > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster
John, > -work_mem Depends on the number of concurrent queries you expect to run and what size sorts you expect them to do. > -maintenance_work_mem - 50% of the largest table? Actually, in current code I've found that anything over 256mb doesn't actually get used. > -shared_buffers - max value 50000 Actually, I need to update that. On newer faster multi-core machines you may want to allocate up to 1GB of shared buffers. > -effective_cache_size - max 2/3 of available ram, ie 24GB on the > hardware described above Yes. > -shmmax - how large dare I set this value on dedicated postgres servers? Set it to 2GB and you'll be covered. > -checkpoint_segments - this is crucial as one of the server is > transaction heavy Well, it only helps you to raise this if you have a dedicated disk resource for the xlog. Otherwise having more segments doesn't help you much. > -vacuum_cost_delay Try 200ms to start. Also, set wal_buffers to 128. -- Josh Berkus PostgreSQL @ Sun San Francisco
Josh Berkus <josh@agliodbs.com> writes:
>> -checkpoint_segments - this is crucial as one of the server is
>> transaction heavy
> Well, it only helps you to raise this if you have a dedicated disk resource
> for the xlog. Otherwise having more segments doesn't help you much.
Au contraire, large checkpoint_segments is important for write-intensive
workloads no matter what your disk layout is. If it's too small then
you'll checkpoint too often, and the resulting increase in page-image
writes will hurt. A lot.
My advice is to set checkpoint_warning to the same value as
checkpoint_timeout (typically 5 minutes or so) and then keep an eye on
the postmaster log for awhile. If you see more than a few "checkpoints
are occuring too frequently" messages, you want to raise
checkpoint_segments.
regards, tom lane
At 06:24 PM 1/28/2007, Josh Berkus wrote: >John, > > > -work_mem > >Depends on the number of concurrent queries you expect to run and what size >sorts you expect them to do. EXPLAIN ANALYZE is your friend. It will tell you how much data each query is manipulating and therefore how much memory each query will chew. The next step is to figure out how many of each query will be running concurrently. Summing those will tell you the maximum work_mem each kind of query will be capable of using. If you have a deep enough understanding of how your pg system is working, then you can set work_mem on a per query basis to get the most efficient use of the RAM in your system. > > -maintenance_work_mem - 50% of the largest table? > >Actually, in current code I've found that anything over 256mb >doesn't actually >get used. Is this considered a bug? When will this limit go away? Does work_mem have a similar limit? > > -shared_buffers - max value 50000 > >Actually, I need to update that. On newer faster multi-core >machines you may >want to allocate up to 1GB of shared buffers. > > > -effective_cache_size - max 2/3 of available ram, ie 24GB on the > > hardware described above > >Yes. Why? "max of 2/3 of available RAM" sounds a bit hand-wavy. Especially with 32gb, 64GB, and 128GB systems available. Is there are hidden effective or hard limit here as well? For a dedicated pg machine, I'd assume one would want to be very aggressive about configuring the kernel, minimizing superfluous services, and configuring memory use so that absolutely as much as possible is being used by pg and in the most intelligent way given one's specific pg usage scenario. > > -shmmax - how large dare I set this value on dedicated postgres servers? > >Set it to 2GB and you'll be covered. I thought that on 32b systems the 2GB shmmax limit had been raised to 4GB? and that there essentially is no limit to shmmax on 64b systems? What are Oracle and EnterpriseDB recommending for shmmax these days? My random thoughts, Ron Peacetree
> What are Oracle and EnterpriseDB recommending for shmmax these days? According to Oracle "set to a value half the size of physical memory". [http://www.oracle.com/technology/tech/linux/validated-configurations/ht ml/vc_dell6850-rhel4-cx500-1_1.html] I've been talking to an Oracle DBA and he said that they are setting this to something between 70-80% on a dedicated database server. As long as one doesn't run other heavy processes and leave room for the OS. EnterpriseDB advocates: 250 KB + 8.2 KB * shared_buffers + 14.2 kB * max_connections up to infinity [http://www.enterprisedb.com/documentation/kernel-resources.html] // John
> -----Original Message----- > From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance- > owner@postgresql.org] On Behalf Of John Parnefjord > Sent: Tuesday, January 30, 2007 2:05 AM > Subject: Re: [PERFORM] Tuning > EnterpriseDB advocates: 250 KB + 8.2 KB * shared_buffers + 14.2 kB * > max_connections up to infinity > [http://www.enterprisedb.com/documentation/kernel-resources.html] ... + 8.1KB * wal_buffers + 6 * max_fsm_pages + 65 * max_fsm_relations. Okay, maybe getting pedantic; but if you're going to cite the ~256KB const over head ... :-)
Tuners,
allways be aware that results on Windows may be totally different!
My main customer is running PostgreSQL 8.1 on MINIMUM shared buffers
max_connections = 100 #
shared_buffers = 200 # min 16 or max_connections*2, 8KB each
I changed it to this value from the very low default 20000, and the system is responding better; especially after fixing the available memory setting within the planner.
... frustrating part is, I could not replicate this behavious with pg_bench :(
Harald
--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
fx 01212-5-13695179
-
Python: the only language with more web frameworks than keywords.
allways be aware that results on Windows may be totally different!
My main customer is running PostgreSQL 8.1 on MINIMUM shared buffers
max_connections = 100 #
shared_buffers = 200 # min 16 or max_connections*2, 8KB each
I changed it to this value from the very low default 20000, and the system is responding better; especially after fixing the available memory setting within the planner.
... frustrating part is, I could not replicate this behavious with pg_bench :(
Harald
--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
fx 01212-5-13695179
-
Python: the only language with more web frameworks than keywords.