> > 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
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?
_________________________________________________________________
Win a voice over part with Kung Fu Panda & Live Search and 100’s of Kung Fu Panda prizes to win with Live Search
http://clk.atdmt.com/UKM/go/107571439/direct/01/