Цитат от Robert Haas <robertmhaas@xxxxxxxxx>:
2009/9/14 <zz_11@xxxxxxx>:
Цитат от Robert Haas <robertmhaas@xxxxxxxxx>:
2009/9/14 <tv@xxxxxxxx>:
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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
-------------------------------------
Powered by Mail.BG - http://mail.bg
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
