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@xxxxxxxxxxxxx http://www.gregsmith.com Baltimore, MD
