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