Search Postgresql Archives

Re: Response time between shared buffer cache and operating system

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux