Thread: Response time between shared buffer cache and operating system

I am using Postgres 8.1.4 on Linux. I am interested in the calculating the following for a specific application:

 

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.

 

These can be averages or detailed info that I can analyze with other tools

 

Are there any functions/views available that log this information, if not how should I change the source code.

---

Win £3000 to spend on whatever you want at Uni! Click here to WIN!

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

>
> 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@gregsmith.com http://www.gregsmith.com Baltimore, MD
 
I used strace to trace the Postgres server for  reads, writes and fsync. 
The server had stats_ block/row level = on to count the number of blocks.
 
Now, the problem is this:  the statistics themselves issue read() & write() 
calls (tried this in isolation) as well as the WAL. I couldnot see how to differentiate
between them. I thought of stopping WAL altogether by setting
wal_buffer_delay = max time to collect the info (2 hours)
but I guessed that might cause inconsitencies.
 
In a message
 
http://archives.postgresql.org/pgsql-hackers/2007-12/msg00622.php
 
it stated that log_statement_stats shows read() calls. So I tested that out.
I couldnot find any info in the message boards or online on how to read the
log to see where the timing for read() is.
 
This is an example output for a query statement from the log file:
 
SELECTLOG: QUERY STATISTICS
SELECTDETAIL: ! system usage stats:
! 0.926754 elapsed 0.046993 user 0.126981 system sec
! [0.050992 user 0.133979 sys total]
! 0/0 [0/0] filesystem blocks in/out
! 0/1870 [0/2646] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 37/433 [125/458] voluntary/involuntary context switches
! buffer usage stats:
! Shared blocks: 7436 read, 26 written, buffer hit rate = 1.22%
! Local blocks: 0 read, 0 written, buffer hit rate = 0.00%
! Direct blocks: 0 read, 0 written
 
Any ideas?
 
 
 

 

---

Get Hotmail on your mobile from Vodafone Try it Now!