Tpc-b test: how much better is Greenplum 6 than 5?

Time:2020-3-24

About pgbench:

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.

Usage method

pgbench [OPTION]... [DBNAME]

Initialization options

-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

Standard options

-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

hardware environment

Number

3 servers

Model

Huawei 2488h V5

CPU

4 * 16 core Intel Xeon 6130 2.10GHz

storage

2*480GB SSD 、22*1.2T SAS

Memory

1T(33*32 GB)  2666MHz DDR4

Mesh port

Two dual port Gigabit Ethernet cards and two dual port 10GbE Ethernet cards

Linux distribution

Red Hat Enterprise Linux Server release 7.5

Linux kernel

3.10.0-862.el7.x86_64

Environmental configuration

GP version

Greenplum6.2.1

Greenplum5.20

Kernel version

PostgreSQL 9.4.24

PostgreSQL 8.3.23

Environmental configuration

Master:mas01 Segment:mas01、mas02、seg08

Cluster parameter setting

Greenplum6

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

Greenplum5

gpconfig -c 'optimizer' -v off
gpconfig -c 'resource_scheduler' -v off
gpconfig -c log_statement -v none
gpconfig -c checkpoint_segments -v 2 --skipvalidation

Parameter description

gp_enable_global_deadlock_detector

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.

log_statement

This GUC reduces unnecessary logs and avoids the interference of log output on I / O performance.

checkpoint_segments

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.

test method

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

Tpc-b test: how much better is Greenplum 6 than 5?

  • 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;

Tpc-b test: how much better is Greenplum 6 than 5?

summary

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;

Tpc-b test: how much better is Greenplum 6 than 5?

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;

Tpc-b test: how much better is Greenplum 6 than 5?

Performance degradation is obvious, Ao table is not suitable for frequent insertion.

Query and concurrent test the Ao table:

Tpc-b test: how much better is Greenplum 6 than 5?

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.

Test command

pgbench -h mas01 -U gpadmin6 -p 6666 -c 64 -j 16 -T 30 -r test

test result

Tpc-b test: how much better is Greenplum 6 than 5?

2. Adjust the shared memory parameter “shared” buffers to store the shared data in memory.

gpconfig -c shared_buffers -v '2GB'

test result

Tpc-b test: how much better is Greenplum 6 than 5?

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

test result

Tpc-b test: how much better is Greenplum 6 than 5?

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

test result

Tpc-b test: how much better is Greenplum 6 than 5?

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

Tpc-b test: how much better is Greenplum 6 than 5?

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

Tpc-b test: how much better is Greenplum 6 than 5?

Tpc-b test: how much better is Greenplum 6 than 5?

Tpc-b test: how much better is Greenplum 6 than 5?

Tpc-b test: how much better is Greenplum 6 than 5?

Screenshot of single query test results

Tpc-b test: how much better is Greenplum 6 than 5?

Tpc-b test: how much better is Greenplum 6 than 5?

Tpc-b test: how much better is Greenplum 6 than 5?

Tpc-b test: how much better is Greenplum 6 than 5?

Tpc-b test: how much better is Greenplum 6 than 5?

Screenshot of single update test results

Tpc-b test: how much better is Greenplum 6 than 5?

Tpc-b test: how much better is Greenplum 6 than 5?

Tpc-b test: how much better is Greenplum 6 than 5?

Tpc-b test: how much better is Greenplum 6 than 5?

Tpc-b test: how much better is Greenplum 6 than 5?

Screenshot of single insertion test results

Tpc-b test: how much better is Greenplum 6 than 5?

Tpc-b test: how much better is Greenplum 6 than 5?

Tpc-b test: how much better is Greenplum 6 than 5?

Tpc-b test: how much better is Greenplum 6 than 5?

Tpc-b test: how much better is Greenplum 6 than 5?

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.

Tpc-b test: how much better is Greenplum 6 than 5?