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 on
process basis.google for sysinternals site:microsoft.com
on other platforms I have no information.
Ah, good point, I should have specified some system details (although
I was hoping I could get at this through postgres as I can when
tracking disk reads).
We have db servers running on both Ubuntu Linux and Solaris 10.
erik jones <erik@myemma.com>
sofware developer
615-296-0838
emma(r)
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:
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.
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/
Show quoted text
From each according to his ability. To each according to his ability to litigate.
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 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.
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:
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?
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?
-----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
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.
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)