On Sat, 2010-02-20 at 10:54 +0700, stuart at stuartbishop.net wrote:
> Hi.
> 
> I'm trying to get what information I can from Slony about current database write activity, in particular frequency of updates per table to help track down which parts of this chaotic collection of components I help run are chewing up the most resources.


Why don't you use the pg_stat_* and pg_statio_* tables in Postgres?

> Best I've got so far is the following:
> 
> SELECT
>     tab_relname,
>     count(*) AS total,
>     count(log_cmdtype = 'I' OR NULL) AS inserts,
>     count(log_cmdtype = 'U' OR NULL) AS updates,
>     count(log_cmdtype = 'D' OR NULL) AS deletes
> FROM (
>     SELECT tab_relname, log_cmdtype
>     FROM
>         _sl.sl_table,
>         _sl.sl_log_1,
>         (SELECT DISTINCT ON (ev_origin) ev_origin, ev_maxxid
>         FROM _sl.sl_event
>         WHERE ev_timestamp > CURRENT_TIMESTAMP - interval '1 minute'
>         ORDER BY ev_origin, ev_timestamp) AS event
>     WHERE
>         log_tableid = tab_id
>         AND log_origin = ev_origin
>         AND log_xid > ev_maxxid
>     UNION ALL
>     SELECT tab_relname, log_cmdtype
>     FROM
>         _sl.sl_table,
>         _sl.sl_log_2,
>         (SELECT DISTINCT ON (ev_origin) ev_origin, ev_maxxid
>         FROM _sl.sl_event
>         WHERE ev_timestamp > CURRENT_TIMESTAMP - interval '1 minute'
>         ORDER BY ev_origin, ev_timestamp) AS event
>     WHERE
>         log_tableid = tab_id
>         AND log_origin = ev_origin
>         AND log_xid > ev_maxxid) AS whatever
> GROUP BY tab_relname;
> 
> 
> I think this is giving me roughly the right answers over the last 60 seconds. Have I missed some data lurking in the internals that may be able to improve the accuracy? I'm running Slony-I 1.2 (.15 soon to move to .20).
> 
> 
> _______________________________________________
> Slony1-general mailing list
> Slony1-general at lists.slony.info
> http://lists.slony.info/mailman/listinfo/slony1-general
-- 
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.