Performance Tuning PostgreSQL

Frequently Asked Questions/Answers, Tutorials, and Common How To's

Moderator: Moderators

Performance Tuning PostgreSQL

Postby APerfect10 » Mon Oct 27, 2008 11:50 pm

This tutorial is intended for PokerTracker 3 users who would like to increase the speed of data retrievals, housekeeping, etc.

PokerTracker 3 uses the PostgreSQL database server to store your hands and information/statistics about your hands. All databases are extremely file intensive as data is constantly being write and read from your hard drive. Hard drives are very slow and take a long time to retrieve data. We can minimize the number of times PostgreSQL is required to read from your hard drive by storing pieces of your database into memory (RAM) which is very fast. By default, PostgreSQL is configured to use a very small amount of memory which is far from optimum. This tutorial will tweak the PostgreSQL configurations so that more memory is used and in turn PokerTracker 3/PostgreSQL performance is increased.

Recommend Method
This method requires PostgreSQL 8.3.x which is the version of PostgreSQL currently packaged with PokerTracker 3. You can determine your version of PostgreSQL by going Start->Run->Programs->PostgreSQL->Command Prompt then typing the following at the command prompt:

Code: Select all
psql --version

  1. Go Start->Programs->PostgreSQL 8.3 and look for "Application Stack Builder". If listed, please jump to step 10.
  2. Uninstall PostgreSQL via the Windows control panel (your data will be preserved)
  3. (Vista users only) Make sure that User Account Control is disabled.
  4. Start->Run, 'cmd' -> OK. At the command prompt type "net user postgres /delete" (no quotes) and hit enter.
  5. Download the latest version of PostgreSQL 8.3.x from the PostgreSQL website. (Choose the latest version -> select 'win32' -> select 'postgresql-8.3.x.zip' -> click either http or ftp under your country's flag)
  6. After download has completed, double click the file to open then extract the contents of the downloaded zip file.
  7. Double click and run 'postgresql-8.3.msi' to manually re-install PostgreSQL 8.3.x
  8. When you get to the screen to "Initialize your Cluster", uncheck the box to skip this step. (This is not needed since your data directory is already configured and in tact from the previous installation)
  9. At the end of the installation...
  10. Launch the "Application Stack Builder"
    1. Select your PostgreSQL Database Server and click 'Next'.
    2. Expand "AddOns" and check "EnterpriseDB Tuning Wizard
    3. Download the tuning wizard by selecting your country and continuing
    4. Continue through the end of installation and ...
  11. Launch the EnterpriseDB Tuning Wizard
    1. Select your PostgreSQL Database server and click 'Next'
    2. Under "Server Utilization" select "Mixed" and click 'Next'.
    3. Continue through the installation of the tuning wizard
PostgreSQL memory usage has now been configured specifically for your computer's specifications and performance should be improved greatly. You can now proceed to use PokerTracker 3.


Advanced Method
This method should only be performed by advanced computer users who are familiar with PostgreSQL configurations.

  1. Open 'My Computer' and browse to your C:\Program Files\PostgreSQL\8.x\data directory and make a copy of "postgresql.con" as a backup.
  2. Open the original postgresql.conf file in Notepad or go Start->Programs->PostgreSQL->Configuration Files->Edit postgresql.conf
  3. Find the following configurations:
    • "shared_buffers = xMB" and set this number equal to about 1/4 of your total memory. That is, if you have 2GB of RAM, set this to 512MB.
    • "work_mem = xMB" and set this value equal to about 1/64 of your total memory. That is, if you have 2GB of RAM, set this to 32MB.
    • "maintenance_work_mem = xMB" and set this value equal to about 1/16 of your total memory. That is, if you have 2GB of RAM, set this to 128MB.
      Note: work_mem and maintentance_work_mem by default are commented out in the configuration file (e.g. has a # in front of them), so make sure to remove those symbols to put the changes into effect.
  4. File->Save to save the changes
  5. Go Start->Programs->PostgreSQL->Reload Configuraitons for the changes to take effect.
VERY IMPORTANT:
The exact configuration values are more of an art than a science. While these values will work for most users, it could potentially cause problems for other users. If you are experiencing issues, lower the values or follow the "Recommend Method" above.

Vista Users:.
If you receive an error message saying that the file can't be written when you try to save the configuration file after having made these changes, make sure that you're running as administrator. To do that, go Start->Programs->PostgreSQL->Configuration Files and now right-click on "Edit postgresql.conf" and choose "Run as administrator".

Linux Users:
If you have PostgreSQL running on a Linux machine, you may receive the following error message when changing these settings:
Code: Select all
EDT DETAIL: Will not verify client certificates.
EDT FATAL: could not create shared memory segment: Invalid argument
EDT DETAIL: Failed system call was shmget(key=5432001, size=549330944, 03600).

If you get this message, you need to edit /etc/sysctl.conf and add the following line:

kernel.shmmax = N

Where you need to replace N with a number larger than the one you just set for "shared_buffers" in the postgresql.conf. Now, this setting uses bytes and not megabytes. If you set shared_buffers to 512 MB, a working value for the kernel.shmmax would be 550000000. You can also see the "size=X" mentioned in the error message above, this is the value that you need to exceed. After having done this change you need to run sysctl -p to load the settings.
APerfect10
Site Admin
 
Posts: 4319
Joined: Sat Dec 08, 2007 6:03 pm

Re: Performance Tuning PostgreSQL

Postby APerfect10 » Mon Oct 27, 2008 11:51 pm

If you have a spare hard disk that you want to use purely for postgres - see this post:

PT3 performance tuning 2 HDDs
APerfect10
Site Admin
 
Posts: 4319
Joined: Sat Dec 08, 2007 6:03 pm


Return to FAQs, Tutorials, and How To's [Read Only]

Who is online

Users browsing this forum: No registered users and 2 guests

cron
highfalutin