Keith's Ramblings…

WARNING: If accidentally read, induce vomiting

Monitoring Streaming Slave Lag Effectively

with 2 comments

One of the easiest ways to monitor slave lag when using streaming replication is to turn hot standby on your slave and use pg_last_xact_replay_timestamp() and/or the other recovery information functions. Here’s an example query to run on the slave systems to get the number of seconds behind it is:

The issue with this query is that while your slave(s) may be 100% caught up, the time interval being returned is always increasing until new write activity occurs on the master that the slave can replay. This can cause your monitoring to give false positives that your slave is falling behind if you have things set up to ensure your slaves are no more than a few minutes behind. A side affect of this monitoring query can also give you an indication that writes to your master have stopped for some reason.

One of our clients has a smaller sized database that doesn’t get quite as much write traffic as our typical clients do. But it still has failover slaves and still needs to be monitored just like our other larger clients to ensure it doesn’t fall too far behind. So, my coworker introduced me to the pg_stat_replication view that was added in PostgreSQL 9.1. Querying this from the master returns information about streaming replication slaves connected to it.

He also provided a handy query to get back a simple, easy to understand numeric value to indicate slave lag. The issue I ran into using the query is that this view uses pg_stat_activity as one of its sources. If you’re not a superuser, you’re not going to get any statistics on sessions that aren’t your own (and hopefully you’re not using a superuser role as the role for your monitoring solution). So, instead I made a function with SECURITY DEFINER set, made a superuser role the owner, and gave my monitoring role EXECUTE privileges on the function.

Running this query gives back a few handy columns that should be good enough for most monitoring tools. You can easily add more columns from pg_stat_replication or any other tables you need to join against for more info.

UPDATE: If you’re running PostgreSQL 9.2+, there is a new, built-in function that avoids needing the above function all together and can just query pg_stat_replication directly.

This can also be useful to monitor slave lag when you don’t have hot standby turned on for your slaves to allow read-only queries.

Combining both of the replication monitoring solutions mentioned in this post should give you a much better overall picture of the status of your master/slave systems.

Written by Keith

October 30th, 2013 at 10:51 am

Posted in PostgreSQL

Tagged with , ,

  • Alex Hunsaker

    Great post, Thanks!.

    I noticed this only takes into account what has been sent to a slave (sent_location – replay_location), not the total lag from the master (pg_current_xlog_location – replay_location).

    If, for whatever reason (huge table update/rewrite) sending gets behind, it can be gigabytes behind the master. While, for me, it was only report the lag as being tens of MB.

    Here is a version that reports both the total lag (using pg_current_xlog_location(), and the old metric (which I would call replay lag). Sorry about the formatting :(:

    SELECT
    client_hostname,
    client_addr,
    ( (cur_xlog * 255 * 16 ^ 6) + cur_offset) – ((replay_xlog * 255 * 16 ^ 6) + replay_offset) as total_lag,
    pg_size_pretty((( (cur_xlog * 255 * 16 ^ 6) + cur_offset) – ((replay_xlog * 255 * 16 ^ 6) + replay_offset))::numeric) as total_nice,
    ((sent_xlog * 255 * 16 ^ 6) + sent_offset) – ((replay_xlog * 255 * 16 ^ 6) + replay_offset) as byte_lag,
    pg_size_pretty((( (sent_xlog * 255 * 16 ^ 6) + sent_offset) – ((replay_xlog * 255 * 16 ^ 6) + replay_offset))::numeric) as byte_nice
    FROM (
    SELECT
    client_hostname,
    client_addr,
    (‘x’ || lpad(split_part(sent_location, ‘/’, 1), 8, ’0′))::bit(32)::bigint AS sent_xlog,
    (‘x’ || lpad(split_part(replay_location, ‘/’, 1), 8, ’0′))::bit(32)::bigint AS replay_xlog,
    (‘x’ || lpad(split_part(sent_location, ‘/’, 2), 8, ’0′))::bit(32)::bigint AS sent_offset,
    (‘x’ || lpad(split_part(replay_location, ‘/’, 2), 8, ’0′))::bit(32)::bigint AS replay_offset,
    (‘x’ || lpad(split_part(pg_current_xlog_location(), ‘/’, 1), 8, ’0′))::bit(32)::bigint as cur_xlog,
    (‘x’ || lpad(split_part(pg_current_xlog_location(), ‘/’, 2), 8, ’0′))::bit(32)::bigint as cur_offset

    FROM pg_stat_replication

    ) AS s;

    • keithf4

      Thanks for the improved query!