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.
Parameters
This filter takes the following parameters:
Output frequency : how often, at minimum, the user code will be called, in seconds.
Max statements : how many SQL statements to track at the same time. More statements will make the statistics more accurate, at the cost of more memory use.
Max reports : the maximum number of top statements to report as part of the statistics.
Clean SQLs : if true, the SQL statements will be made more readable by removing all tabs, line feeds, consecutive spaces and such.
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.");
}