Tuning Postgresql-9.0 with pgtune

Presuming you have PostgreSQL-9.0 installed, you are probably running the default configuration. The default configuration is designed to work with a variety of hardware, and is tuned for the weakest possible machine within its hardware profile. The official documentation doesn’t spell out the minimum requirement for the operating systems we use at LTS-Lyrasis, except to say:

  • Special Cygwin instructions may allow you to run versions of Windows older than Windows 20001.
  • On Linux, such as Ubuntu, there are some minimum software version numbers and minimum hard drive sizes
  • The minimum required version is Python 2.2. This came out in December 21, 20012
  • You will need about 100 MB for the source tree during compilation and about 20 MB for the installation directory. An empty database cluster takes about 35 MB; databases take about five times the amount of space that a flat text file with the same data would take. If you are going to run the regression tests you will temporarily need up to an extra 150 MB.3

Pgtune will help you set up PostgreSQL 9.0 to work well with your current hardware.

Install PGTune

# aptitude install pgtune

Run PGTune

# pgtune -i /etc/postgresql/9.0/main/postgresql.conf -o psql.conf
This puts the file, psql.conf, with the tuning suggestions in root’s home folder

I like using the screen command to move from one command-line page to new command-line page
# aptitude install screen

Using the Screen Command

Since we are doing a lot as root, I am using the “#” sign rather than saying “sudo” over and over. If you want to stay root for a while, you can enter:
$ sudo -i
You will be permanently root until you type “exit”
# screen

Very Basic Screen Commands

Note “^” is shorthand for the Control key, and “#” either means you are acting as the root user or it shows a comment. Let me know where this is confusing in the following How-to, and I will expand the how-to.
^A # <Ctrl>A tells screen that you want to send it a command (“^A” alone does nothing)
^A c #tells screen to make a new screen
^A n # go to next screen
^A 3 # go to screen number 3
– all numbers to be the equivalent of the screens you have running. I put one root session for restarting postgresql on one screen, and another root session for editing postgresql.conf on a second screen within screen.

    ,

  1. since screen is open
    ^A c
    If you ^A n first, screen will tell you there are no other screens.
  2. Using the VI editor, open the pgtune file you just made.
    # vi /root/psql.conf

    1. <shift>G to go to bottom of the file
    2. position your cursor at the top of the block marked
      “CUSTOMIZED OPTIONS”
      pgtune1
    3. type “v” for “visual.”
    4. use your down-cursor arrowPGTune2 and the right-cursor  to highlight all of the customizations.
    5. Hit “y” to copy the text to a buffer. The highlighting will vanish.
      pgtune3
  3. ^A n takes you to your other screen.
  4. Make a backup of your config file
    # cp /etc/postgresql/9.0/main/postgresql.conf /etc/postgresql/9.0/main/postgresql.conf.bak
    You will thank yourself for taking this step
  5. In a second ‘screen’ open your active Postgresql configuration file for editing
    # vi /etc/postgresql/9.0/main/postgresql.conf
    opens the file for editing.
    <Shift>G takes you to the bottom of the file
  6. I like to organize the end of the file a little.
    1. Hit the “I” key for “Insert Mode”
    2. Move the cursor to the end of the last visible line with the <End> key (above the cursor arrows block, usually) or just use the right cursor key to get there
    3. Hit enter to add a blank line at the bottom of the page
    4. Mash <Esc> to go to “command mode”
    5. <Shift>P inserts the lines you saved from the other file.
    6. Mash <I> and put a pound sign and a space,“# “, in front of each of the lines you copied over. This keeps them inert until you are ready to use them. It also means that you do not have to delete the block when you are done, if you don’t choose to do so.
      pgtune4
  7. To put the line items in the same places as the defaults, you need to visually highlight and then yank one line (or part of one line) at a time (just like you did to get the lines here).
  8. While staying in command mode, type a forward slash and the first couple of words of the line you are searching for
    /max_connections
    vi will move you to the top of the file and search for the string you entered.
  9. Position your cursor on the line underneath the existing line and
    <Shift>P to “put” the line in right there.
  10. If the line you are replacing has a pound sign “#” at the beginning, it means that it was not a live directive. If it doesn’t have a “#” sign at the front,
    1. Mash <i> to go into insert mode
    2. Position the cursor at the left end of the line and put in the “#” sign
      pgtune5
  11. <Esc> to go back to command mode, and
  12. <Shift>G to get back to the bottom of the document
  13. Mash “:w” to save the change. “:wq” dumps you out of vi, and that can be confusing.
  14. ^A to go back to the first screen
  15. If, when you get back to the first screen, vi is still open and showing you the pgtune file, Hit :wq to exit the file.
    pgtune6
  16. # /etc/init.d/postgresql restart
    If there is an error, it will probably be about shmmax. In that case, jump to the section about setting the kernel.shmmax to a larger size
  17. If there is no problem starting PostgreSQL, Go back to Direction 3 above and repeat until done.
  18. If there is an error, it will probably be about shmmax. In that case, jump to the section about setting the kernel.shmmax to a larger size.pgtune7

Shmmax!

To adjust the shmmax – to at least what the system says you need after tuning the database.

# sysctl -w kernel.shmmax=<something bigger than the number the postgresql.conf error shows>
For instance
# sysctl -w kernel.shmmax=1102069760
pgtune8
If the error shows up again, look at the number it is looking for and reset kernel.shmmax to that number.
# sysctl -w kernel.shmmax=1110401024 (happened when increasing wal_buffers)

Close out screen

  1. The screen you were on, at the end is the one where you were restarting the PostgreSQL server. If you are still on your open vi session, mash “:wq!” to force a save and a quit to get you out of vi.
  2. Type exit on the command line. The screen you are on will close.
  3. Type exit again. Screen will tell you it is terminating
  4. Type exit again, to end the root session
  5. Type exit again, and (probably)
    1. the ssh session will end, if you are shelled into the server from a remote host.
    2. the system will log you out, if you are logged into the command line locally (runlevel 3)
    3. or the terminal window will close, if you are using the terminal application on the server’s graphic user interface

One interesting note about screen: If you are used to being able to use your up-cursor to scroll back through your commands, you will discover that what you do in screen remains in screen. That history will be unavailable once you end your screen session.

6 comments

  • TOZ on the UbuntuForums.org told me that I should add “kernel.shmmax = ” to the end of the /etc/sysctl.conf file. This hard-coded the fix into sysctl so I do not have to do the
    # sysctl -w kernel.shmmax=1102069760 command at all.
    Thanks TOZ!

    Wolf
    Wolf Halton´s last blog post ..State of WolfHalton.info 2011

  • Pingback: Tuning Postgresql-9.1 com pgtune | Amarildo Sertório

  • Thanks for nice guide, I m happy that PGtune is included in Debian repos. I’m having hard time compiling any app because off missing libraries.

  • In production, I work hard to avoid having to compile from source. In most cases I have been successful. That being said, compiling seems to work best when you read all the documentation (even the “README” file) related to the application. I have been pleasantly surprised to find the developers have included the unusual dependencies in the documentation from time to time. Circular dependencies are the worst. You can’t install A until you have B and you can’t install B until you have A installed.

  • Thanks a lot for your help. Such detailed description of how to install and run Pgtune will actually help me setup PostgreSQL 9.0 on my system and let’s hope it will work well with my current hardware, as you claim.

  • Thanks for the guide.

    Note: pgtune is available on EPEL repository for CentOS systems.
    Marios Zindilis´s last blog post ..openDCIM Installation on CentOS

Leave a Reply