PostgreSQL Configuration Optimization

Hardware and System Configuration

Operating system	Ubuntu13.04
Number of system bits	64
Cpu	Intel (R) Core (TM) 2 Duo CPU
Memory	4G
Hard disk	Seagate st2000dm001-1ch164
Test tools	PostgreSQL-9.1.11

Test tools

Tool Name	Pgbench
Data volume	200W (the entire database size is approximately 300M)
Number of analog clients	4
Number of threads	4
Test time	60 seconds

• Preparation command: Pgbench-i-S PGBENCHDB

• Test command: Pgbench-r-j4-c4-t60 TestDB

Configuration file

The default configuration profile is the postgresql.conf file that is saved in the/etc/postgresql/version/main directory

• If you want to see if the parameter modification takes effect, you can use Psql to connect to the database and use the <show option name > to view it.

• If you want to modify shared_buffers, you may need to execute commands under Ubuntu <sysctl-w>managing Kernel Resources

Main options

Options	Default value	Description	Whether to optimize	Reason
Max_connections	100	Maximum number of client connections allowed	Whether	Because in the course of testing, 100 connections are sufficient
Fsync	On	Forcing data to be updated synchronously to disk	Is	Because the IO pressure of the system is very large, in order to better test the influence of other configuration, change the parameter to OFF
Shared_buffers	24MB	Determines how much memory can be used by PostgreSQL for caching data (1/4 of recommended memory)	Is	In the case of large IO pressures, increasing this value can reduce the IO
Work_mem	1MB	make internal sorting and some complex queries complete in this buffer	Is	Helps improve the speed of sequencing and other operations, and reduces IO
Effective_cache_size	128MB	The optimizer assumes the maximum memory that a query can use, regardless of shared_buffers (1/2 of recommended memory)	Is	setting is slightly larger, the optimizer prefers to use index scans instead of sequential scans
Maintenance_work_mem	16MB	The memory defined here is only used when a more resource-intensive command is called by vacuum.	Is	Increase the value to speed up the execution of the command
Wal_buffer	768kB	Size of log buffers	Is	Can reduce IO, if you encounter more concurrent short transactions, should be used with Commit_delay
Checkpoint_segments	3	Set the maximum number of Wal logs (a log size of 16M)	Is	The default 48M cache is a serious bottleneck, basically set to 10 or more
Checkpoint_completion_target	0.5	Indicates that the checkpoint completion time is within the n% of two checkpoint intervals	Is	Can reduce the cost of average writes
Commit_delay	0	After the transaction is committed, the log is written to the Wal log on the time interval to wal_buffer written to disk. Need to cooperate with commit_sibling	Is	Ability to write multiple transactions at once, reduce IO, improve performance
Commit_siblings	5	Sets the number of concurrent transactions that trigger Commit_delay, CONFIGURED according to how many concurrent transactions	Is	Reduce IO, improve performance

Test data

• The test data is run 3 times, averaging.

• The Fsync is turned off to better reflect the impact of other parameters on PostgreSQL.

Parameters	modifying values	Total transactions	TPS (including establishing connections)	TPS (does not include establishing a connection)
Default settings		8464	140.999792	141.016182
Fsync	Off	92571	1479.969755	1480.163355
Shared_buffers	1GB	100055	1635.759275	1635.977823
Work_mem	10MB	101209	1665.804812	1666.04082
Effective_cache_size	2GB	98209	1636.733152	1636.970271
Maintenance_work_mem	512MB	92930	1548.029233	1548.223108
Checkpoint_segments	32	195982	3265.995	3266.471064
Checkpoint_completion_target	0.9	194390	3239.406493	3239.842596
Wal_buffer	8MB	198639	3310.241458	3310.724067
Recovery Fsync	Off	11157	185.883542	185.909849
Commit_delay && commit_siblings	Ten && 4	11229	187.103538	187.131747

Summarize

	Total transactions	TPS (including establishing connections)	TPS (does not include establishing a connection)
Before optimization	8464	140.999792	141.016182
After optimization (fsync=on)	11229	187.103538	187.131747
After optimization (Fsync=off)	198639	3310.241458	3310.724067

When the Fsync is turned on, the optimized performance can be increased by about 30%. Because some of the optimization options do not reflect its advantage in the default SQL Test statement, if you go to the actual test, the promotion should be more than 30%. In the process of testing, the main bottleneck is in the system IO, if need to reduce the load of Io, the most direct way is to turn off the Fsync, but this will be in the case of power loss, may lose some of the data.

PostgreSQL Configuration Optimization