gregs1104 / pgtune Goto Github PK

PostgreSQL configuration wizard

Home Page: http://pgfoundry.org/projects/pgtune/

License: BSD 3-Clause "New" or "Revised" License

Python 100.00%

pgtune's Introduction

Introduction

pgtune takes the wimpy default postgresql.conf and expands the database server to be as powerful as the hardware it's being deployed on.

Installation/Usage

Source installation

There is no need to build/compile pgtune, it is a Python script. Extracting the tarball to a convenient location is sufficient. Note that you will need the multiple pg_settings-<version>_<architecture> files included with the program too, pgtune can't work without those.

RPM Installation

The RPM package installs:

The pgtune binary under/usr/bin

Documents in /usr/share/doc/pgtune-$version

Setting files in /usr/share/pgtune

Using pgtune

pgtune works by taking an existing postgresql.conf file as an input, making changes to it based on the amount of RAM in your server and suggested workload, and output a new file.

Here's a sample usage:

pgtune -i $PGDATA/postgresql.conf -o $PGDATA/postgresql.conf.pgtune

pgtune --help will give you additional usage information. These are the current parameters:

-i or --input-config : Specifies the current postgresql.conf file.

-o or --output-config : Specifies the file name for the new postgresql.conf file.

-M or --memory: Use this parameter to specify total system memory. If not specified, pgtune will attempt to detect memory size.

-T or --type : Specifies database type. Valid options are: DW, OLTP, Web, Mixed, Desktop

-P or --platform : Specifies platform, defaults to the platform running the program. Valid options are Windows, Linux, and Darwin (Mac OS X).

-c or --connections: Specifies number of maximum connections expected. If not specified, it depends on database type.

-D or --debug : Enables debugging mode.

-S or --settings: Directory where settings data files are located at. Defaults to the directory where the script is being run from. The RPM package includes a patch to use the correct location these files were installed into.

Todo

A TODO list is included in the tarball. There are also some TODO items marked in the source code itself.

Bugs

There aren't any known bugs, besides the cleanup areas mentioned in the source code with TODO tags. These shouldn't impact use of the program. If you find a bug, there is a tracker on the pgfoundry page for pgtune listed below.

Documentation

The documentation README.rst for pgtune is in ReST markup. Tools that operate on ReST can be used to make versions of it formatted for other purposes, such as rst2html to make a HTML version.

Contact

The project is hosted at http://pgfoundry.org/projects/pgtune

Initial commits are done in the git reportiory at http://git.postgresql.org/git/pgtune.git and http://github.com/gregs1104/pgtune

If you have any hints, changes or improvements, please contact:

Greg Smith [email protected]

License

pgtune is licensed under a standard 3-clause BSD license.

Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:

Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.

Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.

Neither the name of the author nor the names of contributors may be used to endorse or promote products derived from this software without specific prior written permission.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

pgtune's People

Contributors

Stargazers

Watchers

pgtune's Issues

support for postgresql 9.2, 9.3 and 9.4

While pgtune is a really valuable tool it seems that it getting behind postgresql versions.

Postgresql version hardcoded to 8.4 in line 342

Fails with KeyError: 'wal_buffers' using -V 9.1 against a 9.4.8 Pg (not supported I suppose)

[beans@intermine pgtune]$ psql --version
psql (PostgreSQL) 9.4.8

So that's what I'm running, but I tell pgtune that it's 9.1 since that's the latest settings file you've got in the repo:

[beans@intermine pgtune]$ ./pgtune -i postgresql.conf -o postgresql.conf.tuned -V 9.1 -c 100 -L
Traceback (most recent call last):
  File "./pgtune", line 698, in <module>
    sys.exit(main(sys.argv))
  File "./pgtune", line 687, in main
    wizard_tune(config, options, settings)
  File "./pgtune", line 593, in wizard_tune
    if settings.memory_unit(key):
  File "./pgtune", line 402, in memory_unit
    return (self.param_to_dict[setting])['memory_unit']
KeyError: 'wal_buffers'

Feature request: ALTER SYSTEM support

Subj.

9.3 support

Any plans for 9.3 support?

confusing bit of README

I think this sentence under "Source installation" is missing a chunk:

Note that you will need the multiple

PGTune generating "checkpoint_timeout" setting for v9.5 (unsupported)

9.4 support

It seems that we need support for 9.4 too.

pgtune is a great tool, don't let it die!

Homepage link dead.

The website link this GitHub repo points to, and the prior download link accessible through that site at http://pgfoundry.org/frs/download.php/2449/pgtune-0.9.3.tar.gz seem to be down. Unclear how long that has been the case.

Is that indicative of this project being unsupported now or a simple hosting/server error to resolve? Thanks!

Edit - Changed title for clarity.

memory size format not clear

"-M or --memory: Use this parameter to specify total system memory. If not specified, pgtune will attempt to detect memory size."

Do I specify it in bytes?

Support TB unit in config literals

We are using "TB" as a unit in our PostgreSQL config (on box with 3TB memory):

oberstet@bvr-sql18:~/scm/3rdparty/pgtune$ sudo ./pgtune --type=dw /data/adr/postgresql.conf
Traceback (most recent call last):
  File "./pgtune", line 698, in <module>
    sys.exit(main(sys.argv))
  File "./pgtune", line 687, in main
    wizard_tune(config, options, settings)
  File "./pgtune", line 596, in wizard_tune
    config.update_setting(key, settings.show(key, value))
  File "./pgtune", line 275, in update_setting
    current = self.current_value(name)
  File "./pgtune", line 242, in current_value
    current = self.settings.parse(name, self.param_to_line[name].value())
  File "./pgtune", line 473, in parse
    return str(self.parse_int(name, value))
  File "./pgtune", line 456, in parse_int
    internal = int(value)
ValueError: invalid literal for int() with base 10: '2TB'

Provide more detailed explanation of database types

In pg_tune there is an option to set the database type:
-T or --type : Specifies database type. Valid options are: DW, OLTP, Web, Mixed, Desktop

It would be helpful to provide more detailed explanation of the database types and what a user should consider when choosing one option over the others.

Thank you for all your hard work on pg_tune.

Postgres 9.5 deprecated checkpoint_segments, now uses min_wal_size, max_wal_size instead

I just ran pgtune. It suggested an adjustment to checkpoint_segments. However, according to https://www.postgresql.org/docs/9.6/static/release-9-5.html, checkpoint_segments is no longer present:

Replace configuration parameter checkpoint_segments with min_wal_size and max_wal_size (Heikki Linnakangas)

If you previously adjusted checkpoint_segments, the following formula will give you an approximately equivalent setting:

max_wal_size = (3 * checkpoint_segments) * 16MB

Note that the default setting for max_wal_size is much higher than the default checkpoint_segments used to be, so adjusting it might no longer be necessary.

9.2 Configuration file?

Is the 9.1 data file safe to use for configuring a 9.2? The diff between 9.0 and 9.1 are substantial so probably not?

Comments out listen_on addresses

Not a huge issue but it tripped me up for a bit. pgtune commented out the "listen_on" addresses field, which I had set to "*" to allow external requests.

BTW pgtune is really awesome.