From: Greg Smith <greg@2ndquadrant.com>
To: Chris Hoover <revoohc@gmail.com>
Cc: PGSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: Estimating hot data size
Date: Wed, 16 Feb 2011 17:02:40 -0500
Message-ID: <4D5C4980.9070807@2ndquadrant.com> (raw)
In-Reply-To: <AANLkTikxfB099wX3TtnXvKPoFLCcXtMCYpNvCSygpNPb@mail.gmail.com>
References: <AANLkTikxfB099wX3TtnXvKPoFLCcXtMCYpNvCSygpNPb@mail.gmail.com>
Chris Hoover wrote:
> Basically, I'm using the sum(heap_blks_read + idx_blks_read) from
> pg_statio_all_tables, and diffing the numbers over a period of time (1
> hour at least). Is this a fair estimate? The reason for doing this
> is we are looking at new server hardware, and I want to try and get
> enough ram on the machine to keep the hot data in memory plus provide
> room for growth.
Those two are measuring reads to the operating system, which isn't
really a good measure of the working data set. If you switch to the
internal counters that measure what's already cached, that won't be
quite right either. Those will be repeatedly measuring the same block,
on the truly hot ones, which inflates how big you'll think the working
set is relative to its true size.
If you visit http://projects.2ndquadrant.com/talks you'll find a talk
called "Inside the PostgreSQL Buffer Cache" that goes over how the cache
is actually managed within the database. There's also some sample
queries that run after you install the pg_buffercache module into a
database. Check out "Buffer contents summary, with percentages".
That's the only way to really measure what you're trying to see. I will
sometimes set shared_buffers to a larger value than would normally be
optimal for a bit, just to get a better reading on what the hot data is.
If you also want to get an idea what's in the operating system cache,
the pgfincore module from http://pgfoundry.org/projects/pgfincore/ will
allow that on a Linux system.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: greg@2ndquadrant.com, revoohc@gmail.com
Subject: Re: Estimating hot data size
In-Reply-To: <4D5C4980.9070807@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox