I just found one particularly interesting fact: when I perform the same test on my mid-2010 iMac (OSX 10.7.5) also with Postgres 9.2.1 and 16GB RAM, I don't experience the slow down. Specifically: set work_mem='1MB'; select ...; // running time is ~1800 ms set work_mem='96MB'; select ...' // running time is ~1500 ms
When I do exactly the same query (the one from my previous post) with exactly the same data on the server: I get 2100 ms with work_mem=1MB and 3200 ms with 96 MB. The Mac has SSD so it's understandably faster, but it exhibits a behavior I would expect. What am I doing wrong here? Thanks. On 30 October 2012 14:08, Petr Praus <p...@praus.net> wrote: > Hello, > > I have a PostgreSQL 9.2 instance running on RHEL 6.3, 8-core machine with > 16GB of RAM. The server is dedicated to this database, the disks are local > RAID10. Given that the default postgresql.conf is quite conservative > regarding memory settings, I thought it might be a good idea to allow > Postgres to use more memory. To my surprise, following advice in the > performance tuning guide on Postgres wiki[2] significantly slowed down > practically every query I run but it's more noticeable on the more complex > queries. > > I also tried running pgtune[1] which gave the following recommendation > with more parameters tuned, but that didn't change anything. It suggests > shared_buffers of 1/4 of RAM size which seems to in line with advice > elsewhere (and on PG wiki in particular). > > default_statistics_target = 50 > maintenance_work_mem = 960MB > constraint_exclusion = on > checkpoint_completion_target = 0.9 > effective_cache_size = 11GB > work_mem = 96MB > wal_buffers = 8MB > checkpoint_segments = 16 > shared_buffers = 3840MB > max_connections = 80 > > I tried reindexing the whole database after changing the settings (using > REINDEX DATABASE), but that didn't help either. I played around with > shared_buffers and work_mem. Gradually changing them from the very > conservative default values (128k / 1MB) also gradually decreased > performance. > > I ran EXPLAIN (ANALYZE,BUFFERS) on a few queries and the culprit seems to > be that Hash Join is significantly slower. It's not clear to me why. > > To give some specific example, I have the following query. It runs in > ~2100ms on the default configuration and ~3300ms on the configuration with > increased buffer sizes: > > select count(*) from contest c > left outer join contestparticipant cp on c.id=cp.contestId > left outer join teammember tm on tm.contestparticipantid=cp.id > left outer join staffmember sm on cp.id=sm.contestparticipantid > left outer join person p on p.id=cp.personid > left outer join personinfo pi on pi.id=cp.personinfoid > where pi.lastname like '%b%' or pi.firstname like '%a%'; > > EXPLAIN (ANALYZE,BUFFERS) for the query above: > > - Default buffers: http://explain.depesz.com/s/xaHJ > - Bigger buffers: http://explain.depesz.com/s/Plk > > The tables don't have anything special in them > > The question is why am I observing decreased performance when I increase > buffer sizes? The machine is definitely not running out of memory. > Allocation if shared memory in OS is (`shmmax` and `shmall`) is set to very > large values, that should not be a problem. I'm not getting any errors in > the Postgres log either. I'm running autovacuum in the default > configuration but I don't expect that has anything to do with it. All > queries were run on the same machine few seconds apart, just with changed > configuration (and restarted PG). > > I also found a blog post [3] which experiments with various work_mem > values that run into similar behavior I'm experiencing but it doesn't > really explain it. > > [1]: http://pgfoundry.org/projects/pgtune/ > [2]: http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server > [3]: > http://www.depesz.com/2011/07/03/understanding-postgresql-conf-work_mem/ > > Thanks, > Petr Praus > > PS: > I also posted the question here: > http://dba.stackexchange.com/questions/27893/increasing-work-mem-and-shared-buffers-on-postgres-9-2-significantly-slows-downbut > a few people suggested >
