1. David Kerr
  2. mrtuner

Overview

HTTPS SSH

mrtuner

This gem can be used to help set sane defaults for your PostgreSQL instances. It can be used in a ruby program to give programmatic access to those defaults.

Requirements

  • Ruby 2.0.0 and up
  • PostgreSQL 9.3+ (Not require to run, but these tunables expect features of PG 9.3)

Installation

gem install mrtuner

If you're using rbenv you'll want to use

rbenv rehash

To add the shim for the binary.

Commandline Usage

mrtuner -h
--type, -t <s>:   Type of Database Server (Web/DW/Mixed/Desktop)
--memory, -m <f>:   Amount of Memory on the Server (GiB)
--connections, -c <i>:   Specify the Target Max Connections
--oversubscribe, -o <i>:   Specify an Oversubscribe target for connections
--help, -h:   Show this message

I just report out key tunables, so not an entire config file In general I would do something like:

echo "$PGDATA/local.conf" >> $PGDATA/postgresql.conf
mrtuner -t web -m 2 -c 200 > $PGDATA/local.conf

Oversubscribing

While not encouraged it's often the reality that you want to tune your database for a sane number of connections but allow more. This is especially true when working with persistent connection pools across multiple applications, where most of the time you can expect any single connection to be active, but you don't expect all connections to be used at the same time.

To accommodate this i've created the oversubscribe flag on mrtune. What this will do is tune your database for --connections connections but it will set max_connections to --oversubscribe

Embeded Usage

require 'mrtuner'

tune = MrTuner.new 'web', 2, { connections => 200 }

MyConfig = tune.get_config

puts MyConfig # => {:max_connections=>200, :maintenance_work_mem=>134217728.0, :work_mem=>10485760.0, :checkpoint_completion_target=>0.7, :shared_buffers=>536870912.0, :effective_cache_size=>2147483648.0}

ToDo

  • Remove dependency on Filesize (AKA quit being lazy)
  • Allow modification of the values and have that affect other areas of the config.
  • Maybe an option to write an entire config file.
  • Tests

Acknowledgements

  • I use Greg Smith's pgtune a lot and this is built in the spirit of that software.