Re: SSDs with Postgresql?

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.

--
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books