Improved MariaDB performance using Shannon Systems PCIe and atomic writes

We recently had a chance to test how the Shannon Direct-IO PCIe Flash G3i devices improves the performance of MariaDB. Anybody who cares about I/O performance has noticed that PCIe drives have become readily available in recent years and that they are significantly faster than normal SSD drives connected to the traditional SATA port (not to mention hard disk drives). In addition to being great hardware, the Shannon drives offer an extra boost by having a device driver that supports atomic writes. This means the device guarantees that a write made to it reaches the drive, and thus the MariaDB server does not have to run extra checks like it does on normal hardware.

MariaDB Server has had atomic write support since 2013 and from 10.2 onwards it is enabled by default if MariaDB server detects compatible hardware. We’ve already blogged about benchmarks on atomic write on selected SSD drives in 2013, but some years have passed and it was time to check out the current state-of-the-art hardware situation.

Benchmark setup

In the benchmarks we used MariaDB 10.1 with a 16 GB InnoDB buffer pool, hosting a database consisting of 16 tables with 25 million rows each. The size of the database sums up to about 100GB of data and is much bigger than what could fit into the InnoDB buffer pool, thus ensuring that most data is fetched from the drives and not just from RAM. We ran benchmarks for different amounts of threads to mimic the stress amount that a typical server has. The only variable that we measured was transactions per second as it is the single best overall figure on how fast the database operates.

The benchmarking tool was sysbench 0.5 using the OTLP scripts. The exact command was:

./sysbench -test=lua/oltp.lua --oltp_tables_count=16 --oltp-table-size=25000000 rand-seed=42 --rand-type=uniform --num-threads=$THREADS --oltp-read-only=off --report-interval=5 --mysql-socket=/var/run/mysqld/mysqld.sock --max-time=602 --max-requests=0 --mysql-user=sbtest --mysql-password=salakala --port=9999 --percentile=99 run

Setting the baseline with normal solid state drives

To establish the baseline we measured how MariaDB server performed using traditional but good Samsung PRO SSD. On this system transactions peaked at around 700 per second.

Note that this type of curve that pans out is typical for MariaDB/MySQL benchmarks and is due to how the server behaves under load using concurrent threads and internal locks.

PCIe hard drive without and with atomic writes

The first benchmark on the PCIe drive was exactly the same as on the SSD, but this time the MariaDB data directory was placed on the Shannon Systems PCIe drive and symlinked to from /var/lib/mysql. With the PCIe hard drive the transaction rate almost doubled and peaked at around 1400 transactions per second.

The second benchmark using the PCIe drive had atomic writes activated by setting innodb_use_atomic_writes = 1 in the my.cnf configuration file. Atomic writes resulted in a significant additional performance increase and transactions  peaked at just below 1700 per second.

In one more additional benchmark we also shut off InnoDB page checksumming with innodb_checksum_algorithm=none. As expected this improved the results slightly more, but doing such a change in production is not recommended.

Full database in RAM

As the last benchmark we even tested mounting all of /var/lib/mysql on tmpfs, so that all of the database was purely in RAM and no hard drive activity was involved in running it. As expected, this did improve performance even more, but surprisingly not too much.

Latency comparison

The latency on PCIe drive is very low, so in addition to the throughput presented above we also plotted the 99% percentile latency, which shows a clear 2x improvement and near RAM level performance.

Latency at approx. 99 percentile	SSD	PCIe	PCIe + atomic writes	No checksums	RAM (tmpfs)
8 threads	34 ms	13 ms	12 ms	13 ms	10 ms

Note that the scale is logarithmic. Also note that there is some anomaly around 16 threads, where it seems that the latency for PCIe performs even better then pure RAM.

Conclusion and thoughts

It seems that running MariaDB on a Shannon PCIe device is almost like having all of the database in RAM. The price of a 1.6 TB or 3.2 TB PCIe hard drive from Shannon Systems if however considerably lower than the equivalent price of ECC protected server RAM chips with the same storage price. Shannon even has PCIe drives with up to 12.8 TB on capacity, so the data density is superior compared to what is possible with RAM based setups.

The conclusion of this benchmark is that running a large MariaDB database with heavy read-write load on Shannon PCIe dirives is indeed a smart move.