Fri Feb 19 19:54:38 PST 2010
- Previous message: [Slony1-general] Unclean Slony tables
- Next message: [Slony1-general] Using Slony tables to monitor database write activity
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
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
- Previous message: [Slony1-general] Unclean Slony tables
- Next message: [Slony1-general] Using Slony tables to monitor database write activity
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
More information about the Slony1-general mailing list