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.

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).


-- 
Stuart Bishop <stuart at stuartbishop.net>
http://www.stuartbishop.net/

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 263 bytes
Desc: OpenPGP digital signature
Url : http://lists.slony.info/pipermail/slony1-general/attachments/20100220/a24d97c9/attachment.pgp