PostgreSQL TPROC-C Benchmarks: PostgreSQL 12 vs. PostgreSQL 13 Performance

Vik Fearing October 6, 2020

PostgreSQL TPC-C Benchmarks: PostgreSQL 12 vs. PostgreSQL 13 Performance

PostgreSQL 13.0 was just released and included a number of performance improvements in various areas. In order to see what kind of difference these changes made, I ran a number of benchmarks using HammerDB with different numbers of virtual users, comparing PostgreSQL 12.4 and 13.0 using both an un-tuned configuration and a 'starter' tuning configuration, i.e. one that we would suggest that users use as a starting point for their own deployments.

 

Server Specifications

The Server Under Test (SUT) is an Amazon AWS m5.metal instance running Red Hat Enterprise Linux release 8.2 (Ootpa).

vCPUs

96

Architecture

x86_64

Memory

393216 MiB

Network

25 Gigabit

 

The PostgreSQL data directory is on eight disks configured in RAID 10. The filesystem is xfs and readahead is set to 8192.

Size

2000 GiB

Volume Type

io1

IOPS

25000

 

The PostgreSQL WAL directory is on its own disk. The filesystem is xfs and readahead is set to 8192.

Size

500 GiB

Volume Type

io1

IOPS

25000

 

PostgreSQL

The tests were run on both PostgreSQL 12.4 and PostgreSQL 13.0 from the community packages. Each server was tested mostly untuned and tuned.

"Untuned" Settings

lc_messages

'C'

listen_addresses

'*'

log_filename

'postgresql.log'

logging_collector

on

max_connections

1700

unix_socket_directories

'/tmp'

 

All other settings remained at the default values.

Tuned Settings

autovacuum_max_workers

10

autovacuum_vacuum_cost_limit

3000

checkpoint_completion_target

0.9

checkpoint_timeout

'15min'

cpu_tuple_cost

0.03

effective_cache_size

'350GB'

lc_messages

'C'

listen_addresses

'*'

log_filename

'postgresql.log'

logging_collector

on

maintenance_work_mem

'2GB'

max_connections

1700

max_wal_size

'300GB'

pg_stat_statements.save

off

pg_stat_statements.track

all

random_page_cost

1.1

shared_buffers

'100GB'

shared_preload_libraries

'pg_stat_statements'

unix_socket_directories

'/tmp'

wal_buffers

'1GB'

work_mem

'128MB'

 

All other settings remained at the default values.

 

HammerDB

The test driver used is HammerDB with a few modifications to the data model to use appropriate data types for PostgreSQL, optimize column order for alignment, and remove fill factor settings (see the Data Model section at the end of this post for the exact schema used). These modifications are in accordance with the TPC-C specification:

Section 1.3 — Table Layouts

For each table, the following list of attributes can be implemented in any order, using any physical representation available from the tested system.

No changes were made to the algorithms used by HammerDB.

The database is populated by HammerDB with 2000 warehouses. Each table is CLUSTERed by its primary key index (except for history which has no indexes). ANALYZE and VACUUM FREEZE are run on all tables. A base backup of this pristine database is preserved.

 

Method

For each number of virtual users, a new database is restored from the base backup. HammerDB is then run with a ramp up time of 5 minutes and test time of 1 hour. 

Note that think and key times are disabled, as the intention with this test is to see the maximum possible performance. This does not reflect a real-world workload, nor is it appropriate if running a "by the book" TPC-C benchmark..
Note: for a proper TPC-C benchmark, the test must be run for a minimum of two hours.

 

Results

The following table shows the results for 12.4 and 13.0 both out of the box and properly tuned. The values indicate transactions per minute.

Virtual Users

12.4 default

12.4 tuned

13.0 default

13.0 tuned

1

30,799

30,401

31,752

30,886

10

199,685

288,767

213,300

291,719

25

288,152

668,073

309,972

656,619

50

351,020

1,117,707

370,621

1,117,031

75

335,233

1,418,023

346,329

1,381,226

100

315,080

1,627,498

323,613

1,604,327

250

253,208

1,848,943

265,909

2,284,675

500

217,511

1,827,963

224,741

1,924,406

 

We can see from this chart that the untuned performance of 12.4 and 13.0 are mostly identical, with 13.0 having the edge, and performance maxes out between 50 and 75 virtual users.

When properly tuned, we can achieve up to eight times the number of transactions per minute, and sustain that as the number of virtual users increases.

The same can be seen between 13.0 untuned and tuned.

If we compare 12.4 and 13.0 with proper tuning, we can see a near 20% improvement in transactions per minute at 250 virtual users before slowly dropping back down to 12.4 levels.

 

Summary

The tests show that there is immense benefit to properly tuning your PostgreSQL server.  Version 13.0 performs better than 12.4 out of the box, and the difference is even more striking when both servers are tuned.
PostgreSQL 13 represents another leap forward for Open Source relational databases, as it continues to improve with every new release.

 

Appendix A: Data Model


CREATE TABLE customer (
    c_since timestamp with time zone NOT NULL,
    c_id integer NOT NULL,
    c_w_id integer NOT NULL,
    c_d_id smallint NOT NULL,
    c_payment_cnt smallint NOT NULL,
    c_delivery_cnt smallint NOT NULL,
    c_first character varying(16) NOT NULL,
    c_middle character(2) NOT NULL,
    c_last character varying(16) NOT NULL,
    c_street_1 character varying(20) NOT NULL,
    c_street_2 character varying(20) NOT NULL,
    c_city character varying(20) NOT NULL,
    c_state character(2) NOT NULL,
    c_zip character(9) NOT NULL,
    c_phone character(16) NOT NULL,
    c_credit character(2) NOT NULL,
    c_credit_lim numeric(12,2) NOT NULL,
    c_discount numeric(4,4) NOT NULL,
    c_balance numeric(12,2) NOT NULL,
    c_ytd_payment numeric(12,2) NOT NULL,
    c_data character varying(500) NOT NULL,

    CONSTRAINT customer_i1 PRIMARY KEY (c_w_id, c_d_id, c_id)
);
CREATE UNIQUE INDEX customer_i2 ON customer USING btree (c_w_id, c_d_id, c_last, c_first, c_id);

CREATE TABLE district (
    d_w_id integer NOT NULL,
    d_next_o_id integer NOT NULL,
    d_id smallint NOT NULL,
    d_ytd numeric(12,2) NOT NULL,
    d_tax numeric(4,4) NOT NULL,
    d_name character varying(10) NOT NULL,
    d_street_1 character varying(20) NOT NULL,
    d_street_2 character varying(20) NOT NULL,
    d_city character varying(20) NOT NULL,
    d_state character(2) NOT NULL,
    d_zip character(9) NOT NULL,

    CONSTRAINT district_i1 PRIMARY KEY (d_w_id, d_id)
);

CREATE TABLE history (
    h_date timestamp with time zone NOT NULL,
    h_c_id integer,
    h_c_w_id integer NOT NULL,
    h_w_id integer NOT NULL,
    h_c_d_id smallint NOT NULL,
    h_d_id smallint NOT NULL,
    h_amount numeric(6,2) NOT NULL,
    h_data character varying(24) NOT NULL
);

CREATE TABLE item (
    i_id integer NOT NULL,
    i_im_id integer NOT NULL,
    i_name character varying(24) NOT NULL,
    i_price numeric(5,2) NOT NULL,
    i_data character varying(50) NOT NULL,

    CONSTRAINT item_i1 PRIMARY KEY (i_id)
);

CREATE TABLE new_order (
    no_w_id integer NOT NULL,
    no_o_id integer NOT NULL,
    no_d_id smallint NOT NULL,

    CONSTRAINT new_order_i1 PRIMARY KEY (no_w_id, no_d_id, no_o_id)
);

CREATE TABLE order_line (
    ol_delivery_d timestamp with time zone,
    ol_o_id integer NOT NULL,
    ol_w_id integer NOT NULL,
    ol_i_id integer NOT NULL,
    ol_supply_w_id integer NOT NULL,
    ol_d_id smallint NOT NULL,
    ol_number smallint NOT NULL,
    ol_quantity smallint NOT NULL,
    ol_amount numeric(6,2),
    ol_dist_info character(24),

    CONSTRAINT order_line_i1 PRIMARY KEY (ol_w_id, ol_d_id, ol_o_id, ol_number)
);

CREATE TABLE orders (
    o_entry_d timestamp with time zone NOT NULL,
    o_id integer NOT NULL,
    o_w_id integer NOT NULL,
    o_c_id integer NOT NULL,
    o_d_id smallint NOT NULL,
    o_carrier_id smallint,
    o_ol_cnt smallint NOT NULL,
    o_all_local smallint NOT NULL,

    CONSTRAINT orders_i1 PRIMARY KEY (o_w_id, o_d_id, o_id)
);
CREATE UNIQUE INDEX orders_i2 ON orders USING btree (o_w_id, o_d_id, o_c_id, o_id);

CREATE TABLE stock (
    s_i_id integer NOT NULL,
    s_w_id integer NOT NULL,
    s_ytd integer NOT NULL,
    s_quantity smallint NOT NULL,
    s_order_cnt smallint NOT NULL,
    s_remote_cnt smallint NOT NULL,
    s_dist_01 character(24) NOT NULL,
    s_dist_02 character(24) NOT NULL,
    s_dist_03 character(24) NOT NULL,
    s_dist_04 character(24) NOT NULL,
    s_dist_05 character(24) NOT NULL,
    s_dist_06 character(24) NOT NULL,
    s_dist_07 character(24) NOT NULL,
    s_dist_08 character(24) NOT NULL,
    s_dist_09 character(24) NOT NULL,
    s_dist_10 character(24) NOT NULL,
    s_data character varying(50) NOT NULL,

    CONSTRAINT stock_i1 PRIMARY KEY (s_w_id, s_i_id)
);

CREATE TABLE warehouse (
    w_id integer NOT NULL,
    w_name character varying(10) NOT NULL,
    w_street_1 character varying(20) NOT NULL,
    w_street_2 character varying(20) NOT NULL,
    w_city character varying(20) NOT NULL,
    w_state character(2) NOT NULL,
    w_zip character(9) NOT NULL,
    w_tax numeric(4,4) NOT NULL,
    w_ytd numeric(12,2) NOT NULL,

    CONSTRAINT warehouse_i1 PRIMARY KEY (w_id)
);

Vik FearingPostgreSQL Expert

Vik Fearing is a PostgreSQL expert at EnterpriseDB, and lives in France. He has been in the PostgreSQL community since 2008 and is the founder and co-organizer of pgDay Paris, co-organizer of PostgreSQL Conference Europe, and a volunteer and speaker at many other conferences around the world. He is also a moderator for several of the PostgreSQL mailing lists as well as an IRC channel operator for #postgresql and #postgresqlfr. He is an inaugural member of the PostgreSQL Code of Conduct Committee and part of the team behind the @PostgreSQL Twitter account. In his spare time, he likes to write minor patches to the PostgreSQL codebase.