Pgbench is a simple program to run benchmark on PostgreSQL. It may run the same sequence of SQL commands over and over in a concurrent database session and calculate the average transaction rate (transactions per second). By default, pgbench tests a tpc-b-based but more relaxed scenario involving five select, update, and insert commands per transaction. However, it is easy to test other situations by writing your own transaction script files.
pgbench [OPTION]... [DBNAME]
-i. -- initialize calls the initialization mode -F. -- fillfactor = num set fill factor -n. -- no vacuum does not run after initialization -q. -- quiet mode -s. -- scale = num scale factor
-c. -- client = number of concurrent num database clients (default: 1) -C. -- connect creates a new connection for each transaction -D. -- define = value = value define the variables used by the custom script -f. -- file = filename read transaction script from file name -j. -- jobs = num threads (1 by default) -l. -- log write log file -50. -- latency limit = num counts transactions with a duration of more than milliseconds (MS) as latency -M. -- Protocol = simple|extended|prepared protocol for submitting query (default: simple) -n. -- do not run vacuum before no-vacuum test -N. -- skip some updates skip updates of pgbench ﹣ tellers and pgbench ﹣ branches -P. -- progress = num displays thread progress report every specified second -r. -- report latencies reports the average latency per command -R. -- rate = target rate of num transactions per second -s. -- scale = num reports this scale factor in the output -S. -- select only to execute read-only query -t. -- transactions = number of transactions run by each client of num (default is 10) -T. -- time = num benchmark duration (seconds) -v. -- all 4 standard forms of vacuum before vacuum all test
Huawei 2488h V5
4 * 16 core Intel Xeon 6130 2.10GHz
2*480GB SSD 、22*1.2T SAS
1T(33*32 GB) 2666MHz DDR4
Two dual port Gigabit Ethernet cards and two dual port 10GbE Ethernet cards
Red Hat Enterprise Linux Server release 7.5
Cluster parameter setting
gpconfig -c 'optimizer' -v off gpconfig -c 'gp_enable_global_deadlock_detector' -v on gpconfig -c 'resource_scheduler' -v off gpconfig -c log_statement -v none gpconfig -c checkpoint_segments -v 2 --skipvalidation
gpconfig -c 'optimizer' -v off gpconfig -c 'resource_scheduler' -v off gpconfig -c log_statement -v none gpconfig -c checkpoint_segments -v 2 --skipvalidation
This GUC is used to control whether to turn on the global deadlock detection function. It is turned off by default in Greenplum 6 and needs to be turned on to support concurrent update / delete operations. Greenplum 5 does not support this GUC.
This GUC reduces unnecessary logs and avoids the interference of log output on I / O performance.
This GUC affects the frequency of checkpoint active disk brushing. The default value of 8 will reduce the frequency of disk brushing, but the amount of data per disk brushing is large, resulting in instantaneous performance degradation of the whole cluster. Turning down this setting appropriately for a large number of OLTP update class statements will increase the frequency of disk swiping, but the average performance will be significantly improved because the data volume of each disk swiping becomes smaller; Greenplum 5 supports this GUC but has no obvious effect, because the performance bottleneck of Greenplum 5 is not I / O, but serialization caused by table locking.
The test database is built with pgbench, and the data scale is 1000 times, lasting for 60 seconds.
- Initialization command
pgbench -h mas01 -U gpadmin6 -p 6666 -i -s 1000 test
- Benchmark command
pgbench -h mas01 -U $user -p $port -c $N -T 60 -r test
- Single query test command
pgbench -h mas01 -U $user -p $port -c $N -S -T 60 -r test
- Single update test command
pgbench -h mas01 -U $user -p $port -c $N -S -T 60 -r test -f update.sql # vi update.sql \set naccounts 100000 * :scale \setrandom aid 1 :naccounts \setrandom delta -5000 5000 BEGIN; UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; END;
- Single insert test command
pgbench -h mas01 -U $user -p $port -c $N -S -T 60 -r test -f insert.sql # vi insert.sql \set nbranches 1 * :scale \set ntellers 10 * :scale \set naccounts 100000 * :scale \setrandom aid 1 :naccounts \setrandom bid 1 :nbranches \setrandom tid 1 :ntellers \setrandom delta -5000 5000 BEGIN; INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); END;
During the execution process, it was observed that Greenplum 5 has a large number of locks when it involves updates. When there is a single query and a single insert, because of the two transactions, there is no timely commit, resulting in the conflict of lwlock (system shared resource lock). Greenplum 6. The global deadlock detection function is introduced to support the concurrent update of the data table of the heap table, which solves this problem. During the concurrent update test, lwlocks on the master are not available, and lwlocks on the nodes are few but few in total, so the performance is greatly improved.
What would happen if it was an AO table? Next, modify the table pgbench [accounts] to Ao table, and then test:
test=# create table pgbench_accounts_ao(like pgbench_accounts)WITH (appendonly=true,compresstype=zlib,COMPRESSLEVEL=5); test=# insert into pgbench_accounts_ao select * from pgbench_accounts; test=# alter table pgbench_accounts rename to pgbench_accounts_bak; test=# alter table pgbench_accounts_ao rename to pgbench_accounts; test=# vacuum analyze pgbench_accounts;
As expected, there was a problem with the update of Ao table. It was observed that only one process was running and others were waiting for locks.
Insert and concurrent test the Ao table:
create table pgbench_history_ao(like pgbench_history)WITH (appendonly=true,compresstype=zlib,COMPRESSLEVEL=5); insert into pgbench_history_ao select * from pgbench_history; alter table pgbench_history rename to pgbench_history_bak; alter table pgbench_history_ao rename to pgbench_history; vacuum analyze pgbench_history;
Performance degradation is obvious, Ao table is not suitable for frequent insertion.
Query and concurrent test the Ao table:
The TPS is also very low. It seems that the OLTP promotion of GP6 only works on the heap table.
Then we optimize the parameters of Greenplum 6, take tpc-b benchmark as an example, and adjust the CPU core number 64 as the concurrent number
1. The comparison between Greenplum 6 and 5 does not start the thread. It is to reduce the influence of the pgbench thread parameter on Greenplum 5. Now adjust the pgbench command to start the thread – J parameter with a value of 16.
pgbench -h mas01 -U gpadmin6 -p 6666 -c 64 -j 16 -T 30 -r test
2. Adjust the shared memory parameter “shared” buffers to store the shared data in memory.
gpconfig -c shared_buffers -v '2GB'
3. Adjusting the transaction commit parameters does not force the wal to be written to the disk, only writing to the cache will return the commit success to the client, delaying the wal_writer_delay * 3 ms to write to the disk, which can improve the TPS but has the risk of transaction loss.
gpconfig -c synchronous_commit -v off
4. Turn off the persistent call and do not force the data to refresh to the disk. There is a risk of data loss in case of power failure or system problems.
gpconfig -c fsync -v 'off' –skipvalidation
Based on the previous comparison test command, add the – J $n parameter to start the thread, and test the performance result of Greenplum 6 under the current parameter setting
RT (average response time), in milliseconds
Screenshot of benchmark test results
Based on the previous test command, add the – J $n parameter to start the thread, and test the result under the current parameter setting
Screenshot of benchmark test results
Screenshot of single query test results
Screenshot of single update test results
Screenshot of single insertion test results
About the author
Ye Jianfeng, MPP database R & D management
Coordinate in Guangzhou, started to learn to use Greenplum in 2012, and has been proficient in Database Planning and deployment, SQL development and optimization, ETL data loading, database operation and maintenance, performance optimization, etc.