Thread: Tracking disk writes? (again)
Hi, is there any way that I can track the number of actual disk writes done by a particular database or database cluster? I already sent this question last Friday and, while even I usually frown upon reposts to lists and forums, I really need some kind of answer, even if it's "you can't" in which case a why would also be cool.
erik jones <erik@myemma.com>
sofware developer
615-296-0838
emma(r)
Erik, on windows you can use performance monitor to filter down diskrequest. With sysinternals tools you can also go down to disk accesses on process basis. google for sysinternals site:microsoft.com on other platforms I have no information. Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Reinsburgstraße 202b 70197 Stuttgart 0173/9409607 fx 01212-5-13695179 - Python: the only language with more web frameworks than keywords.
On Mar 12, 2007, at 11:22 AM, Harald Armin Massa wrote:
Erik,on windows you can use performance monitor to filter down diskrequest.With sysinternals tools you can also go down to disk accesses onprocess basis.google for sysinternals site:microsoft.comon other platforms I have no information.
We have db servers running on both Ubuntu Linux and Solaris 10.
Erik, for solaris I think dtrace can be of help: http://www.opensolaris.org/os/community/dtrace/ PostgreSQL 8.2 introduced dtrace-ability. From what I read it is specifically created to do this kind of measurement. Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Reinsburgstraße 202b 70197 Stuttgart 0173/9409607 fx 01212-5-13695179 - Python: the only language with more web frameworks than keywords.
On Mar 12, 2007, at 11:46 AM, Harald Armin Massa wrote:
Erik,for solaris I think dtrace can be of help:PostgreSQL 8.2 introduced dtrace-ability.From what I read it is specifically created to do this kind of measurement.
Hmm... That would be a particulary heavy weight solution to what I was hoping for. Basically, I'm looking to poll the db or "something" to get either the total number of block or page writes done by the db either ever or since my last reading. That way I can graph disk writes over time and compare to the graphs I've already got for transactions committed, transactios rolled back, disk blocks read from disk, and disk blocks found in cache.
erik jones <erik@myemma.com>
sofware developer
615-296-0838
emma(r)
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 03/12/07 12:16, Erik Jones wrote: > > On Mar 12, 2007, at 11:46 AM, Harald Armin Massa wrote: > >> Erik, >> >> for solaris I think dtrace can be of help: >> >> http://www.opensolaris.org/os/community/dtrace/ >> >> PostgreSQL 8.2 introduced dtrace-ability. >> >> From what I read it is specifically created to do this kind of >> measurement. > > Hmm... That would be a particulary heavy weight solution to what I was > hoping for. Basically, I'm looking to poll the db or "something" to get > either the total number of block or page writes done by the db either > ever or since my last reading. That way I can graph disk writes over > time and compare to the graphs I've already got for transactions > committed, transactios rolled back, disk blocks read from disk, and disk > blocks found in cache. Like a system montitor that tracks reads, writes, transactions, etc? > > erik jones <erik@myemma.com> > sofware developer > 615-296-0838 > emma(r) -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF9ZiyS9HxQb37XmcRAiNKAKCxG7RJUEQTpyU6N6bqlllOcvUJ6wCfZh+8 7s3LprWsR2IEB3n+FwyLy58= =cPIV -----END PGP SIGNATURE-----
On Mon, Mar 12, 2007 at 11:14:14AM -0500, Erik Jones wrote: > Hi, is there any way that I can track the number of actual disk > writes done by a particular database or database cluster? I already > sent this question last Friday and, while even I usually frown upon > reposts to lists and forums, I really need some kind of answer, even > if it's "you can't" in which case a why would also be cool. I'm not sure it's what you mean, but the pg_stats* tables include stuff like blocks read from tables and indexes and the like... You do need to enable it in the config though. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
Martijn van Oosterhout wrote: > On Mon, Mar 12, 2007 at 11:14:14AM -0500, Erik Jones wrote: > > Hi, is there any way that I can track the number of actual disk > > writes done by a particular database or database cluster? I already > > sent this question last Friday and, while even I usually frown upon > > reposts to lists and forums, I really need some kind of answer, even > > if it's "you can't" in which case a why would also be cool. > > I'm not sure it's what you mean, but the pg_stats* tables include stuff > like blocks read from tables and indexes and the like... You do need to > enable it in the config though. But it does not include disk writes, only reads. I think it would make sense to add blocks dirtied and blocks written to the block-level stats. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Mar 12, 2007, at 1:31 PM, Martijn van Oosterhout wrote:
On Mon, Mar 12, 2007 at 11:14:14AM -0500, Erik Jones wrote:Hi, is there any way that I can track the number of actual diskwrites done by a particular database or database cluster? I alreadysent this question last Friday and, while even I usually frown uponreposts to lists and forums, I really need some kind of answer, evenif it's "you can't" in which case a why would also be cool.I'm not sure it's what you mean, but the pg_stats* tables include stufflike blocks read from tables and indexes and the like... You do need toenable it in the config though.
That is exactly what I mean except that there doesn't seem to be anything in there for tracking writes done by the database. There's the number of reads from disk and reads from the cache, but nothing that I can see wrt writes to disk.
erik jones <erik@myemma.com>
sofware developer
615-296-0838
emma(r)
On Mar 12, 2007, at 1:15 PM, Ron Johnson wrote:
-----BEGIN PGP SIGNED MESSAGE-----Hash: SHA1On 03/12/07 12:16, Erik Jones wrote:On Mar 12, 2007, at 11:46 AM, Harald Armin Massa wrote:Erik,for solaris I think dtrace can be of help:PostgreSQL 8.2 introduced dtrace-ability.From what I read it is specifically created to do this kind ofmeasurement.Hmm... That would be a particulary heavy weight solution to what I washoping for. Basically, I'm looking to poll the db or "something" to geteither the total number of block or page writes done by the db eitherever or since my last reading. That way I can graph disk writes overtime and compare to the graphs I've already got for transactionscommitted, transactios rolled back, disk blocks read from disk, and diskblocks found in cache.Like a system montitor that tracks reads, writes, transactions, etc?
Well, disk reads, cache hits, transactions commited, transactions rolled back, index size and usage, etc. are all able to be tracked vi the pg catalogue tables and views. But, I haven't seen anything that will give me numbers on actual disk writes done by the database.
erik jones <erik@myemma.com>
sofware developer
615-296-0838
emma(r)
Erik Jones <erik@myemma.com> writes:
> Well, disk reads, cache hits, transactions commited, transactions
> rolled back, index size and usage, etc. are all able to be tracked vi
> the pg catalogue tables and views. But, I haven't seen anything that
> will give me numbers on actual disk writes done by the database.
One of the reasons you don't see that is that a large fraction of the
writes are triggered in background by the "bgwriter" process, which
operates at too low a level to participate in the stats collection
mechanism. I'm not sure what would be involved in refactoring things
sufficiently to make that workable, but it'd be nontrivial.
regards, tom lane
Tom Lane wrote: > Erik Jones <erik@myemma.com> writes: > > Well, disk reads, cache hits, transactions commited, transactions > > rolled back, index size and usage, etc. are all able to be tracked vi > > the pg catalogue tables and views. But, I haven't seen anything that > > will give me numbers on actual disk writes done by the database. > > One of the reasons you don't see that is that a large fraction of the > writes are triggered in background by the "bgwriter" process, which > operates at too low a level to participate in the stats collection > mechanism. I'm not sure what would be involved in refactoring things > sufficiently to make that workable, but it'd be nontrivial. You mean that bgwriter cannot send stat messages? Keep in mind that these are block-level stats, so there's no need to peek at the page contents ... -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Tom Lane wrote:
>> One of the reasons you don't see that is that a large fraction of the
>> writes are triggered in background by the "bgwriter" process, which
>> operates at too low a level to participate in the stats collection
>> mechanism. I'm not sure what would be involved in refactoring things
>> sufficiently to make that workable, but it'd be nontrivial.
> You mean that bgwriter cannot send stat messages?
Right. The stats mechanism is attached to relcache entries, which the
bgwriter doesn't have. And if it did collect stats, it would never send
them because that happens in the outer postgres.c loop (it's not totally
clear what would be a good granularity for sending them in bgwriter).
And I think it is not a backend in the stats collector's eyes, either.
Surely these things could be dealt with, but it'd take some refactoring.
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 03/12/07 22:57, Tom Lane wrote: > Alvaro Herrera <alvherre@commandprompt.com> writes: >> Tom Lane wrote: >>> One of the reasons you don't see that is that a large fraction of the >>> writes are triggered in background by the "bgwriter" process, which >>> operates at too low a level to participate in the stats collection >>> mechanism. I'm not sure what would be involved in refactoring things >>> sufficiently to make that workable, but it'd be nontrivial. > >> You mean that bgwriter cannot send stat messages? > > Right. The stats mechanism is attached to relcache entries, which the > bgwriter doesn't have. And if it did collect stats, it would never send > them because that happens in the outer postgres.c loop (it's not totally > clear what would be a good granularity for sending them in bgwriter). > And I think it is not a backend in the stats collector's eyes, either. > > Surely these things could be dealt with, but it'd take some refactoring. It would definitely be useful for the DBA to have the data to let him graph each tablespace's read & write activity over time. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org/ -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF9ieUS9HxQb37XmcRAvcpAJ0VhUsShoGNN/Gjkm98QqQYoAxl+gCfbdXh oTauMDGbl1ZeC3GVGezBHDU= =7qNy -----END PGP SIGNATURE-----
On Mar 12, 2007, at 10:57 PM, Tom Lane wrote:
Alvaro Herrera <alvherre@commandprompt.com> writes:Tom Lane wrote:One of the reasons you don't see that is that a large fraction of thewrites are triggered in background by the "bgwriter" process, whichoperates at too low a level to participate in the stats collectionmechanism. I'm not sure what would be involved in refactoring thingssufficiently to make that workable, but it'd be nontrivial.You mean that bgwriter cannot send stat messages?Right. The stats mechanism is attached to relcache entries, which thebgwriter doesn't have. And if it did collect stats, it would never sendthem because that happens in the outer postgres.c loop (it's not totallyclear what would be a good granularity for sending them in bgwriter).And I think it is not a backend in the stats collector's eyes, either.Surely these things could be dealt with, but it'd take some refactoring.
Tom,
Thanks for your insights on this. To be honest, I was kind of expecting you or one of the other core guys to stand up and say, "bgwriter!" as I already expected that if there wasn't currently any accounting from the bgwriter this wouldn't really be feasible. What are the odds of you guys putting this on a your TODO list for a future postgres release? Tracking disk level io in both directions would be an invaluable tool for profiling our db over time in order to correlate different kinds of usage of our app with the numbers we get from iostat et al. Yes, on Solaris (and soon, Linux) DTrace is available for attaching to single processes and tracking what they are doing at the moment, but that doesn't give me the ability to answer the question: "We had reports of app slowness last night, we see via iostat that there was a huge io spike at the time, was it all postgres?
Also, are there any usage scenarios where having the bgwriter on could be detrimental to system performance that we should watch for?
erik jones <erik@myemma.com>
sofware developer
615-296-0838
emma(r)