Re: possible wrong query plan on pg 8.3.5,

Цитат от Robert Haas <robertmhaas@gmail.com>:

> 2009/9/14  <zz_11@mail.bg>:
>> Цитат от Robert Haas <robertmhaas@gmail.com>:
>>
>>> 2009/9/14  <tv@fuzzy.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?
>

We have 8 GB RAM, running Centos 64-bit and ~10 to 15 active
connections ( using connection pool).
120 MB for work mem is good. If I drop this value I will receive very
bad performance for the hole system.

I will try to reduce wal_buffers ( is this value connected to ram usage ? ).



> 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.
>
On linux if I have swap the os never restores the ram used for swap.
And I do not see any swap on OS. I send the vmstat for the server:
  0  0   1388  44852  25160 6225316    0    0   304     0 1018  201  0
  0 100  0  0
procs -----------memory---------- ---swap-- -----io---- --system--
-----cpu------
  r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us
sy id wa st
  0  0   1388  47612  25148 6222364    0    0   332     4 1015  194  0
  0 100  0  0
  0  0   1388  47072  25156 6222900    0    0   268     8 1015  190  0
  0 100  0  0
  0  0   1388  46532  25160 6223656    0    0   270     0 1014  194  0
  0 100  0  0



> ...Robert
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
>




-------------------------------------
Powered by Mail.BG - http://mail.bg