PostgreSQL parameter optimization comparison performance test

1, Testing purpose

By running the standard test program TPC-B and TPC-C, determine the performance differences between different parameters and different versions (8.2.14 vs 8.4.2, it provides reference for parameter configuration and version selection of PostgreSQL databases on ECOP cm.

The test platform is:

Hardware configuration: CPU e4600 dual-core 2.4g 2g RAM 160g SATA * 2

Operating System: Ubuntu 9.10 Server

File System: ext3

Databases: PostgreSQL 8.2.14 and 8.4.2

Install using source code compilation, GCC version 4.4.1

2, Testing and data

2.1 TPC-B(Pgbench)

2.1.1 Test Method

Test parameter:./pgbench-I-s 20 pgbench. Set the load factor to 20.

 

Initialize database table capacity:

Able # of rows

---------

Branches 20

Tellers 200

Accounting 2000000

History 0

Physical database size: the total size of the database is 288 MB, of which the table accounts is 248 MB.

SQL statement executed for each transaction:

1. begin;

2. Update accounts set abalance = abalance +: Delta where aid =: aid;

3. Select abalance from accounts where aid =: aid;

4. Update tellers set tbalance = tbalance +: Delta where tid =: tid;

5. Update branches set bBalance = bBalance +: Delta where Bid =: bid;

6. insert into history (TID, bid, aid, Delta, mtime) values (: tid,: bid,: Aid,: Delta, current_timestamp );

7. end;

Test method:./pgbench-T 2000-C 20-u Postgres pgbench simulates 20 concurrent users, and each user executes 2000 transactions. Each configuration parameter is executed three times and the TPS value is recorded.

2.1.2 Test result (8.2.14)

The pgbench test result of postgresql 8.2.14 (Unit TPS/number of transactions per second) focuses on the parameters shared_buffers, inclutive_cache_size, wal_buffers, and checkpoint_segments.

Serial number	Parameter configuration	First time	Second	Third time	Average Value
1	Default parameters	404	408	415	409
2	Adjust Wal Log Parameters Wal_buffers = 1024kb (default: 64kb) Checkpoint_segments = 32 (default: 3)	852	895	813	853
3	Place Wal logs on the second Hard Disk	1673	1673	1657	1667
4	Adjust Memory Parameters Shared_buffers = 256 MB (32 MB by default) Work_mem = 10 MB (1 MB by default) Required tive_cache_size = 512 MB (128 MB by default)	2351	2375	2390	2372
5	Adjust Memory Parameters Shared_buffers = 512 MB Work_mem = 10 MB Required tive_cache_size = 1024 MB	2264	2354	2351	2323
6	Disable Wal logs	2761	2806	2810	2792

2.1.3 Test result (8.4.2)

PostgreSQL 8.4.2 pgbench test results (Unit TPS/number of transactions per second). The key parameters are shared_buffers, inclutive_cache_size, wal_buffers, and checkpoint_segments.

Serial number	Parameter configuration	First time	Second	Third time	Average Value
1	Default parameters	466	470	450	462
2	Adjust Wal Log Parameters Wal_buffers = 1024kb (default: 64kb) Checkpoint_segments = 32 (default: 3) Checkpoint_completion_target = 0.9 (0.5 by default)	939	940	1064	981
3 *	Place Wal logs on the second Hard Disk	1364	1945	1378	1562
4	Adjust Memory Parameters Shared_buffers = 256 MB (32 MB by default) Work_mem = 10 MB (1 MB by default) Required tive_cache_size = 512 MB (128 MB by default)	2316	2341	2341	2332
5	Adjust Memory Parameters Shared_buffers = 512 MB Work_mem = 10 MB Required tive_cache_size = 1024 MB	2319	2361	2370	2350
6	Disable Wal logs	2800	2813	2814	2809

Note * When testing 3 is executed, the data changes greatly. This is also true for multiple executions. The minimum value is 1214, and the maximum value is 2010, which is unstable.

2.1.4 Test performance data comparison

2.1.5 Test Data Analysis

1) The default parameter configuration results in poor performance, whether 8.2.14 or 8.4.2. For details, see Test 1.

2) Increasing the checkpoint_segments value in the wal log parameter can significantly improve the performance and increase the performance by more than doubled. The actual effect of increasing checkpoint_segments is that the database writes logs faster. For details, see Test 2.

3) after placing Wal logs on the second hard disk, the I/0 competition is reduced, and the performance is improved significantly, which is about doubled. For details, see Test 3.

4) The Memory parameter is added so that the content of the entire database table can be basically stored in the memory. The performance is improved significantly by over 50%. For details, see Test 4.

5) when the memory can be loaded into the entire database table, increasing the memory parameter does not affect the performance improvement. See Test 5.

6) When prewrite logs are disabled, the performance can be improved by about 20%. For details, see Test 6.

7) 8.4.2 has a performance improvement over 8.2.14, up to 15%

2.2 TPC-C(Benchmark factory)

2.2.1 Test Method

Use benchmark factory to connect to the database with ODBC, initialize load factor 2, and initialize table capacity

Simulate 5, 10, 15, and 20 concurrent connections respectively.

Parameter configuration:

Shared_buffers = 512 MB

Work_mem = 10 MB

Required tive_cache_size = 1024 MB

Fsync = ON/OFF open/close wal

2.2.2 Test result (8.2.14)

PostgreSQL 8.2.14 TPC-C test results (disable Wal)

Concurrent threads	TPS(Transaction volume per second)	Average response time (MS)
5	79.24	63
10	110.92	90
15	116.43	128
20	119.40	167

2.2.3 Test result (8.4.2)

PostgreSQL 8.4.2 TPC-C test results (disable Wal)

Concurrent threads	TPS(Transaction volume per second)	Average response time (MS)
5	70.65	70
10	107.42	93
15	144.66	103
20	150.35	132

PostgreSQL 8.4.2 results of TPC-C testing (enabling Wal)

Concurrent threads	TPS(Transaction volume per second)	Average response time (MS)
5	62.98	79
10	116.46	85
15	125.31	119
20	125.74	158

2.2.4 Test performance data comparison

2.2.5 Test Data Analysis

1) when the number of concurrent threads is 5 or 10 at a low load, the performance difference between 8.2.14 and 8.4.2 may not be significant, or even better,

2) as the load increases, the performance of 8.2.14 is significantly different from that of 8.4.2 (119.40 vs 150.35 when the number of concurrent threads is 20), and the performance of 8.4.2 is 25.9% higher.

3Test Summary

The key impact of database performance is disk I/0 performance.

ECOP default is 8.2.x version, the current database table capacity in dozens of MB to within MB, according to different models and user volume, In the CM1-4 model can be configured with the following parameters:

Shared_buffers = 256 MB

Work_mem = 10 MB

Required tive_cache_size = 512 MB

Wal_buffers = 1024kb

Checkpoint_segments = 32

The cm5 model can be configured as follows:

Shared_buffers = 512 MB

Work_mem = 10 MB

Required tive_cache_size = 1024 MB

Wal_buffers = 1024kb

Checkpoint_segments = 32

Place Wal logs on an independent hard disk.

When appropriate, you can consider upgrading the database to 8.4.2. Currently, the compatibility problem found is better solved. Only the query time in the query column needs to be modified, compatibility can be maintained elsewhere.