@metis-data/slow-query-log

Metis Slow Query Log

Documentation

Overview

This is a Metis package that enables postgres slow query log and auto analyze. Using postgres extensions pg_store_plans/file_fdw/log_fdw it collects relevant queries from databases along with their execution plans. Those queries can be exported to Metis platform to be analyzed and monitored. The extension that will be used is pg_store_plans if available, or file_fdw/log_fdw. Our recommendation is to set log_min_duration_statement to 10, to avoid logging relative fast queries and log_statement_sample_rate to 0.01 to reduce log files size. Files above 512mb are consider to be too large and are not supported at the moment.

Note: compute_query_id flag which is part of the feature is available from postgres version 14 or later.

Usage

• Run

npm install --save @metis-data/slow-query-log

• Set the collector from your code:

// With autoRun enabled
import { MetisSqlCollector } from '@metis-data/slow-query-log';

const metis = new MetisSqlCollector({ autoRun: true });
await metis.setup();

// Without autoRun
import { MetisSqlCollector } from '@metis-data/slow-query-log';

const metis = new MetisSqlCollector();
await metis.setup();

// Call this function to send slow query logs from last 2 log files 
// with logs that added after the last call to run(), (or 1 minute on first call)
await metis.run();

• Options:

  Options can be set from the constructor or from environment variables

  • autoRun: will send slow query log automatically every 1 minute, if set to false, calls to run() should be handled manually
  • exportResults: if true, will send each run() call results to Metis platform
  • connectionString: database url, must be set from this configuration or DATABASE_URL
  • metisApiKey: api key generated from metis platform, must be set from this configuration or METIS_API_KEY
  • logFetchInterval: intervals of fetching logs by millisecond, 1 minute by default
  • serviceName: service name to appear on Metis platform, "default" string by default
  • logger: must implement log and error functions, by default { log: console.log, error: console.error }

• Environment variables:

  Setting the environment variables is equivalent to some of the options above and only one of them is needed

  • DATABASE_URL: same as options.connectionString
  • METIS_API_KEY: same as options.metisApiKey
  • LOG_FETCH_INTERVAL: same as options.logFetchInterval
  • METIS_SERVICE_NAME: same as options.serviceName

• Database setup:

  This package tries to install postgres file_fdw/log_fdw extension, so the connection must be of a user with the appropriate permissions.

  For managed databases (like aws rds) the next parameters must be set:

parameter	value	db needs restart?
shared_preload_libraries	auto_explain	yes
logging_collector	'on'	yes (locally)
log_destination	'csvlog'	yes (locally)
log_filename	'postgresql.log.%Y-%m-%d-%H'	yes (locally)
log_rotation_age	60	yes (locally)
auto_explain.log_min_duration	10	no
auto_explain.log_format	'json'	no
auto_explain.log_analyze	true	no
auto_explain.log_buffers	true	no
auto_explain.log_timing	true	no
auto_explain.log_verbose	true	no
auto_explain.log_nested_statements	true	no
log_statement	'mod'	no
log_statement_sample_rate	0.01	no
log_min_duration_statement	10	no
compute_query_id	'on'	no

• RDS setup using aws cli: If it is the first time of enabling postgres logs on RDS, a new parameter group should be created with logging_collector=on.

  After enabling slow query log in your RDS, the rest of postgres variables can be set with aws cli:

  aws rds modify-db-parameter-group \
    --db-parameter-group-name your-parameter-group-name \
    --parameters \
      "ParameterName=shared_preload_libraries,ParameterValue=auto_explain,ApplyMethod=pending-reboot" \
      "ParameterName=log_destination,ParameterValue=csvlog,ApplyMethod=immediate" \
      "ParameterName=log_filename,ParameterValue=postgresql.log.%Y-%m-%d-%H,ApplyMethod=immediate" \
      "ParameterName=log_rotation_age,ParameterValue=60,ApplyMethod=immediate" \
      "ParameterName=log_statement,ParameterValue=mod,ApplyMethod=immediate" \
      "ParameterName=log_statement_sample_rate,ParameterValue=0.01,ApplyMethod=immediate" \
      "ParameterName=log_min_duration_statement,ParameterValue=10,ApplyMethod=immediate" \
      "ParameterName=compute_query_id,ParameterValue=on,ApplyMethod=immediate" \
      "ParameterName=auto_explain.log_format,ParameterValue=json,ApplyMethod=immediate" \
      "ParameterName=auto_explain.log_min_duration,ParameterValue=10,ApplyMethod=immediate" \
      "ParameterName=auto_explain.log_analyze,ParameterValue=true,ApplyMethod=immediate" \
      "ParameterName=auto_explain.log_buffers,ParameterValue=true,ApplyMethod=immediate" \
      "ParameterName=auto_explain.log_timing,ParameterValue=true,ApplyMethod=immediate" \
      "ParameterName=auto_explain.log_verbose,ParameterValue=true,ApplyMethod=immediate" \
      "ParameterName=auto_explain.log_nested_statements,ParameterValue=true,ApplyMethod=immediate"
  
  # reboot to apply shared_preload_libraries, this set will override an exists values
  # so if another library is needed make sure to add it to the string command
  aws rds reboot-db-instance --db-instance-identifier your-db-instance-id
  

• Docker/local database setup:

  If you are using postgres on docker container, you should set the required database parameters in the docker-compose file:

  version: '3.1'
  
  services:
    db:
      image: postgres
      environment:
        POSTGRES_USER: postgres
        POSTGRES_PASSWORD: postgres
  
      command: postgres 
          -c shared_preload_libraries=auto_explain
          -c logging_collector=on 
          -c log_destination=csvlog 
          -c log_filename=postgresql.log.%Y-%m-%d-%H 
          -c log_rotation_age=60
          -c log_statement=mod
          -c log_statement_sample_rate=0.01
          -c log_min_duration_statement=10
          -c compute_query_id=on
          -c auto_explain.log_format=json
          -c auto_explain.log_min_duration=10
          -c auto_explain.log_analyze=true
          -c auto_explain.log_buffers=true
          -c auto_explain.log_timing=true
          -c auto_explain.log_verbose=true
          -c auto_explain.log_nested_statements=true
  #...
  

  If you are using any other local server, make sure to set those parameters in postgres config file postgresql.conf and restart the server.

Issues

If you would like to report a potential issue please use Issues