Re: SSDs with Postgresql?

On Thu, Apr 21, 2011 at 12:10 PM, Greg Smith <greg(at)2ndquadrant(dot)com> 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