- I'm using ext4 - Kernel: Linux 2.6.32-279.9.1.el6.x86_64 #1 SMP Fri Aug 31 09:04:24 EDT 2012 x86_64 x86_64 x86_64 GNU/Linux - I haven't tuned kernel in any way except setting kernel.shmmax and kernel.shmall to: kernel.shmmax = 68719476736 kernel.shmall = 4294967296 - We are using 15k drives (magnetic) connected through SAS in RAID10 setup, I don't know precise model numbers (I can find out),
On 1 November 2012 15:40, Marcos Ortiz <mlor...@uci.cu> wrote: > Regards, Petr. > Tuning PostgreSQL is not just change the postgresql.conf, it includes more > things like: > - the filesystem that you are using > - the kernel version that you using (particularly in Linux systems) > - the tuning to kernel variables > - the type of discs that you are using (SSDs are very fast, like you saw > in your iMac system) > > > On 10/30/2012 02:44 PM, Petr Praus wrote: > > 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 >> > > > -- > ** > > Marcos Luis OrtÃz Valmaseda > about.me/marcosortiz > @marcosluis2186 <http://twitter.com/marcosluis2186> > ** > > <http://www.uci.cu/> > >