Re: Monitoring buffercache...

Поиск
Список
Период
Сортировка
От Brad Nicholson
Тема Re: Monitoring buffercache...
Дата
Msg-id 1227555922.18990.94.camel@bnicholson-desktop
обсуждение исходный текст
Ответ на Monitoring buffercache...  (Kevin Kempter <kevin@consistentstate.com>)
Список pgsql-performance
On Mon, 2008-11-24 at 11:43 -0700, Kevin Kempter wrote:
> 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.

I wouldn't routinely run pg_buffercache on a busy database.  Plus, I
don't think that pg_buffercache will answer this question for you. It
will tell you whats currently in the buffer pool and the clean/dirty
status, but that's not the first place I'd look, but what you really
need is to figure out the hit ratio on the buffer pool and go from
there.

> Does anyone have any suggestions per a better approach or maybe a way to
> improve the performance for the above query ?

You should be able to use the blocks hit vs block read data in the
pg_stat_database view (for the overall database), and drill down into
pg_statio_user_tables/pg_statio_all_tables to get more detailed data if
you want.

--
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.


В списке pgsql-performance по дате отправления:

Предыдущее
От: "Scott Marlowe"
Дата:
Сообщение: Re: limit clause produces wrong query plan
Следующее
От: "Scott Marlowe"
Дата:
Сообщение: Re: Monitoring buffercache...