Princever.github.io

Sharing and enjoying my life!

Report of Parallel Sequence Scan

Abstract

It is a report about a Test of Parallel Seq Scan on PostgreSQL. We tested the performance when number of worker threads is 0, 1, 2, 4, 6, 8, 12, 16, 20, 24. Then we visualize the result with perf and flame graph.

Test design

The design of test is simple. We run the test on a 12-core server and record the running time and workload.

Hardware spec and configuration

OS					CentOS6.6-64
RAM					8x8GB Micron 8GB DDR4 1Rx4
Processor			2.4GHz Intel Xeon-Haswell (E5-2620-V3-HexCore)
Processor			2.4GHz Intel Xeon-Haswell (E5-2620-V3-HexCore)
Motherboard			SuperMicro X10DRU-i+
Remote Mgmt 		Card	Aspeed AST2400 - Onboard
Network Card		SuperMicro AOC-UR-i4XT
Backplane			SuperMicro BPN-SAS-815TQ
Power Supply		SuperMicro PWS-751P-1R
Driver controller	Mainboard Onboard
Security Device		SuperMicro AOM-TPM-9655V

Software version, modification and configuration

PostgreSQL			version 9.6devel
perf				version 2.6.32-573.18.1.el6.x86_64.debug

Schema design, data size and tools

We tested parallel seq scan with pgbench. With the scale factor of 2000 and 5000 whose cooresponding data size is 3200k lines and 8000k lines.
Setting
1
max_parallel_degree
to 0, 1, 2, 4, 6, 8, 12, 16, 20, 24, we ran the SELECT query and collocted the running time and detials of each workers.
Using perf to record the function calls of the process.
Draw the results in flame graph.
For more detials of the test and flame gpaph, click here.

Test queries

We use

1	EXPLAIN (ANALYZE true, VERBOSE true, BUFFERS true) select * from pgbench_accounts where filler = 'foo';

as the query.

Results

Data = 3200k lines

number of workers	running time(s)	efficiency
no-para	117,253	1.00
1	59,796	1.96
2	39,986	2.93
4	24,152	4.85
6	17,274	6.79
8	13,470	8.70
12	9,858	11.89
16	8,868	13.22
20	8,060	14.55
24	7,848	14.94

Data = 8000k lines

number of workers	running time(s)	efficiency
1	221,332	1.00
2	183,937	1.20
4	197,698	1.12
6	165,121	1.34
8	197,104	1.12

Conclusion and Future work

From the graph we can see, the performance of parallel seq scan is clear linear when the number of workers below 12(Because it is a 12-core server). So as we can imagine, as the number of cores of the server increase, the performance will verge on a linear function.

Thanks for viewing! Don’t forget following me on GitHub!

Written on February 29, 2016

煌めくどんな星も、君を照らすから - Princever.GitHub.io

Princever's blog