Metrics filter - Postgres

The Metrics filter collects information about what SQL commands are being run, how long they take to execute, and how much data they return.

This filter is a duplex filter because it needs to collect information both from the client and the server. For instance, when the client sends a Query packet, this filter makes a note of it and starts a timer. It will then keep track of DataRow packets being sent back as a result of this query.

This filter can output a summary of what it has seen so far (since the Gallium Data engine started) either in a file, or to a logger, or to standard out.

Note that the SQL statements are "cleaned up" for display, meaning extra spaces and line breaks are removed. This is only for display, obviously this filter does not affect the actual statements.

Parameters

This filter takes the following parameters:

  • Output type : can be either file (output to a file), log (output to a logger as info messages), or out (output to standard out).

  • Output name : if the output is set to file, this should be the name of the file to write to. If the output is set to log, this should be the name of the logger to write to.

  • Use timestamp : if the output is set to file, selecting this option will cause a timestamp to be added to the file name.

  • Output frequency : how often to output a report, in minutes. This is a minimum: if there is no activity, no report will be output.

  • Max statements : this filter keeps track of the top 200 statements by default, you can change that number here.

  • Max reports : when outputting a report, only the top 10 statements will be shown by default. You can change that number here.

Example

Typical output from this filter:

Packet type: Parse

bytes in:61973

packets in:212

bytes out:0

packets out:0


Packet type: ReadyForQuery

bytes in:0

packets in:0

bytes out:1135

packets out:227


Packet type: DataRow

bytes in:0

packets in:0

bytes out:390896

packets out:10929


Statement: SELECT c.oid, a.attnum, a.attname, c.relname, n.nspname, a.attnotnull OR (t.typtype = 'd' AND t.typnotnull), a.attidentity != '' OR pg_catalog.pg_get_expr(d.adbin, d.adrelid) LIKE '%nextval(%' FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON (c.relnamespace = n.oid) JOIN pg_catalog.pg_attribute a ON (c.oid = a.attrelid) JOIN pg_catalog.pg_type t ON (a.atttypid = t.oid) LEFT JOIN pg_catalog.pg_attrdef d ON (d.adrelid = a.attrelid AND d.adnum = a.attnum) JOIN (SELECT 2615 AS oid , 2 AS attnum UNION ALL SELECT 12100, 1) vals ON (c.oid = vals.oid AND a.attnum = vals.attnum)

# execs: 14

total: 126.5ms

avg: 9.0ms

total rows: 28

avg rows/req: 2.0

total bytes: 4466

avg bytes/req: 319.0


Statement: SELECT 1 FROM pg_catalog.pg_namespace WHERE nspname = 'pgagent'

# execs: 14

total: 59.9ms

avg: 4.3ms

total rows: 0

avg rows/req: .0

total bytes: 462

avg bytes/req: 33.0


Statement: SELECT c.oid, a.attnum, a.attname, c.relname, n.nspname, a.attnotnull OR (t.typtype = 'd' AND t.typnotnull), a.attidentity != '' OR pg_catalog.pg_get_expr(d.adbin, d.adrelid) LIKE '%nextval(%' FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON (c.relnamespace = n.oid) JOIN pg_catalog.pg_attribute a ON (c.oid = a.attrelid) JOIN pg_catalog.pg_type t ON (a.atttypid = t.oid) LEFT JOIN pg_catalog.pg_attrdef d ON (d.adrelid = a.attrelid AND d.adnum = a.attnum) JOIN (SELECT 1262 AS oid , 1 AS attnum UNION ALL SELECT 1262, 2 UNION ALL SELECT 1262, 3 UNION ALL SELECT 1262, 4 UNION ALL SELECT 1262, 5 UNION ALL SELECT 1262, 6 UNION ALL SELECT 1262, 7 UNION ALL SELECT 1262, 8 UNION ALL SELECT 1262, 9 UNION ALL SELECT 1262, 10 UNION ALL SELECT 1262, 11 UNION ALL SELECT 1262, 12 UNION ALL SELECT 1262, 13 UNION ALL SELECT 1262, 14) vals ON (c.oid = vals.oid AND a.attnum = vals.attnum)

# execs: 14

total: 46.9ms

avg: 3.3ms

total rows: 196

avg rows/req: 14.0

total bytes: 16478

avg bytes/req: 1177.0