Persistent Memory Performance Benchmark (MySQL TPC-C Benchmark) #PersistentMemory #MySQL

Persistent Memory Performance Benchmark (MySQL TPC-C Benchmark) July 8, 2019 Shohei Matsuura Copyright (C) 2019 Yahoo Japan Corporation. All Rights Reserved.

Benchmark Environment Optane Server TPC-C(OLTP) Benchmark Program Warehouse=1K* MySQL 8.0.16/InnoDB Ubuntu 18.10(4.18.0-20) 1. DB Data + Log Data on SSD Intel® SSD DC S4600 Series (480GB, 2.5in SATA 6Gb/s) XFS 2. DB Data + Log Data on PMEM Intel® Optane™ DC Persistent Memory 2666 MT/s XFS with DAX *TPC-C Warehouse=1K is approximately 80GB and 90GB with indexes in size. Copyright (C) 2019 Yahoo Japan Corporation. All Rights Reserved. 1

Benchmark Environment (Contʼd) CPU Intel® Xeon® Gold 6230 Processor 2.10GHz x 2 (40 cores, 80 threads) Memory Micron DDR4-2933 16.0GB x 12 (192GB) Persistent Memory (PMEM) Intel® Optane™ DC Persistent Memory 128GB Module 2666 x 12 (1,536 GB) Local Storage(SSD) Intel® SSD DC S4600 Series (480GB, 2.5in SATA 6Gb/s, 3D1, TLC) • Throughput: Read 500 MB/sec, Write 490 MB/ses • I/O Latency: Sequential Read/Write: 36 usec/36 usec Random Read/Write: 112 usec/52 usec • IOPS: Random 4KB Reads 72,000 IOPS, 4KB Random Writes 65,000 IOPS Fabrics Intel Ethernet Connection X722 for 10GBASE OS Ubuntu 18.10 4.18.0-20-generic DBMS MySQL 8.0.16 TPC-C Client Program tpcc.lua (sysbench) Copyright (C) 2019 Yahoo Japan Corporation. All Rights Reserved. 2

MySQL Configuration • Isolation Level: Read Committed • Page Size: 16KB(default) • Auto Commit Disabled • InnoDB Buffer Pool: 2GB • InnoDB Log Buffer: 2GB • Binlog Enabled • innodb_flush_log_at_trx_commit=1 Ø log persisted at each commit Copyright (C) 2019 Yahoo Japan Corporation. All Rights Reserved. my.cnf [mysqld] datadir=/db #datadir=/home/shmatsuu/dbs user=shmatsuu max_connections=1024 secure-file-priv = "" innodb_open_files=1000 innodb_flush_log_at_trx_commit=1 innodb_log_buffer_size=2GB innodb_buffer_pool_size=2GB innodb_thread_concurrency=0 max_prepared_stmt_count=1000000 autocommit=0 transaction-isolation = READ-COMMITTED local-infile=1 3

TPC-C Transaction Throughput TPC-C BENCHMARK (MySQL8.0.16 InnoDB) 2000 1800 1600 1400 TRANSACTIONS/SEC 1200 1000 mysql-ssd mysql-pmem 800 600 400 200 0 0 50 100 150 CONCURRENCY Copyright (C) 2019 Yahoo Japan Corporation. All Rights Reserved. 200 250 300 4

CPU Utilization • CPU is not fully used in the both cases (SSD, PMEM) even if 160 concurrent workloads are executed, i.e. twice of (logical) cpu cores on the sever • The amount of sys in cpu time is notable, possibly indicating high lock contentions in PMEM • Small amount of I/O wait is observable in SSD CPU Utilization(MySQL8.0.16 InnoDB, PMEM, Concurrency=160) 90 90 80 80 70 70 st wa 50 sy 40 us 60 st sy 40 30 30 20 20 10 10 0 0 Copyright (C) 2019 Yahoo Japan Corporation. All Rights Reserved. wa 50 us 1 15 29 43 57 71 85 99 113 127 141 155 169 183 197 211 225 239 253 267 281 295 309 323 337 351 365 379 393 407 421 435 449 463 477 491 505 519 533 547 561 575 589 60 CPU Utilization(%) 100 100 1 15 29 43 57 71 85 99 113 127 141 155 169 183 197 211 225 239 253 267 281 295 309 323 337 351 365 379 393 407 421 435 449 463 477 491 505 519 533 547 561 575 589 CPU Utilization(%) CPU Utilization(MySQL8.0.16 InnoDB, SSD, Concurrency=160) 5

I/O Throughput • In both SSD and PMEM, I/Os are write-intensive to write data pages at checkpoint and logs at commit/checkpoints. • Considering a reported 1.5 GB/sec of write I/O throughput of PMEM*, a huge room for unused bandwidth is observable, possibly indicating that MySQL is not fully utilizing PMEM capability in this benchmark. • As we are running the same transaction workload for SSD and PMEM, the amount of writes/reads must be the same. So, the I/O graphs for SSD and PMEM must be similar to each other. However, the below graphs look they are different. This is because I/O stats are collected at 1 sec interval for both SSD and PMEM and as the I/O latency of PMEM is much smaller than SSD, the interval is not adequate to capture the I/O throughput of PMEM at full resolution. Read I/Os are observable in SSD but not in PMEM. The same reason would explain this difference as well. I/O Throughput(MySQL8.0.16 InnoDB, PMEM, Concurrency=160) 500 450 450 400 400 350 350 MB_read/s 250 MB_wrtn/s 200 150 300 MB_wrtn/s 200 150 100 100 50 50 0 0 1 16 31 46 61 76 91 106 121 136 151 166 181 196 211 226 241 256 271 286 301 316 331 346 361 376 391 406 421 436 451 466 481 496 511 526 541 556 571 586 MB_read/s 250 1 17 33 49 65 81 97 113 129 145 161 177 193 209 225 241 257 273 289 305 321 337 353 369 385 401 417 433 449 465 481 497 513 529 545 561 577 593 300 I/O Throughput(MB/sec) I/O Throughput(MB/sec) I/O Throughput(MySQL8.0.16 InnoDB, SSD, Concurrency=160) 500 * “ Izraelevitz, J, et al. “Basic Performance Measurements of the Intel Optane DC Persistent Memory Module.” https://arxiv.org/abs/1903.05714 Copyright (C) 2019 Yahoo Japan Corporation. All Rights Reserved. 7

Locking Status
•

Some transactions are waiting for record locks to be released.
mysql> SELECT * FROM performance_schema.data_locks where LOCK_STATUS!='GRANTED';
+--------+--------------------------------------------+-----------------------+-----------+----------+--------------+-------------+----------------+-------------------+------------+-----------------------+-----------+--------------+-------------+---------------+
| ENGINE | ENGINE_LOCK_ID
| ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID |
OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN |
LOCK_TYPE | LOCK_MODE
| LOCK_STATUS | LOCK_DATA
|
+--------+--------------------------------------------+-----------------------+-----------+----------+--------------+-------------+----------------+-------------------+------------+-----------------------+-----------+--------------+-------------+---------------+
| INNODB | 139625518006128:6:3074:506:139611872107912 |
4304024 |
240 |
106387 |
TPCCSSD
| new_orders | NULL
| NULL
| PRIMARY
|
139611872107912 |
RECORD
| X,REC_NOT_GAP | WAITING
| 202, 9, 2179 |
| INNODB | 139625517929288:6:8156:443:139611871604152 |
4303877 |
323 |
127193 |
TPCCSSD
| new_orders | NULL
| NULL
| PRIMARY
|
139611871604152 |
RECORD
| X,REC_NOT_GAP | WAITING
| 539, 10, 2159 |
| INNODB | 139625517966352:6:13092:13:139611871846408 |
4304202 |
272 |
117864 |
TPCCSSD
| new_orders | NULL
| NULL
| PRIMARY
|
139611871846408 |
RECORD
| X,REC_NOT_GAP | WAITING
| 867, 8, 2176 |
| INNODB | 139625517980816:6:885:537:139611871942440 |
4305292 |
329 |
124838 |
TPCCSSD
| new_orders | NULL
| NULL
| PRIMARY
|
139611871942440 |
RECORD
| X,REC_NOT_GAP | WAITING
| 57, 7, 2172
|
+--------+--------------------------------------------+-----------------------+-----------+----------+--------------+-------------+----------------+-------------------+------------+-----------------------+-----------+--------------+-------------+---------------+

Copyright (C) 2019 Yahoo Japan Corporation. All Rights Reserved.

6

Thank you! Copyright (C) 2019 Yahoo Japan Corporation. All Rights Reserved.