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
<mailto: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
<http://c.id>=cp.contestId
left outer join teammember tm on tm.contestparticipantid=cp.id
<http://cp.id>
left outer join staffmember sm on cp.id
<http://cp.id>=sm.contestparticipantid
left outer join person p on p.id <http://p.id>=cp.personid
left outer join personinfo pi on pi.id
<http://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-down
but a few people suggested
--
Marcos Luis OrtÃz Valmaseda
about.me/marcosortiz <http://about.me/marcosortiz>
@marcosluis2186 <http://twitter.com/marcosluis2186>
10mo. ANIVERSARIO DE LA CREACION DE LA UNIVERSIDAD DE LAS CIENCIAS
INFORMATICAS...
CONECTADOS AL FUTURO, CONECTADOS A LA REVOLUCION
http://www.uci.cu
http://www.facebook.com/universidad.uci
http://www.flickr.com/photos/universidad_uci
