Re: possible wrong query plan on pg 8.3.5,
От | zz_11@mail.bg |
---|---|
Тема | Re: possible wrong query plan on pg 8.3.5, |
Дата | |
Msg-id | 20090914191353.rqmnanzrendsk0oo@mail.bg обсуждение исходный текст |
Ответ на | Re: possible wrong query plan on pg 8.3.5, (Robert Haas <robertmhaas@gmail.com>) |
Ответы |
Re: possible wrong query plan on pg 8.3.5,
(Віталій Тимчишин <tivv00@gmail.com>)
|
Список | pgsql-performance |
Цитат от 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
В списке pgsql-performance по дате отправления: