MySQL pmem

Cost and performance benefits of deploying MySQL Comparing SSD vs. NVMe and Intel® Optane™ DC Persistent Memory

by Emmanuel COLUSSI

The Challenge for this test

Tested different IO configuration (SAS , SSD , PMEM) for Logs and redo log with a MySQL database workload and see the impact on the result.

Why NVMe ?

  • Flash much faster than Spinning Disk, new protocol needed
  • New protocol to access Non-Volatile Memory (NVM)
  • Replaces SCSI (36 yrs old)
  • NVMe on :
    • Host via FC-NVMe, RoCE, iWARP or Infiniband)
    • Fabric via FC-NVMe, RoCE, iWARP, or Infiniband)
    • Array
      • FC-NVMe, RoCe, iWARP, Infiniband
      • SSD (NAND with NVMe interface)
      • PCIe SCM AIC with NVMe interface
  • Massively better parallelization than SCSI
  • 64,000 Queues x 64,000 I/O per Queue
  • Higher IOPS & Lower Latency, leaner protocol
  • Lower CPU Utilization (~20%)

Why Persistent Memory ?


Intel Optane DC persistent memory attaches directly to the CPU via the memory bus and can be directly accessed from the user space without the need for a device driver, system calls, interrupts, or context switches.Applications have the option to fully bypass the operating system and directly access the data on Intel Optane persistent memory with App Direct Mode and a Direct Access (DAX) option within the file systems. DAX-capable file systems are available in Microsoft Windows and Linux operating systems.

PMEM Operational modes :

Persistent memory can be used in a variety of ways to deliver lower latency for many applications:

  • In-memory databases:  In-memory databases can leverage persistent memory’s larger capacities  and significantly reduce restart times. Once the database memory maps  the index, tables, and other files, the data is immediately  accessible. This avoids lengthy startup times where the data is  traditionally read from disk and paged in to memory before it can be  accessed or processed.
  • Fraud detection:  Financial institutions and insurance companies can perform  real-time data analytics on millions of records to detect fraudulent  transactions.
  • Cyber threat analysis: Companies can quickly detect and defend against increasing cyber threats.
  • Web-scale personalization:  Companies can tailor online user experiences by returning relevant  content and advertisements, resulting in higher user click-through  rate and more e-commerce revenue opportunities.
  • Financial trading:  Financial trading applications can rapidly process and execute  financial transactions, allowing them to gain a competitive advantage  and create a higher revenue opportunity.
  • Internet of Things (IoT): Faster data ingest and processing of huge datasets in real-time reduces time to value.
  • Content delivery networks (CDN):  A CDN is a highly distributed network of edge servers strategically  placed across the globe with the purpose of rapidly delivering digital  content to users. With a memory capacity, each CDN node can cache more  data and reduce the total number of servers, while networks can reliably  deliver low-latency data to their clients. If the CDN cache is  persisted, a node can restart with a warm cache and sync only the data  it is missed while it was out of the cluster.

Infrastructure description

The infrastructure of the platform is composed of:

  • 2 CPU Xeon(R) Gold 6254 – 384 GB
  • 12 x PMEM Intel Optane 128 GB distributed evenly among the 12 memory channels
  • 6 x SSD 400 GB
  • 2 x NVMe disk Ultrastor DS SN200 – 800 GB
  • 4 x NVMe disk Intel Optane SSD DC P4800X 375 GB
  • RedHat Enterprise server 8.2
  • MySQL 8.0 - InnoDB storage engine
  • TPC-H Tools v3.0.0

Workloads :

My choice of workload to put our system to the test is a 300GB data warehouse database. We constructed our database based on the TPC-H schema. The TPC-H benchmark models a decision-support system for worldwide sales and distribution of arbitrary products. It provides guidelines to define size of business (i.e., scale factor), which in turn dictates the total size of the data. The size of the database we chose, 300GB. There are nearly 1.7 billion rows in the fact table (i.e., LINEITEM) representing orders data .

The Workload testing used a workload derived from the TPC-H Benchmark and as such is not comparable to published TPC-H results. It takes the system to process a query set from Q1 to Q9

Results

Run TPC-H Analytical queries : Q1 to Q9 on SSD Configuration :

Run TPC-H Analytical queries : Q1 to Q9 on NVMe Configuration :

Run TPC-H Analytical queries : Q1 to Q9 on PMEM Configuration

Results PMEM Gain over NVMe and SSD Storage

We have a gain of around 68% between SSD and NVMe storage. We have a gain of around 48% between NVMe and PMEM storage. We have a gain of around 82% between SSD and PMEM storage.

Conclusion

Intel® Optane™ DC Persistent Memory and NVMe Storage are benefits for Database workloads.

  • Accelerate log writes for faster application performance
  • Reduce software licensing costs through fewer cores
  • Fast caching tier where writes hit the low-latency DRAM
  • Better CPU use through byte-addressable storage
  • Faster checkpoints and restores

The testing has demonstrated the performance benefits of configuring Intel® Optane™ DC Persistent Memory for MySQL 8.0 of the log enhancements.

The availability of Intel® Optane™ DC Persistent Memory 128GB modules improves MySQL 8.0 Database performance by combining the speed of memory access time with the data persistence of storage.

To better optimize performance, you need to know the IO disk profile of the workloads

Resources

TPC-H

TPC-H Tools

InnoDB Architecture

Intel® Optane™ DC Persistent Memory