Re: Response time between shared buffer cache and operating system

On Thu, 7 Aug 2008, RASHA OSMAN wrote:

> How long it takes the operating system to fulfil a page demand, ie,
> reading the page from disk or from the OS cache to the Postgres shared
> buffer. Also how long it takes the bgwriter to flush a page from the
> shared buffer into the OS cache or disk.

PostgreSQL doesn't actually know any of this information. Pages get read;
maybe they came from disk, maybe from the OS's cache, the database doesn't
know. Similarly, pages gets written to the OS cache, and PostgreSQL has
no idea when that actually makes its way onto disk. Also, there is zero
internal timing of these low-level operations inside the database right
now. You can get statement-level timing out of PostgreSQL using things
like \timing and EXPLAIN ANALYZE, that's about it.

Right now you need operating system profiling tools to figure all this
out. If you were using the latest development rev of PostgreSQL on
Solaris/FreeBSD/MacOS, you might collect this information with dtrace, but
none of that helps on the 8.1/Linux combo you're running. Maybe you could
convince oprofile to collect the data you want for you on Linux.

I would also suggest estimating these values by writing some SQL-based
benchmark for your purposes. You could look at the pg_stat* views (see
http://www.postgresql.org/docs/8.1/static/monitoring-stats.html ) to get
an idea how many calls were made to the OS. If you started from a clean
PostgreSQL and OS cache (stop database, remount database disk, start
database), carefully controlled what you looked for via SELECT, and timed
the results, you could estimate all these values from there. This would
be more useful than the source-code level modifications you were asking
about IMHO, because even if you had that you'd still need to go through
much of the exercise I just described to figure out how to translate the
per-page figures into something useful for the application you want these
measurements for.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD