Re: Re: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries
От | Marcos Ortiz |
---|---|
Тема | Re: Re: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries |
Дата | |
Msg-id | 5092DE42.5060206@uci.cu обсуждение исходный текст |
Ответ на | Re: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries (Petr Praus <petr@praus.net>) |
Ответы |
Re: Re: Increasing work_mem and shared_buffers on Postgres
9.2 significantly slows down queries
("Gunnar \"Nick\" Bluth" <gunnar.bluth@pro-open.de>)
Re: Re: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries (Petr Praus <petr@praus.net>) |
Список | pgsql-performance |
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)
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 msset work_mem='96MB';select ...' // running time is ~1500 msWhen 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 <petr@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 = 50maintenance_work_mem = 960MBconstraint_exclusion = oncheckpoint_completion_target = 0.9effective_cache_size = 11GBwork_mem = 96MBwal_buffers = 8MBcheckpoint_segments = 16shared_buffers = 3840MBmax_connections = 80I 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 cleft outer join contestparticipant cp on c.id=cp.contestIdleft outer join teammember tm on tm.contestparticipantid=cp.idleft outer join staffmember sm on cp.id=sm.contestparticipantidleft outer join person p on p.id=cp.personidleft outer join personinfo pi on pi.id=cp.personinfoidwhere 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/PlkThe tables don't have anything special in themThe 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.Thanks,Petr PrausPS: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
@marcosluis2186
В списке pgsql-performance по дате отправления:
Предыдущее
От: Petr PrausДата:
Сообщение: Re: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries