Metrics code filter - Postgres

The Metrics code filter collects data on incoming and outgoing traffic and presents that data to user code. It is similar to the Metrics filter, but it leaves it up to user logic to present the information.

This filter has no effect on the requests or responses.

Algorithm

This filter works by keeping track of certain packets from the client and from the server to figure out what SQL commands are being executed, how long they take, and how much data they return.

The user code is called at an interval set by the Output frequency parameter, but this parameter is only a minimum. Once the interval has elapsed, the user code will be invoked next time a relevant request or response packet is received.

To determine the most significant SQL commands, this filter uses a simple formula that takes into account the number of times the SQL command has been invoked, and how long it has been since its last invocation.

Metrics format

The user code in this filter will be invoked with a variable called context.stats that has the following format:

{
"Statement infos": [
{
"Average rows": <int>,
"Average bytes": <int>,
"Num execs": <int>,
"Total exec": <int>,
"Statement": <string>,
"Total rows": <int>,
"Average exec": <float>,
"Total bytes": <int>
}, ...
],
"Packet stats": [
{
"Packet type": <string>,
"Packets out": <int>,
"Bytes out": <int>,
"Packets in": <int>,
"Bytes in": <int>
}, ...
]
}

The "Statement infos" array will contain up to the number specified by the Max reports parameter.

Example

The following is an extract of a real-life set of metrics, serialized with JSON.stringify(context.stats, null, 2) :

{
"Statement infos": [
{
"Average rows": 740,
"Average bytes": 25700,
"Num execs": 6,
"Total exec": 86,
"Statement": "SELECT COLLATION_SCHEMA, COLLATION_NAME FROM INFORMATION_SCHEMA.COLLATIONS",
"Total rows": 4440,
"Average exec": 14.12,
"Total bytes": 154200
},
{
"Average rows": 9,
"Average bytes": 480,
"Num execs": 12,
"Total exec": 78,
"Statement": "SELECT n.nspname AS \"Name\", r.usename AS \"Owner\", pg_catalog.obj_description(n.oid, 'pg_namespace') AS \"Comment\" FROM pg_catalog.pg_namespace n LEFT JOIN pg_catalog.pg_user r ON n.nspowner= r.usesysid",
"Total rows": 108,
"Average exec": 7.82,
"Total bytes": 5760
}
],
"Packet stats": [
{
"Packet type": "CommandComplete",
"Packets out": 93,
"Bytes out": 1143,
"Packets in": 0,
"Bytes in": 0
},
{
"Packet type": "Execute",
"Packets out": 0,
"Bytes out": 0,
"Packets in": 93,
"Bytes in": 837
}
]
}

A trivial example of code to handle this:

let si = context.stats["Statement infos"];
for (let c in si) {
log.info("SQL: " + si[c]["Statement"] + " took " + si[c]["Total exec"] + "ms.");
}