Skip site navigation (1) Skip section navigation (2)

Re: possible wrong query plan on pg 8.3.5,

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: zz_11(at)mail(dot)bg
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: possible wrong query plan on pg 8.3.5,
Date: 2009-09-14 15:30:03
Message-ID: 603c8f070909140830x100e7983q29790d3e3b1cac7b@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
2009/9/14  <zz_11(at)mail(dot)bg>:
> Цитат от Robert Haas <robertmhaas(at)gmail(dot)com>:
>
>> 2009/9/14  <tv(at)fuzzy(dot)cz>:
>>>
>>> It seems there's something very wrong - the plans are "equal" but in the
>>> first case the results (actual time) are multiplied by 100. Eithere there
>>> is some sort of cache (so the second execution is much faster), or the
>>> system was busy during the first execution, or there is something wrong
>>> with the hardware.
>>
>> I think you should run this query more than twice.  If it's slow the
>> first time and fast every time for many executions after that, then
>> it's probably just the data getting loaded into the OS cache (or
>> shared buffers).  If it's bouncing back and forth between fast and
>> slow, you might want to check whether your machine is swapping.
>
> I did it many times. Alter the first atempt it works fast, but after a
> couple of minutes ( I think after changing the data in cache) the query is
> working also very slow.
>
> I do not see any swap on OS.
>
>>
>> It might also be helpful to post all the uncommented settings from
>> your postgresql.conf file.
>
> postgresql.conf :
>
> max_connections = 2000
> shared_buffers = 1800MB
> temp_buffers = 80MB
> work_mem = 120MB
>
> maintenance_work_mem = 100MB
> max_fsm_pages = 404800
> max_fsm_relations = 5000
>
> max_files_per_process = 2000
> wal_buffers = 64MB
> checkpoint_segments = 30
> effective_cache_size = 5000MB
> default_statistics_target = 800

I think you're exhausting the physical memory on your machine.  How
much RAM do you have?  How many active connections at one time?  120MB
is a HUGE value for work_mem.  I would try reducing that to, say, 4
MB, and see what happens.  Your setting for temp_buffers also seems
way too high.  I would put that one back to the default, at least for
starters.  And for that matter, why have you increased the value for
wal_buffers to over 1000 times the default value?

The reason you may not be seeing evidence of swapping is that it may
be happening quite briefly during query execution.  But I have to
think it's happening, because otherwise the performance drop-off is
hard to account for.

...Robert

In response to

Responses

pgsql-performance by date

Next:From: Scott MarloweDate: 2009-09-14 15:51:39
Subject: Re: possible wrong query plan on pg 8.3.5,
Previous:From: Andrzej ZawadzkiDate: 2009-09-14 14:19:02
Subject: CLUSTER and a problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group