Aug 4, 2009

Optimizing PostgreSQL for Production ...

Optimizing PostgreSQL for Production Use.

This is a reference list of things that need to be changed to PostgreSql Server prior to production use. This material is a collection from other resources but outlined in a while to simplify configuration settings for regular database administrators like me.

STEP 1 [Configurations]: Go to PostgreSql config at: file /etc/postgresql/8.*/main/postgresql.conf to change the settings.

STEP 2 [Available Memory]: Know how much memory you need to allocate for your database. If your server is solely dedicated for database use, then your physical memory shall be your available memory. If it is shared with other services such as Apache2 Web Server then, leave an ample memory for others. Generally, allocate a very liberal amount of memory for PostgreSql since databases are naturally resource hogs. My rule of thumb for this is: 

75% of Physical Memory (RAM) = 'Available Memory'
Example: .75 * 2G RAM = [1.5 G RAM]

STEP 3 [Change th config file]

DescriptionSets the number of shared memory buffers used by the database server. Settings significantly higher than the minimum are usually needed for good performance. 
Recommended.25 * [Available Memory]
Special NoteYou need to change the kernel parameter [SHMMAX] to twice the amount of memory allocated for shared_buffers.

DescriptionSpecifies the amount of memory to be used by internal sort operations and hash tables before switching to temporary disk files.
Unitskb     Default: 1024kb
Recommended[Available Memory/max_connections] / 2

DescriptionSpecifies the maximum amount of memory to be used in maintenance operations, such as VACUUMCREATE INDEX, and ALTER TABLE ADD FOREIGN KEY. The value is specified in kilobytes, and defaults to 16384 kilobytes (16 MB). 
Recommended[Available Memory] / 8

DescriptionNumber of disk-page buffers allocated in shared memory for Write-Ahead Log (WAL) data.

DescriptionMaximum distance between automatic WAL checkpoints, in log file segments (each segment is normally 16 megabytes). 
Recommended(([Disk Space Allocation for WAL] / 8) -1 ) / 2
Special Notemust be between 16 to 128 depending on how much disk space are you willing to allocate for WAL.

DescriptionSets the planner's assumption about the effective size of the disk cache that is available to a single index scan.
Recommended[Available Memory] * .75

CPU Operation Costs:

Stumble Upon Toolbar

No comments: