Thread: Performance Monitoring
<div style="text-align: left;">Hello All,<br /><br />Recently, I have been involved in some work that requires me to monitorlow level performance counters for pgsql. Specifically, when I execute a particular query I want to be able to tellhow many system calls get executed on behalf of that query and time of each sys call. The idea is to provide a breakdown of the total query running time in terms of CPU time (user+system) and IO (wait time + service time) and any othercontributing factors.<br /><br />I have been using various performance monitoring tool in linux including vmstat, mpstat,iostat and strace(to monitor system calls). Surprisingly, no matter what type of query I execute, when I use straceto monitor the system calls generated they turn out to be the same for ALL sorts of queries. Now this could happenbecause (a) the tool (strace) is not robust (b) something the pgsql code is doing that eludes strace. At this pointI have been unable to narrow it down. Any help in this regard will be greatly appreciated. Also if somebody knows abetter way of achieving the same goal please let me know.<br /><br />Thanks for reading.<br />-Umar<br /></div><br /><hr/>Live Earth is coming. Learn more about the hottest summer event - only on MSN. <a href="http://liveearth.msn.com?source=msntaglineliveearthwlm"target="_new">Check it out!</a>
On Fri, 15 Jun 2007, Umar Farooq wrote: > Surprisingly, no matter what type of query I execute, when I use strace > to monitor the system calls generated they turn out to be the same for > ALL sorts of queries. How are you calling strace? The master postgres progress forks off new processes for each of the clients, you need to make sure you include stats from all of them as well to get anything. I normally use strace -c -f -p [main postgres process] which is helpful to collecte basic info, but even that's not quite right; you don't get the work done by the other proceses (logger, writer, stats collector). To get everything, you need to start some process that you attach strace to like this, then have that process start the server. I haven't found strace to be a great tool for this type of work. You might take at look at systemtap instead ( http://sourceware.org/systemtap/wiki ) which is a bit immature but is going the right direction. I will now bow my head and wait for someone to suggest you move to an OS that supports dtrace. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
On Friday 15 June 2007 13:29, Greg Smith wrote: > On Fri, 15 Jun 2007, Umar Farooq wrote: > > Surprisingly, no matter what type of query I execute, when I use strace > > to monitor the system calls generated they turn out to be the same for > > ALL sorts of queries. > > How are you calling strace? The master postgres progress forks off new > processes for each of the clients, you need to make sure you include stats > from all of them as well to get anything. > > I normally use > > strace -c -f -p [main postgres process] > > which is helpful to collecte basic info, but even that's not quite right; > you don't get the work done by the other proceses (logger, writer, stats > collector). To get everything, you need to start some process that you > attach strace to like this, then have that process start the server. > > I haven't found strace to be a great tool for this type of work. You > might take at look at systemtap instead ( > http://sourceware.org/systemtap/wiki ) which is a bit immature but is > going the right direction. > > I will now bow my head and wait for someone to suggest you move to an OS > that supports dtrace. > You know that's what I was thinking :-) -- Robert Treat Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL