Open Source SQL databases enters millions queries per second era - - PowerPoint PPT Presentation

▶

May 19, 2023 240 likes •619 views

Open Source SQL databases enters millions queries per second era Alexander Korotkov, Sveta Smirnova Postgres Professional, Percona 2016 Alexander Korotkov, Sveta Smirnova Open Source SQL databases enters millions queries per second era 1 / 33

SLIDE 1

Open Source SQL databases enters millions queries per second era

Alexander Korotkov, Sveta Smirnova

Postgres Professional, Percona

2016

Alexander Korotkov, Sveta Smirnova Open Source SQL databases enters millions queries per second era 1 / 33

SLIDE 2

Russian developers of PostgreSQL:

Alexander Korotkov, Teodor Sigaev, Oleg Bartunov

▶ Speakers at PGCon, PGConf: 20+ talks ▶ GSoC mentors ▶ 3 PostgreSQL major contributors + 1 committer ▶ Conference organizers ▶ 50+ years of PostgreSQL expertship: dev., audit, consult. ▶ Postgres Professional company co-founders

PostgreSQL CORE

▶ Locale support ▶ PostgreSQL extendability:

GiST(KNN), GIN, SP-GiST

▶ Full Text Search (FTS) ▶ NoSQL (hstore, jsonb) ▶ Indexed regexp search ▶ Access method extendability

Extensions

▶ intarray ▶ pg_trgm ▶ ltree ▶ hstore ▶ plantuner ▶ jsquery

Alexander Korotkov, Sveta Smirnova Open Source SQL databases enters millions queries per second era 2 / 33

SLIDE 3

Sveta Smirnova

▶ MySQL Support engineer for more than 10 years ▶ Author of book MySQL Troubleshooting ▶ JSON UDF functions: design prototype for built-in JSON

support

▶ Pluggable FILTER clause for MySQL ▶ Speaker at Percona Live, OOW, Fosdem, DevConf, ... ▶ http://www.slideshare.net/SvetaSmirnova ▶ https://twitter.com/svetsmirnova ▶ https://github.com/svetasmirnova

Alexander Korotkov, Sveta Smirnova Open Source SQL databases enters millions queries per second era 3 / 33

SLIDE 4

Scalability benchmark

What do we have?

▶ Outstanding scalability improvements in PostgreSQL 9.6 and MySQL 5.7. ▶ Nice benchmarks for MySQL 5.7 made by Dimitri Kravtchuk.

▶ https://goo.gl/aw0sM6 ▶ https://goo.gl/xc8cp8 ▶ https://goo.gl/7dwkoY

▶ Some benchmarks for PostgreSQL 9.6

▶ https://goo.gl/RNWYxb ▶ https://goo.gl/3WrOAH

▶ Access to 72-cores server for testing.

We want to run

▶ same tests ▶ on the same machine ▶ using the same tool

for both MySQL 5.7 and PostgreSQL 9.6.

Alexander Korotkov, Sveta Smirnova Open Source SQL databases enters millions queries per second era 4 / 33

SLIDE 5

Try #1: synchronizing tests

▶ For PostgreSQL standard is pgbench ▶ For MySQL SysBench is widely used

▶ It is scriptable ▶ Easy to communicate with MySQL developers via bugs database, email and so on

▶ SysBench has built-in PostgreSQL support. ▶ I converted pgbench tests into Lua ▶ (open-database-bench) ▶ In PostgreSQL world, it’s standard to run small SQL-queries as prepared

statements.

Alexander Korotkov, Sveta Smirnova Open Source SQL databases enters millions queries per second era 5 / 33

SLIDE 6

sysbench with prepared statements: try 1

▶ Problem: NULL handling is broken in sysbench for PostgreSQL. FATAL: failed to execute function `event': 3 (last message repeated 7 times) FATAL: PQexecPrepared() failed: 7 ERROR: invalid input syntax for integer: "" ▶ Fix. Pull request was merged by Alexey Kopytov. /* Convert SysBench bind structures to PgSQL data */ for (i = 0; i < (unsigned)pgstmt->nparams; i++) {

if (stmt->bound_param[i].is_null)

+ if (stmt->bound_param[i].is_null && *(stmt->bound_param[i].is_null)) continue; switch (stmt->bound_param[i].type) {

Alexander Korotkov, Sveta Smirnova Open Source SQL databases enters millions queries per second era 6 / 33

SLIDE 7

sysbench with prepared statements: try 2

▶ Problem 2: sysbench can’t load PostgreSQL when using

prepared statements.

93087 korotkov 20 0 9289440 3,718g 2964 S 242,6 0,1 0:32.82 sysbench 93161 korotkov 20 0 32,904g 81612 80208 S 4,0 0,0 0:00.47 postgres 93116 korotkov 20 0 32,904g 80828 79424 S 3,6 0,0 0:00.46 postgres 93118 korotkov 20 0 32,904g 80424 79020 S 3,6 0,0 0:00.47 postgres 93121 korotkov 20 0 32,904g 80720 79312 S 3,6 0,0 0:00.47 postgres 93128 korotkov 20 0 32,904g 77936 76536 S 3,6 0,0 0:00.46 postgres 93130 korotkov 20 0 32,904g 81604 80204 S 3,6 0,0 0:00.47 postgres 93146 korotkov 20 0 32,904g 81112 79704 S 3,6 0,0 0:00.46 postgres .............................................................................. ▶ ...give up with sysbench, let’s use pgbench!

Alexander Korotkov, Sveta Smirnova Open Source SQL databases enters millions queries per second era 7 / 33

SLIDE 8

pgbench OLTP read-only script

\set table_size 10000000 \set range_size 100 \set id1 random(1, :table_size) ............................................................... \set id10 random(1, :table_size) \set r1l random(1, :table_size) \set r1u :r1l + :range_size ............................................................... \set r4l random(1, :table_size) \set r4u :r4l + :range_size SELECT c FROM sbtest WHERE id = :id1; ............................................................... SELECT c FROM sbtest WHERE id = :id10; SELECT c FROM sbtest WHERE id BETWEEN :r1l AND :r1u; SELECT SUM(K) FROM sbtest WHERE id BETWEEN :r2l AND :r2u; SELECT c FROM sbtest WHERE id BETWEEN :r3l AND :r3u ORDER BY c; SELECT DISTINCT c FROM sbtest WHERE id BETWEEN :r4l AND :r4u;

Alexander Korotkov, Sveta Smirnova Open Source SQL databases enters millions queries per second era 8 / 33

SLIDE 9

pgbench OLTP read-write script

\set table_size 10000000 ............................................................... \set u1 random(1, :table_size) \set u2 random(1, :table_size) \set u3 random(1, :table_size) \set u4 random(1, :table_size) BEGIN; SELECT c FROM sbtest WHERE id = :id1; ............................................................... SELECT DISTINCT c FROM sbtest WHERE id BETWEEN :r4l AND :r4u; UPDATE sbtest SET k = k + 1 WHERE id = :u1; UPDATE sbtest SET c = sb_rand_str('###########-###########-###########-###########-###########-###########-###########-###########-###########-###########') WHERE id = :u2; DELETE FROM sbtest WHERE id = :u3; INSERT INTO sbtest (id, k, c, pad) VALUES (:u3, :u4, sb_rand_str('###########-###########-###########-###########-###########-###########-###########-###########-###########-###########'), sb_rand_str('###########-###########-###########-###########-###########')) ON CONFLICT DO NOTHING; COMMIT;

I’ve to implement sb_rand_str() in server side C.

Alexander Korotkov, Sveta Smirnova Open Source SQL databases enters millions queries per second era 9 / 33

SLIDE 10

How to run this? It’s on the github and reproducible!

$ git clone https://github.com/postgrespro/pg_oltp_bench.git $ cd pg_oltp_bench $ make USE_PGXS=1 $ sudo make USE_PGXS=1 install $ psql DB -f oltp_init.sql $ psql DB -c "CREATE EXTENSION pg_oltp_bench;" $ pgbench -c 100 -j 100 -M prepared -f oltp_ro.sql -T 300 -P 1 DB $ pgbench -c 100 -j 100 -M prepared -f oltp_rw.sql -T 300 -P 1 DB

Alexander Korotkov, Sveta Smirnova Open Source SQL databases enters millions queries per second era 10 / 33

SLIDE 11

Inequal comparison!

Alexander Korotkov, Sveta Smirnova Open Source SQL databases enters millions queries per second era 11 / 33

SLIDE 12

Benchmark: Point selects

Alexander Korotkov, Sveta Smirnova Open Source SQL databases enters millions queries per second era 12 / 33

SLIDE 13

Benchmark: OLTP RO results

Alexander Korotkov, Sveta Smirnova Open Source SQL databases enters millions queries per second era 13 / 33

SLIDE 14

Benchmark: OLTP RW results

Alexander Korotkov, Sveta Smirnova Open Source SQL databases enters millions queries per second era 14 / 33

SLIDE 15

That was close...

Alexander Korotkov, Sveta Smirnova Open Source SQL databases enters millions queries per second era 15 / 33

SLIDE 16

Pin/UnpinBufger in lockless manner Before ”touching”any block of data, backend have to ”pin”correcponding bufger. Pin/UnpinBufger – very frequent

peration.

Before: S_LOCK(bufHdr); bufHdr->pinCount++; S_UNLOCK(bufHdr); Large: atomic_increment(buf_hdr->pinCount); See commit details: https://goo.gl/LLCvR8.

Alexander Korotkov, Sveta Smirnova Open Source SQL databases enters millions queries per second era 16 / 33

SLIDE 17

Reduce ProcArrayLock contention

▶ Snapshot contains list of running transaction ids. Getting

snapshot requires shared ProcArrayLock.

▶ Transaction commit clears its id from shared memory.

Committing transaction requires exclusive ProcArrayLock.

▶ High TPS leads to high ProcArrayLock contention. ▶ Solution: clear transaction id in group.

See commit details: https://goo.gl/ZxiilI.

Alexander Korotkov, Sveta Smirnova Open Source SQL databases enters millions queries per second era 17 / 33

SLIDE 18

Reduce CLogControlLock contention

▶ Getting transaction status requires shared CLogControlLock.

Setting transaction status requires exclusive CLogControlLock. Reading new CLOG page requires exclusive CLogControlLock.

▶ On modern multicore systems, backends frequently get

transaction status. Number of demanded transactions is also high.

▶ Solution: increase CLOG bufgers from 32 to 128. We would

have to read CLOG pages rarely. See commit details: https://goo.gl/aaPYsJ.

Alexander Korotkov, Sveta Smirnova Open Source SQL databases enters millions queries per second era 18 / 33

SLIDE 19

What is PostgreSQL bottleneck?

Alexander Korotkov, Sveta Smirnova Open Source SQL databases enters millions queries per second era 19 / 33

SLIDE 20

What ARE PostgreSQL bottlenecks?

Alexander Korotkov, Sveta Smirnova Open Source SQL databases enters millions queries per second era 20 / 33

SLIDE 21

What are PostgreSQL bottlenecks?

▶ Bufger manager – slow hash-table, pin, locks etc. ▶ Snapshots – for each new snapshot we have to iterate over each

active transaction. It’s O(n2) where n – number of active sessions.

▶ Synchronous protocol. ▶ Slow xid allocation – a lot of locks.

Alexander Korotkov, Sveta Smirnova Open Source SQL databases enters millions queries per second era 21 / 33

SLIDE 22

PostgreSQL bottlenecks in numbers

▶ SELECT val FROM tab WHERE id IN (:id1, ... :id10)

– 150K per second = 1.5M points per second, no gain. Bottleneck in locks.

▶ 10 x SELECT 1 in single command – 2.2M queries per second.

Taking snapshots is a bottleneck.

▶ SELECT 1 with CSN patch (cheap snapshots) – 3.9M queries

per second. Protocol is a bottleneck.

▶ SELECT txid_current() – 390K per second. Bottleneck in

locks.

Alexander Korotkov, Sveta Smirnova Open Source SQL databases enters millions queries per second era 22 / 33

SLIDE 23

How can we improve PostgreSQL?

▶ True in-memory engine without bufger manager. ▶ CSN for faster snapshots. ▶ Asynchronous binary protocol for processing more short queries. ▶ Lockless xid allocation.

Alexander Korotkov, Sveta Smirnova Open Source SQL databases enters millions queries per second era 23 / 33

SLIDE 24

At the MySQL side

▶ It could be easy for me

▶ Dimitri continuously publishing very detailed test results ▶ I could just ask Alexander to check how PostgreSQL is doing

▶ Original purpose of this investigation

▶ Many use heterogeneous database setups ▶ Some have better experience with one of databases ▶ All solve real-life issues ▶ Speed of writes on master ▶ Maximum performance for read-only slave ▶ Efgect of checksums, synchronizations, compression ▶ How to get best results from each database on same hardware?

▶ We have to use same test base

Alexander Korotkov, Sveta Smirnova Open Source SQL databases enters millions queries per second era 24 / 33

SLIDE 25

Strangeness for read-write

▶ Percona test machine

▶ Processors: physical = 2, cores = 12, virtual = 24, hyperthreading = yes ▶ Memory: 251.9G ▶ Disk speed: about 33K IOPS

▶ Postgres Professional’s test machine

▶ Processors: physical = 4, cores = 72, virtual = 144, hyperthreading = yes ▶ Memory: 3,0T ▶ Disk speed: about 3K IOPS Alexander Korotkov, Sveta Smirnova Open Source SQL databases enters millions queries per second era 25 / 33

SLIDE 26

Initial read-write test results

▶ Percona test machine

OLTP test statistics: transactions: 1000000 (28727.81 per sec.) read/write requests: 5000000 (143639.05 per sec.)

ther operations:

2000000 (57455.62 per sec.)

▶ Postgres Professional’s test machine

transactions: 1000000 (29784.74 per sec.) read/write requests: 5000000 (148923.71 per sec.)

ther operations:

2000000 (59569.49 per sec.)

▶ Almost same ▶ I want to gain performance from all cores!

Alexander Korotkov, Sveta Smirnova Open Source SQL databases enters millions queries per second era 26 / 33

SLIDE 27

Try #2: Read-only test can be 100% in memory

▶ 700 QPS after initial run ▶ SysBench uses as much CPU as MySQL

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 4585 smirnova 20 0,157t 0,041t 9596 S 7226 1,4 12:27.16 mysqld 8745 smirnova 20 0 1266212 629148 1824 S 7126 0,0 9:22.78 sysbench

▶ Solution

▶ Run sysbench with option –percentile=0 ▶ Run several parallel sysbench processes ▶ Using –num-threads less than 36 improves CPU usage ▶ Still not ideal ▶ Maximum was 1,217,873 QPS for 256 threads

▶ Writing proper benchmarks is challenging ▶ I have to stuck with Dimitri’s results

Alexander Korotkov, Sveta Smirnova Open Source SQL databases enters millions queries per second era 27 / 33

SLIDE 28

Changes in MySQL which made this happen

▶ InnoDB: transaction list optimization

▶ Version 5.7.2: global transaction list was split into two ▶ Read-write ▶ Read-only ▶ Version 5.7.3: by default transaction not put into any list unless it started with

ption READ WRITE

▶ Read only transactions are mutex-free ▶ READ ONLY transactions are not visible in SHOW ENGINE INNODB STATUS

utput

▶ More details ▶ WL #6047

InnoDB: Reduce lock_sys_t::mutex contention, WL #6899 InnoDB: fjx index->lock contention WL #6326 InnoDB: faster & parallel fmushing MDL (Meta-Data Lock) scalability

Alexander Korotkov, Sveta Smirnova Open Source SQL databases enters millions queries per second era 28 / 33

SLIDE 29

Changes in MySQL which made this happen

▶ InnoDB: transaction list optimization ▶ InnoDB: Reduce lock_sys_t::mutex contention, WL #6899 ▶ InnoDB: fjx index->lock contention WL #6326 ▶ InnoDB: faster & parallel fmushing

▶ Multiple page cleaner threads: WL #6642 ▶ Reduced number of pages which needs to be fmushed: WL #7047 ▶ Improved adaptive fmushing: WL #7868

MDL (Meta-Data Lock) scalability

Alexander Korotkov, Sveta Smirnova Open Source SQL databases enters millions queries per second era 28 / 33

SLIDE 30

Changes in MySQL which made this happen

▶ InnoDB: transaction list optimization ▶ InnoDB: Reduce lock_sys_t::mutex contention, WL #6899 ▶ InnoDB: fjx index->lock contention WL #6326 ▶ InnoDB: faster & parallel fmushing ▶ MDL (Meta-Data Lock) scalability

▶ Remove THR_LOCK::mutex for InnoDB: WL #6671 ▶ Partitioned LOCK_grant ▶ Number of partitions is constant ▶ Thread ID used to assign partition ▶ WL #8355 ▶ Bug #72829 ▶ Lock-free MDL lock acquisition for DML: WL #7306, WL #7305 Alexander Korotkov, Sveta Smirnova Open Source SQL databases enters millions queries per second era 28 / 33

SLIDE 31

Other improvements in MySQL which afgect performance

▶ Performance Schema is cheaper than before

▶ I did not notice any difgerence while was running benchmarks ▶ I did not turn ON any instruments

▶ innodb_checksum_algorithm is crc32 by default ▶ InnoDB Temporary Table Performance

▶ No UNDO and REDO logging ▶ No Insert bufgering ▶ No persistence ▶ WL #6469, WL #6470, WL #6915, https://goo.gl/LeIYD4

▶ InnoDB bufger pool dump and reload ▶ More

Alexander Korotkov, Sveta Smirnova Open Source SQL databases enters millions queries per second era 29 / 33

SLIDE 32

Special Thanks

▶ Freematiq for provided servers. ▶ MySQL Server Team ▶ MySQL InnoDB Team ▶ Dimitri Kravtchuk ▶ Alexey Kopytov

Alexander Korotkov, Sveta Smirnova Open Source SQL databases enters millions queries per second era 30 / 33

SLIDE 33

Tools used

▶ sysbench ▶ pgbench ▶ pg_oltp_bench ▶ open-database-bench ▶ PGXACT cacheline align patch

Alexander Korotkov, Sveta Smirnova Open Source SQL databases enters millions queries per second era 31 / 33

SLIDE 34

Rate Our Session!

Alexander Korotkov, Sveta Smirnova Open Source SQL databases enters millions queries per second era 32 / 33

SLIDE 35

Thank you for attention!

Alexander Korotkov, Sveta Smirnova Open Source SQL databases enters millions queries per second era 33 / 33