>
> 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!
