Replication lag checks can block

We've now been hit twice by this today, 10:14 UTC, 11:18 UTC.

The following query can help diagnose the problem - if you see a log of replication_lag queries that's a problem:

select current_query from pg_Stat_activity where usename like 'lpnet%';

And again 13:44 UTC

Instead of querying the _sl.sl_status table, lets query a snapshot of it.

Create a simple script, lagmon.py, that mirrors this table to the ReplicationStatus table, along with the snapshot timestamp. It will do this every 5 seconds.

This script needs to be smart enough to reconnect when the database dies.

This script needs to be started on boot.

The replication_lag() and replication_lag(integer) stored procedures should be altered to query this mirrored table. Lag will be the mirrored lag, plus (CURRENT_TIMESTAMP - snapshot_timestamp).

When querying the replication status gets slow for whatever reason, it just slows down how fast lagmon.py can refresh the snapshot. All the existing systems keep going fine using worst potential lag. If lagmon.py crashes or is killed, everything will just see the lag increase and cope as normal.

We may want our nagios monitoring systems to use the real _sl.sl_status table (at the moment they use the replication_lag() stored procedures like the appservers), and monitor lagmon.py's output separately.

Thought about an alternative & potentially more efficient implementation:

lagmon.py as above, but stuffing the results into memcache rather than a DB table.
Appservers query memcached for lag, falling back to the replication_lag() stored procedures if memcache is down for any reason.

Problem with this approach is the appservers still need to query the database to determine what their slave node number is, which potentially could change between requests with database load balancers and config file reloading brought into the equasion.

Fixed in db r8891 <http://bazaar.launchpad.net/~launchpad-pqm/launchpad/db-devel/revision/8891>