A tool to run queries against different postgres settings. Normally one would scour the postgres logs for slow queries.

Author: Matt Harrison (matthewharrison@gmail.com) LICENSE: MIT

Run the following to pull queries from log files:

pgtweak  --extract-queries --log-file some/pg/log/file > extracted.sql

One can go through extracted.sql to find a query to example, or hand write them in a text file.

A config file is used to indicate what settings to tweak. It is a json file that looks like this:

[
  [
    "work_mem", 
    [
      "1MB", 
      "512MB"
    ]
  ], 
  [
    "maintenance_work_mem", 
    [
      "1MB", 
      "512MB"
    ]
  ]
]

That is the one generated by:

pgtweak --gen-config

You can add more settings and possible values for settings. Remember though, that it will test the combination of each setting. For every new value option you add to a setting, you double the runs. (Note that just adding an setting with a single value just includes that in every run.)

Run:

pgtweak --query-file simple.sql --analyze-queries --dburl postgres://postgres@localhost/testetl --config-file conf.json > results.json

Note that dburl is a SqlAlchemy style connection string like postgres://user:password@host:post/database

The results.json file contains JSON list containing the query, time to run, results, and settings for that query.

Explain settings (TODO)

http://www.varlena.com/GeneralBits/Tidbits/annotated_conf_e.html

http://archives.postgresql.org/pgsql-performance/2004-02/msg00287.php

http://archives.postgresql.org/pgsql-hackers/2008-11/msg00919.php

Need to turn on Postgres logging in postgresql.conf:

log_destination = stderr
logging_collector = on
log_filename = 'pgsql-%Y-%m-%d.log'
log_min_error_statement = 'error'
log_connections = false
log_line_prefix = '<%t>'
log_statement = all
log_directory = '/var/log/pglog'

• Make sure log_directory exists and has correct permissions
• Restart postgres