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.
# aptitude install 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”
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.
- since screen is open
If you ^A n first, screen will tell you there are no other screens.
- Using the VI editor, open the pgtune file you just made.
# vi /root/psql.conf
<shift>Gto go to bottom of the file
- position your cursor at the top of the block marked
- type “v” for “visual.”
- use your down-cursor arrow and the right-cursor to highlight all of the customizations.
- Hit “y” to copy the text to a buffer. The highlighting will vanish.
^A ntakes you to your other screen.
- 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
- 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
- I like to organize the end of the file a little.
- Hit the “I” key for “Insert Mode”
- 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
- Hit enter to add a blank line at the bottom of the page
- Mash <Esc> to go to “command mode”
- <Shift>P inserts the lines you saved from the other file.
- 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.
- 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).
- While staying in command mode, type a forward slash and the first couple of words of the line you are searching for
vi will move you to the top of the file and search for the string you entered.
- Position your cursor on the line underneath the existing line and
<Shift>P to “put” the line in right there.
- 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,
- Mash <i> to go into insert mode
- Position the cursor at the left end of the line and put in the “#” sign
- <Esc> to go back to command mode, and
- <Shift>G to get back to the bottom of the document
- Mash “:w” to save the change. “:wq” dumps you out of vi, and that can be confusing.
^Ato go back to the first screen
- 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.
# /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
- If there is no problem starting PostgreSQL, Go back to Direction 3 above and repeat until done.
- 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.
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>
# sysctl -w kernel.shmmax=1102069760
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
- 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.
- Type exit on the command line. The screen you are on will close.
- Type exit again. Screen will tell you it is terminating
- Type exit again, to end the root session
- Type exit again, and (probably)
- the ssh session will end, if you are shelled into the server from a remote host.
- the system will log you out, if you are logged into the command line locally (runlevel 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.