Skip to content
A tool for tweaking/optimzing postgres settings using queries
Python
Find file
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Failed to load latest commit information.
bin
pgtweaklib
test
LICENSE
MANIFEST.in
README.rst
setup.py

README.rst

PgTweak

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

Scouring logs for queries

Run the following to pull queries from log files:

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

Getting a query file

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

Generating a sample config 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.)

Testing settings

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.

Settings and explanations

Explain settings (TODO)

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

random_page_cost

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

general settings

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

Enabling Logging

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
Something went wrong with that request. Please try again.