Persistent Memory Performance Benchmark(MySQL OLTP Benchmark) #persistentmemory #inteloptane #inteloptanedcpersistentmemory #MySQL #InnoDB #oltp #benchmark

Persistent Memory Performance Benchmark (MySQL OLTP Benchmark) September 9, 2019 Shohei Matsuura Copyright (C) 2019 Yahoo Japan Corporation. All Rights Reserved.

What this slide is about? • A series of performance benchmark is conducted to understand the performance characteristics of MySQL/InnoDB on Intelʼs persistent memory, when it handles OLTP workloads. Previously, the initial benchmark result has been posted to MySQL Community*. Having discussed and revised different server parameter settings for MySQL/InnoDB from the initial setting with the Community, the same benchmark and a new benchmark are run again now with the revised parameter settings. This slide illustrates the updated benchmark results with these revised parameter settings. * https://www.slideshare.net/techblogyahoo/persistent-memory-performance-benchmark-mysql-tpcc-benchmark-persistentmemory-mysql Copyright (C) 2019 Yahoo Japan Corporation. All Rights Reserved. 2

Contents 1. Introduction 2. Benchmarks 3. Findings Copyright (C) 2019 Yahoo Japan Corporation. All Rights Reserved. 3

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

What is Persistent Memory? Ø Memory with data persistency characteristic Ø Slower than DRAM but faster than SSD Ø More capacity than DRAM and less capacity than SSD Higher Volatile Non-volatile Performance DRAM Persistent Memory SSD Capacity Copyright (C) 2019 Yahoo Japan Corporation. All Rights Reserved. Larger 5

What is Persistent Memory? Ø Now persistent memory is available from Intel as Intel® Optane™ DC Persistent Memory* Ø Ø Ø Ø * DDR-T interface Load/Store Access Model (Byte Addressable as DRAM) Capacity Lineups(As of Aug. 1, 2019)*: 128GB, 256GB, 512GB per DIMM slot Maximum 3 channels for persistent memory per memory controller, and maximum 6 channels for persistent memory per socket (i.e. 512 GB x 6 =3TB/socket) https://www.intel.com/content/www/us/en/architecture-and-technology/optane-dc-persistent-memory.html ** https://ark.intel.com/content/www/us/en/ark/products/series/190349/intel-optane-dc-persistent-memory.html Copyright (C) 2019 Yahoo Japan Corporation. All Rights Reserved. 6

Two Operating Modes of Intel® Optane™ DC Persistent Memory 1. Memory Mode • • Use persistent memory as an extension of DRAM to provide lager memory capacity at a lower cost Persistency disabled when operating in this mode 2. AppDirect Mode • ß Our benchmark uses this mode. Persistency enabled and data written to the memory persists even after a reboot Copyright (C) 2019 Yahoo Japan Corporation. All Rights Reserved. 7

Exposing Persistent Memory to Operating System and Software SNIA persistent memory programming model* defines to access persistent memory (PMEM) as a memory-mapped file. * A.Rudoff. “Persistent Memory Programming.” https://www.usenix.org/system/files/login/articles/login_summer17_07_rudoff.pdf Copyright (C) 2019 Yahoo Japan Corporation. All Rights Reserved. 8

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

Benchmark Test Cases Ø Our benchmark test cases focus on OLTP workloads. 1. sysbench oltp_read_only & oltp_write_only • To understand the basic performance characteristics of MySQL/InnoDB when it is run on PMEM 2. sysbench TPC-C-like benchmark • Ø To understand the performance characteristics when handling more complex OLTP workload on PMEM These benchmark cases are tested both on SSD and PMEM. Copyright (C) 2019 Yahoo Japan Corporation. All Rights Reserved. 10

Benchmark Environment Optane Server OLTP Benchmark Program (sysbench) 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 6 x Intel® Optane™ DC Persistent Memory (Interleaved) 2666 MT/s XFS with DAX Copyright (C) 2019 Yahoo Japan Corporation. All Rights Reserved. 11

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 6 (768 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 Benchmark Program sysbench (oltp_read_only, oltp_write_only, tpcc.lua) Database Size • • Copyright (C) 2019 Yahoo Japan Corporation. All Rights Reserved. oltp_read_only & oltp_write_only: 21GB (100M records) TPC-C-like: 97GB (1K Warehouses) 12

MySQL Configuration • Isolation Level: Read Committed • Page Size: 16KB(default) • Auto Commit Disabled • InnoDB Buffer Pool: 120GB • InnoDB Log Buffer: 256MB • InnoDB Log Files: 2GB x 8 • Undo Log Truncate: off • Binlog Enabled • innodb_flush_log_at_trx_commit=1 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=64MB innodb_buffer_pool_size=120GB innodb_log_file_size=2GB innodb_log_files_in_group=8 innodb_undo_log_truncate=off innodb_thread_concurrency=0 max_prepared_stmt_count=1000000 autocommit=0 transaction-isolation = READ-COMMITTED local-infile=1 13

MySQL Configuration & Buffer Hit Rate • In the both test cases #1(oltp_read_only & oltp_write_only) and #2 (TPC-C), InnoDBʼs buffer pool size is varied to observe how a different buffer pool size and hit rate change the transaction throughput both on SSD and PMEM. my.cnf This value is varied. Copyright (C) 2019 Yahoo Japan Corporation. All Rights Reserved. [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=64MB innodb_buffer_pool_size=120GB innodb_log_file_size=2GB innodb_log_files_in_group=8 innodb_undo_log_truncate=off innodb_thread_concurrency=0 max_prepared_stmt_count=1000000 autocommit=0 transaction-isolation = READ-COMMITTED local-infile=1 14

2.1 OLTP Read-Only & Write-Only Copyright (C) 2019 Yahoo Japan Corporation. All Rights Reserved.

OLTP Read-Only & Write-Only Table Definition Table: sbtest1 id k c pad int(11) int(11) char(120) char(60) Indexes: • on ʻidʼ column • on ʻkʼ column Copyright (C) 2019 Yahoo Japan Corporation. All Rights Reserved. CREATE TABLE sbtest1( id int(11) NOT NULL AUTO_INCREMENT, k int(11) NOT NULL DEFAULT '0', c char(120) NOT NULL DEFAULT '', pad char(60) NOT NULL DEFAULT '', PRIMARY KEY (id), KEY k_1 (k) ) ENGINE=InnoDB AUTO_INCREMENT=100000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | 16

OLTP Read-Only Queries • 10 selects to fetch a record with a given primary key value • 4 range selects (some are with aggregation and sorting) Query BEGIN Execute SELECT c FROM sbtest1 WHERE id=49761902 Execute SELECT c FROM sbtest1 WHERE id=50095054 Execute SELECT c FROM sbtest1 WHERE id=50218359 Execute SELECT c FROM sbtest1 WHERE id=50036041 Execute SELECT c FROM sbtest1 WHERE id=50158760 Execute SELECT c FROM sbtest1 WHERE id=56502967 Execute SELECT c FROM sbtest1 WHERE id=40222254 Execute SELECT c FROM sbtest1 WHERE id=50325783 Execute SELECT c FROM sbtest1 WHERE id=48111821 Execute SELECT c FROM sbtest1 WHERE id=51782209 Execute SELECT c FROM sbtest1 WHERE id BETWEEN 52442450 AND 52442549 Execute SELECT SUM(k) FROM sbtest1 WHERE id BETWEEN 53714040 AND 53714139 Execute SELECT c FROM sbtest1 WHERE id BETWEEN 50185875 AND 50185974 ORDER BY c Execute SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN 50244175 AND 50244274 ORDER BY c Execute COMMIT Copyright (C) 2019 Yahoo Japan Corporation. All Rights Reserved. 17

OLTP Write-Only Queries • 2 update statements with 1 updating a column an index is defined, given a primary-key value • 1 delete statement, given a primary-key value • 1 insert statement, given a primary-key value Query BEGIN Execute UPDATE sbtest1 SET k=k+1 WHERE id=50237916 Execute UPDATE sbtest1 SET c='94942489290-07237061387-7710367359465277717058-18064189727-00853698175-09604417512-6397100147328576335929-82283004321' WHERE id=48521944 Execute DELETE FROM sbtest1 WHERE id=50073229 Execute INSERT INTO sbtest1 (id, k, c, pad) VALUES (50073229, 55454370, '84802913111-25973477350-60097478522-25049777500-9407260740201106890643-66834792746-94293528900-04994085436-81672674624', '41316155228-00735849835-70818752206-73315185025-20027991371') Execute COMMIT Copyright (C) 2019 Yahoo Japan Corporation. All Rights Reserved. 18

Read-Only Transaction Throughput • Buffer Size =120GB No significant difference between SSD and persistent memory, as all data reside on memory in the both cases with this buffer size and it is read-only workload. OLTP READ ONLY TEST (MySQL/InnoDB 8.0.16) 30000 25000 Transactions/sec 20000 15000 mysql-ssd mysql-pmem 10000 5000 0 0 50 100 150 200 250 300 Concurrency Copyright (C) 2019 Yahoo Japan Corporation. All Rights Reserved. 19

Read-Only Transaction CPU Utilization (Concurrency=40) • No significant difference in the utilization. • MySQL/InnoDB utilizes CPU in an effective manner, i.e. with 40 concurrent workloads on an 80-core machine, 50% of CPU is being utilized in the both SSD and PMEM cases. CPU Utilization(MySQL 8.0.16 InnoDB, PMEM,Concurrency=40) CPU Utilization(MySQL 8.0.16 InnoDB, SSD,Concurrency=40) 100 100 90 80 70 70 60 st wa 50 sy 40 us CPU Utilization(%) 80 60 st sy 40 30 20 20 10 10 0 0 1 10 19 28 37 46 55 64 73 82 91 100 109 118 127 136 145 154 163 172 181 190 199 208 217 226 235 244 253 262 271 280 289 298 307 316 325 30 Copyright (C) 2019 Yahoo Japan Corporation. All Rights Reserved. wa 50 us 1 9 17 25 33 41 49 57 65 73 81 89 97 105 113 121 129 137 145 153 161 169 177 185 193 201 209 217 225 233 241 249 257 265 273 281 289 297 305 313 321 329 90 CPU Utilization(%) Buffer Size =120GB 20

Read-Only Transaction I/O Statistics (Concurrency=40) No read and write I/Os are observed as the entire data fits in memory and is read from there. I/O Throughput (MySQL 8.0.16 InnoDB, PMEM, Concurrency =40) 1400 1400 1200 1200 1000 800 MB_read/s MB_wrtn/s 600 400 I/O Throughput(MB/sec) I/O Throughput(MB/sec) I/O Throughput (MySQL 8.0.16 InnoDB, SSD, Concurrency=40) 1000 800 MB_wrtn/s 400 200 0 0 1 11 21 31 41 51 61 71 81 91 101 111 121 131 141 151 161 171 181 191 201 211 221 231 241 251 261 271 281 291 301 311 321 MB_read/s 600 200 Copyright (C) 2019 Yahoo Japan Corporation. All Rights Reserved. Buffer Size =120GB 1 12 23 34 45 56 67 78 89 100 111 122 133 144 155 166 177 188 199 210 221 232 243 254 265 276 287 298 309 320 • 21

Read-Only Transaction CPU Utilization (Concurrency=160) • No significant difference in the utilization. • MySQL/InnoDB utilizes CPU in an effective manner, i.e. with 160 concurrent workloads on an 80-core machine, 100% of CPU is being utilized. CPU Utilization(MySQL 8.0.16 InnoDB, PMEM,Concurrency=160) 90 90 80 80 70 70 wa 50 sy 40 us 60 wa sy 40 30 30 20 20 10 10 0 0 Copyright (C) 2019 Yahoo Japan Corporation. All Rights Reserved. st 50 us 1 9 17 25 33 41 49 57 65 73 81 89 97 105 113 121 129 137 145 153 161 169 177 185 193 201 209 217 225 233 241 249 257 265 273 281 289 297 305 313 321 329 st CPU Utilization(%) 100 100 1 10 19 28 37 46 55 64 73 82 91 100 109 118 127 136 145 154 163 172 181 190 199 208 217 226 235 244 253 262 271 280 289 298 307 316 325 CPU Utilization(%) CPU Utilization(MySQL 8.0.16 InnoDB, SSD,Concurrency=160) 60 Buffer Size =120GB 22

Read-Only Transaction I/O Statistics (Concurrency=160) No read and write I/Os are observed as the entire data fits in memory and is read from there. I/O Throughput (MySQL 8.0.16 InnoDB, PMEM, Concurrency =160) 1400 1200 1200 1000 800 MB_read/s MB_wrtn/s 600 I/O Throughput(MB/sec) 1400 1000 800 400 200 200 0 0 Copyright (C) 2019 Yahoo Japan Corporation. All Rights Reserved. MB_read/s MB_wrtn/s 600 400 1 11 21 31 41 51 61 71 81 91 101 111 121 131 141 151 161 171 181 191 201 211 221 231 241 251 261 271 281 291 301 311 321 I/O Throughput(MB/sec) I/O Throughput (MySQL 8.0.16 InnoDB, SSD, Concurrency=160) Buffer Size =120GB 1 11 21 31 41 51 61 71 81 91 101 111 121 131 141 151 161 171 181 191 201 211 221 231 241 251 261 271 281 291 301 311 321 • 23

Read-Only Transaction Throughput & Buffer Hit Rate • At high buffer hit rate (100% or 70%), no significant difference in transaction throughput between SSD and PMEM, as most of the time data is read from memory. • At the buffer hit rate 35%, slightly the transaction throughput with PMEM is higher, but not a significant difference. • At a low buffer hit rate (4%), approximately 1.5 times higher transaction throughput is achieved with PMEM compared to SSD. This is due to a higher read I/O performance of PMEM. OLTP READ ONLY TEST (MySQL InnoDB 8.0.16, PMEM) 30000 25000 25000 20000 20000 buffer _hit=100% 15000 buffer _hit=70% buffer _hit=35% buffer _hit=4% Transactions/sec Transactions/sec OLTP READ ONLY TEST (MySQL InnoDB 8.0.16, SSD) 30000 buffer _hit=100% 15000 buffer _hit=70% buffer _hit=35% 10000 10000 5000 5000 buffer _hit=4% 0 0 0 50 100 150 Concurrency Copyright (C) 2019 Yahoo Japan Corporation. All Rights Reserved. 200 250 300 0 50 100 150 200 250 300 Concurrency 24

Write-Only Transaction Throughput • Significant difference in transaction throughput in write-only workload. • 2 times or more higher transaction throughput with PMEM until the concurrency=80. The difference tends to converge as the concurrency increases beyond 80, possibly due to increasing lock contentions. Copyright (C) 2019 Yahoo Japan Corporation. All Rights Reserved. OLTP WRITE ONLY TEST (MySQL/InnoDB 8.0.16) 60000 50000 40000 Transactions/sec • Buffer Size =120GB 30000 mysql-ssd mysql-pmem 20000 10000 0 0 50 100 150 200 250 300 Concurrency 25

Write-Only Transaction CPU Utilization (Concurrency=40) • Higher CPU utilization with PMEM. • Small I/O wait observable (but hard to see with the color!) with SSD, causing a lower CPU utilization. • Even with a higher CPU utilization with PMEM, it is lower than the read-only case. Ideally speaking, with concurrency=40 on an 80-core machine, CPU utilization should be around 50%. But, the utilization turns out to be around 40%. This is possibility indicating that InnoDB is still not able to use CPU effectively with concurrent write workloads. CPU Utilization(MySQL 8.0.16 InnoDB, PMEM,Concurrency=40) 90 90 80 80 70 70 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 10 19 28 37 46 55 64 73 82 91 100 109 118 127 136 145 154 163 172 181 190 199 208 217 226 235 244 253 262 271 280 289 298 307 316 325 st CPU Utilization(%) 100 100 1 10 19 28 37 46 55 64 73 82 91 100 109 118 127 136 145 154 163 172 181 190 199 208 217 226 235 244 253 262 271 280 289 298 307 316 325 CPU Utilization(%) CPU Utilization(MySQL 8.0.16 InnoDB, SSD,Concurrency=40) 60 Buffer Size =120GB 26

Write-Only Transaction I/O Statistics (Concurrency=40) • Less than a half of I/O throughput is achieved with SSD, compared to PMEM. • Considering a reported 13.9 GB/sec I/O bandwidth of PMEM* for sequential write with 6 PMEM, the bandwidth of PMEM is not fully utilized with this concurrency. I/O Throughput (MySQL 8.0.16 InnoDB, PMEM, Concurrency=40) I/O Throughput (MySQL 8.0.16 InnoDB, SSD, Concurrency=40) 1400 1200 1000 1000 800 MB_read/s MB_wrtn/s 600 I/O Throughput(MB/sec) 1200 800 MB_wrtn/s 600 400 400 200 200 0 0 1 11 21 31 41 51 61 71 81 91 101 111 121 131 141 151 161 171 181 191 201 211 221 231 241 251 261 271 281 291 301 311 321 MB_read/s 1 9 17 25 33 41 49 57 65 73 81 89 97 105 113 121 129 137 145 153 161 169 177 185 193 201 209 217 225 233 241 249 257 265 273 281 289 297 305 313 321 329 1400 I/O Throughput(MB/sec) Buffer Size =120GB * “ 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. 27

Write-Only Transaction CPU Utilization (Concurrency=160) • Higher CPU utilization with PMEM. • Small I/O wait observable with SSD, causing a lower CPU utilization. • Even with twice of concurrent workloads than the number of the CPU cores, CPU is not fully utilized with InnoDB again, possibly due to lock contentions. CPU Utilization(MySQL 8.0.16 InnoDB, PMEM,Concurrency=160) 100 90 90 80 80 70 70 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 10 19 28 37 46 55 64 73 82 91 100 109 118 127 136 145 154 163 172 181 190 199 208 217 226 235 244 253 262 271 280 289 298 307 316 325 st CPU Utilization(%) 100 1 10 19 28 37 46 55 64 73 82 91 100 109 118 127 136 145 154 163 172 181 190 199 208 217 226 235 244 253 262 271 280 289 298 307 316 325 CPU UTilization(%) CPU Utilization(MySQL 8.0.16 InnoDB, SSD,Concurrency=160) 60 Buffer Size =120GB 28

Write-Only Transaction I/O Statistics (Concurrency=160) The I/O throughput achieved with concurrency=160 is similar to whatʼs achieved with concurrency=40. • Buffer Size =120GB • For SSD: 200MB/sec on average • For PMEM: 400-600 MB/sec on average I/O throughput does not increase as the number of concurrency, which is possibly another indication of locking contentions occurring in this workload. I/O Throughput (MySQL 8.0.16 InnoDB, PMEM, Concurrency=160) 1400 1200 1200 1000 1000 800 MB_read/s MB_wrtn/s 600 I/O Throughput(MB/sec) 1400 800 MB_wrtn/s 400 200 200 0 0 Copyright (C) 2019 Yahoo Japan Corporation. All Rights Reserved. MB_read/s 600 400 1 11 21 31 41 51 61 71 81 91 101 111 121 131 141 151 161 171 181 191 201 211 221 231 241 251 261 271 281 291 301 311 321 I/O Throughput(MB/sec) I/O Throughput (MySQL 8.0.16 InnoDB, SSD, Concurrency=160) 1 10 19 28 37 46 55 64 73 82 91 100 109 118 127 136 145 154 163 172 181 190 199 208 217 226 235 244 253 262 271 280 289 298 307 316 325 • 29

Write-Only Transaction Throughput & Buffer Hit Rate • At all buffer hit rate, PMEM outperforms SSD nearly 2 times or more in throughput. Throughput difference is more significant at lower concurrency that higher concurrency. • Even with buffer hit rate=100%, where no buffer page eviction happens except at checkpoints, the difference is significant. With this buffer hit rate, major I/Os to SSD and PMEM, except checkpoints, are flushing a log at each transaction commit. This implies that the difference largely comes from I/O performance difference in writes of SSD and PMEM. OLTP WRITE ONLY TEST (MySQL InnoDB 8.0.16, PMEM) 60000 50000 50000 40000 40000 buffer_hit=100% 30000 buffer_hit=70% buffer_hit=35% buffer_hit=4% Transactions/sec Transactions/sec OLTP WRITE ONLY TEST (MySQL InnoDB 8.0.16, SSD) 60000 buffer _hit=100% 30000 buffer _hit=70% buffer _hit=35% 20000 20000 10000 10000 buffer _hit=4% 0 0 0 50 100 150 Concurrency Copyright (C) 2019 Yahoo Japan Corporation. All Rights Reserved. 200 250 300 0 50 100 150 200 250 300 Concurrency 30

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

TPC-C Benchmark Specifications • OLTP workload simulating an online-ordering system of a wholesales business. • The benchmark engages 5 transactions (new-order, payment, order-status, delivery, stock-level) on 9 tables, such as placing a new order of a customer, checking a stock-level in a warehouse etc., where each transaction consisting of select, update and insert statements. • The complete benchmark specification is available from here: http://www.tpc.org/tpc_documents_current_versions/pdf/tpc-c_v5.11.0.pdf Copyright (C) 2019 Yahoo Japan Corporation. All Rights Reserved. 32

• http://www.tpc.org/tpc_documents_current_versions/pdf/tpc-c_v5.11.0.pdf

TPC-C Transaction Throughput • At lower concurrency(1-40) where lock contentions are less occurring, PMEM achieves a higher throughput than SSD. • As concurrency increases, the throughput of PMEM TPC-C Benchmark (MySQL 8.0.16/InnoDB) 8000 and SSD tends to converge, and they start decreasing beyond 200 concurrency. The performance gain of PMEM at lower concurrency mainly comes from write performance advantage of PMEM, according to the results of oltp_read_only and oltp_write_only. Copyright (C) 2019 Yahoo Japan Corporation. All Rights Reserved. 7000 6000 Transactions/Sec • Buffer Size =120GB 5000 4000 mysql-ssd mysql-pmem 3000 2000 1000 0 0 50 100 150 200 250 300 Concurrency 33

TPC-C CPU Utilization (Concurrency=40) • Lower CPU utilization in SSD due to a small I/O wait (in red). • Considering the fact this benchmark is run on an 80-core machine and the concurrency is 40, ideally speaking 50% of CPU utilization is desired especially in user space. This is nearly achieved with PMEM. CPU Utilization(MySQL8.0.16 InnoDB, PMEM, Concurrency=40) 100 90 90 80 80 70 70 60 st wa sy 40 us CPU Utilization(%) 60 st sy 40 30 30 20 20 10 wa 50 us 10 1 14 27 40 53 66 79 92 105 118 131 144 157 170 183 196 209 222 235 248 261 274 287 300 313 326 339 352 365 378 391 404 417 430 443 456 469 482 495 508 521 534 547 560 573 586 599 612 625 0 Copyright (C) 2019 Yahoo Japan Corporation. All Rights Reserved. 0 1 14 27 40 53 66 79 92 105 118 131 144 157 170 183 196 209 222 235 248 261 274 287 300 313 326 339 352 365 378 391 404 417 430 443 456 469 482 495 508 521 534 547 560 573 586 599 612 625 CPU Utilization(%) CPU Utilization(MySQL8.0.16 InnoDB, SSD, Concurrency=40) 100 50 Buffer Size =120GB 34

TPC-C I/O Statistics (Concurrency=40) • Less than a half of I/O throughput is achieved in SSD, compared to PMEM. • The bandwidth of PMEM (13.9GB/sec for sequential write with 6 PMEM) is not fully utilized. I/O Throughput(MySQL8.0.16 InnoDB, PMEM, Concurrency=40) I/O Throughput(MySQL8.0.16 InnoDB, SSD, Concurrency=40) 900 900 800 800 700 500 MB_read/s 400 MB_wrtn/s 300 600 500 MB_read/s 300 200 200 100 100 1 19 37 55 73 91 109 127 145 163 181 199 217 235 253 271 289 307 325 343 361 379 397 415 433 451 469 487 505 523 541 559 577 595 613 0 Copyright (C) 2019 Yahoo Japan Corporation. All Rights Reserved. MB_wrtn/s 400 0 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 603 617 600 I/O Throughput(MB/sec) 700 I/O Throughput(MB/sec) Buffer Size =120GB 35

TPC-C CPU Utilization (Concurrency=160) CPU utilization is similar in the both cases (i.e. about 75% utilization; 60% user+15% sys), and CPU is not fully utilized even with twice of concurrency workloads are running as the number of cores on the server on persistent memory. CPU Utilization(MySQL8.0.16 InnoDB, PMEM, Concurrency=160) 90 90 80 80 70 70 60 st wa sy 40 us CPU Utilization(%) 100 60 st sy 40 30 20 20 10 10 0 0 Copyright (C) 2019 Yahoo Japan Corporation. All Rights Reserved. wa 50 30 1 14 27 40 53 66 79 92 105 118 131 144 157 170 183 196 209 222 235 248 261 274 287 300 313 326 339 352 365 378 391 404 417 430 443 456 469 482 495 508 521 534 547 560 573 586 599 CPU Utilization(%) CPU Utilization(MySQL8.0.16 InnoDB, SSD, Concurrency=160) 100 50 Buffer Size =120GB us 1 14 27 40 53 66 79 92 105 118 131 144 157 170 183 196 209 222 235 248 261 274 287 300 313 326 339 352 365 378 391 404 417 430 443 456 469 482 495 508 521 534 547 560 573 586 599 • 36

TPC-C I/O Statistics (Concurrency=160) • Less than a half of I/O throughput is achieved in SSD, compared to PMEM. • The bandwidth of PMEM (1.5GB/sec for write) is not fully utilized. • SSDʼs and PMEMʼs I/O throughput does not differ from the case where the concurrency=40. I/O Throughput(MySQL8.0.16 InnoDB, PMEM, Concurrency=160) 900 800 800 700 700 600 600 MB_read/s MB_wrtn/s 400 300 500 MB_read/s 300 200 200 100 100 0 0 Copyright (C) 2019 Yahoo Japan Corporation. All Rights Reserved. MB_wrtn/s 400 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 500 I/O Throughput(MB/sec) 900 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 609 I/O Throughput(MB/sec) I/O Throughput(MySQL8.0.16 InnoDB, SSD, Concurrency=160) Buffer Size =120GB 37

TPC-C Transaction Throughput & Buffer Hit Rate • Lower the buffer hit rate, higher throughput is achieved with PMEM than SSD, especially at lower concurrency. • The performance difference mainly comes from higher write I/O performance of PMEM, as read I/O performance of PMEM has not improved transaction throughput as much as write I/O has done. TPC-C BENCHMARK Throughput (PMEM, MySQL8.0.16 InnoDB) TPC-C BENCHMARK Throughput (SSD, MySQL8.0.16 InnoDB) 8000 8000 7593.96 7606.84 6982.6 7000 7000 6618.42 6162.52 6000 5997.43 5666.2 6608.47 6200.3 5847.61 6140.05 5989.41 6000 5531.16 5422.63 5000 buffer_hit=100% 4000 buffer_hit=67% 3392.26 3128.2 3131.29 3050.19 3000 2943.33 buffer_hit=33% 2935.16 2836.54 buffer_hit=2% Transactions/sec Transactions/sec 5000 4512.49 4102.66 4000 3000 1850.94 2000 1514.59 buffer_hit=100% 3815.92 3747.79 2289.85 2000 4618.46 3608.73 3597.07 3483.07 3361.73 buffer_hit=67% buffer_hit=33% buffer_hit=2% 2569.29 2443.22 2289.3 2522.36 1885.64 1808.75 2462.02 2399.98 2370.28 2361.96 2327.46 2251.6 1873.95 1869.27 1842.89 1877.21 1824.64 1868.28 1271.49 891.95 634.84 452.03 297.49336.89 255.57 144.43 88.79 59.98 1000 732.45 700.92 714.62 719.94 731.16 749.71 736.32 348.96 376.96 332.79 359.7 348.16 325.78 348.6 0 1000 661.8 289.05 269.23 280.82 60.43 0 0 50 100 150 Concurrency Copyright (C) 2019 Yahoo Japan Corporation. All Rights Reserved. 200 250 300 0 50 100 150 200 250 300 Concurrency 38

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

Findings 1. For read-only workload, MySQL/InnoDB can effectively use CPU resource in both SSD and PMEM cases. With a high buffer hit rate and a high concurrency, the workload becomes CPU-bound. 2. For write-only workload, MySQL/InnoDB is not fully using CPU resources in both SSD and PMEM cases. With a high concurrency (i.e. 160), the CPU utilization stays around 50% (with SSD) and 60% (with PMEM). 3. For write-only workload on PMEM, the PMEM bandwidth is not fully utilized, i.e. only 400-600MB/sec out of 13.9 GB/sec (sequential write with 6 PMEM) is used in write-only workload. Copyright (C) 2019 Yahoo Japan Corporation. All Rights Reserved. 40

Findings 4. For read-only workload, higher transaction throughput is only achieved at a low buffer hit rate (e.g. 4%) between SSD and PMEM. 5. For write-only workload, higher transaction throughput is achieved at all buffer hit rates (100%, 70%, 35%, 4%) with PMEM. 6. For TPC-C workload, lower the buffer hit rate, higher transaction throughput is achieved with PMEM, mainly due to higher write I/O performance of PMEM compared to SSD. Copyright (C) 2019 Yahoo Japan Corporation. All Rights Reserved. 41

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