Обсуждение: 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!