On Thu, Apr 21, 2011 at 12:10 PM, Greg Smith <greg@xxxxxxxxxxxxxxx> wrote: > On 04/21/2011 11:33 AM, Florian Weimer wrote: >> >> Is there an easy way to monitor WAL traffic in away? It >> does not have to be finegrained, but it might be helpful to know if >> we're doing 10 GB, 100 GB or 1 TB of WAL traffic on a particular >> database, should the question of SSDs ever come up. >> > > You can use functions like pg_current_xlog_location() : > http://www.postgresql.org/docs/9.0/interactive/functions-admin.html > > Save a copy of this periodically: > > select now(),pg_current_xlog_location(); > > And you can see WAL volume over time given any two points from that set of > samples. > > To convert the internal numbers returned by that into bytes, you'll need to > do some math on them. Examples showing how that works and code in a few > languages: > > http://archives.postgresql.org/pgsql-general/2010-10/msg00077.php (by hand) > http://munin-monitoring.org/browser/trunk/plugins/node.d/postgres_streaming_.in?rev=3905 > (in Perl) > http://archives.postgresql.org/pgsql-general/2010-10/msg00079.php (in C) > http://postgresql.1045698.n5.nabble.com/How-can-we-tell-how-far-behind-the-standby-is-td3252297.html > (in bash with bc(!), other links) > > What I keep meaning to write is something that does that as part of the SQL > itself, so it gets pulled out of the database already in bytes. > We have an open task to work on this same problem. What we had cobbled together so far was some sql which converted the xlog value into an integer (it's pretty ugly, but I could send it over if you think it would help), which we could then stick in a monitoring system and graph. To get an idea of traffic, I just multiplied this by 16MB. End result ended up looking like this: https://circonus.com/shared/graphs/9497d906-4c5b-e6d2-bf91-d8869e7c1668/OnxdZG Couldn't decide on exactly where to go from there. That's graphing MB/sec, which does map easily in my mind, since xlogs streams are in 16mb bursts. It would make more sense for wal streaming though (but in that case we'd probably want to measure it more precisely). Robert Treat play: http://xzilla.net work: http://omniti.com -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
