Monitoring buffercache...
От | Kevin Kempter |
---|---|
Тема | Monitoring buffercache... |
Дата | |
Msg-id | 200811241143.57182.kevin@kevinkempterllc.com обсуждение исходный текст |
Ответы |
Re: Monitoring buffercache...
Re: Monitoring buffercache... Re: Monitoring buffercache... |
Список | pgsql-performance |
Hi All; I've installed pg_buffercache and I want to use it to help define the optimal shared_buffers size. Currently I run this each 15min via cron: insert into buffercache_stats select now(), isdirty, count(*) as buffers, (count(*) * 8192) as memory from pg_buffercache group by 1,2; and here's it's explain plan explain insert into buffercache_stats select now(), isdirty, count(*) as buffers, (count(*) * 8192) as memory from pg_buffercache group by 1,2; QUERY PLAN ------------------------------------------------------------------------------------------- Subquery Scan "*SELECT*" (cost=65.00..65.23 rows=2 width=25) -> HashAggregate (cost=65.00..65.12 rows=2 width=1) -> Function Scan on pg_buffercache_pages p (cost=0.00..55.00 rows=1000 width=1) (3 rows) Then once a day I will pull a report from the buffercache_stats table. The buffercache_stats table is our own creation : \d buffercache_stats Table "public.buffercache_stats" Column | Type | Modifiers ----------------+-----------------------------+----------- snap_timestamp | timestamp without time zone | isdirty | boolean | buffers | integer | memory | integer | Here's my issue, the server that we'll eventually roll this out to is extremely busy and the every 15min query above has the potential to have a huge impact on performance. Does anyone have any suggestions per a better approach or maybe a way to improve the performance for the above query ? Thanks in advance...
В списке pgsql-performance по дате отправления: