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]

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

Parameterwork_mem
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

Parametermaintenance_work_mem
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). 
Unitskb
Recommended[Available Memory] / 8

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

Parametercheckpoint_segments
DescriptionMaximum distance between automatic WAL checkpoints, in log file segments (each segment is normally 16 megabytes). 
Units
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.

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

CPU Operation Costs:
cpu_tuple_cost0.0030
cpu_index_tuple_cost0.0010
cpu_operator_cost0.0005



Stumble Upon Toolbar