How Can ScaleFlux Handle MySQL Workload?

ScaleFlux Handle MySQL Recently I had the opportunity to test a storage device from ScaleFlux called CSD 2000. In this blog post, I will share the results of using it to run MySQL in comparison with an Intel device that had a similar capacity.

First of all, why do we need another storage device? Why is ScaleFlux any different?

The answer is simple; it gives us built-in compression and atomic writes. For many workloads, but especially for database-type workloads, these are very important features.

Because of built-in compression, we can store more data on the ScaleFlux device than on a similar device with the same capacity.

Because of atomic writes, we can disable InnoDB Double Write buffer which means less writes/fsync on the disk layer. This should give us a performance advantage against non-atomic drives.

I ran many different tests on different data sizes, with different schemas.

In these tests, I was not trying to find the performance limits of the cards. I was comparing CSD 2000 against an Intel drive with the same storage capacity. My goal was to see what kind of performance I got from these cards using the same data and workload.

The drives used in these tests were:

ScaleFlux – CSD 2000 4TB
Intel – P4610 3.2TB

Servers used in these tests were:

Application server: Supermicro; SYS-6019U-TN4RT
- 48xIntel(R) Xeon(R) Gold 6126 CPU @ 2.60GHz
- 190G RAM
Database Server: Inspur; SA5212M4
- 32xIntel(R) Xeon(R) CPU E5-2640 v3 @ 2.60GHz
- 64G RAM

On the application server, we ran Sysbench which is a standard tool for MySQL benchmarks. On the database server, Percona Server for MySQL 8.0.19 ran with mostly default settings.

I also disabled binary, slow query logging, and adaptive hash. As you can see I used quite a small InnoDB Buffer Pool. Why? That was on purpose. Here I was comparing storage devices so there is no point having a huge Buffer Pool and serving the queries from memory. I wanted to hit the disk as many times as possible.

The only setting that I changed between the tests is the innodb_doublewrite, and I tested both drives with Double write disabled and enabled.

I have used the following settings:

innodb_buffer_pool_size=8G
innodb_log_file_size = 2G
max_connections=500
slow_query_log=off
disable_log_bin
innodb_doublewrite=ON/OFF
tmpdir = /var/lib/mysql/
innodb_adaptive_hash_index=off
innodb_flush_method=O_DIRECT
innodb_purge_threads=32
sync_binlog=0
max_prepared_stmt_count=4000000

Which Tests Did I Run?

First I ran the standard OLTP read_only, write_only, and read-write tests from Sysbench with the standard schema. Then I made small changes in the schema. I added two additional varchar fields with more realistic text, here is the new schema:

CREATE TABLE `sbtest1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `k` int NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  `data1` varchar(255) DEFAULT NULL,
  `data2` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`),
  KEY `idx_data1` (`data1`)
) ENGINE=InnoDB AUTO_INCREMENT=9999948 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

data1 and data2 are the additional varchar fields, I used books from the Gutenberg project and loaded random lines from a book.

Why did I do that? Because this is where ScaleFlux drive positions itself. They claim if the data is compressible they can outperform the Intel drive. With these modifications the table was much more compressible – the compression ratio was around 2x.

I also added the following queries to the OLTP lua scripts to actually use these fields:

  index_updates = {
      "UPDATE %s%u SET k=?,data1=? WHERE id=?",
      t.INT,{t.CHAR,255},t.INT},

   non_index_updates = {
      "UPDATE %s%u SET c=?,data2=? WHERE id=?",
       {t.CHAR,120},{t.CHAR,255},t.INT},

   inserts = {
      "INSERT INTO %s%u (id, k, c, pad, data1, data2) VALUES (?, ?, ?, ?, ?, ?)",
      t.INT, t.INT, {t.CHAR, 120}, {t.CHAR, 60}, {t.CHAR,255}, {t.CHAR,255}},

   index_selects = {
      "SELECT id,data2 FROM %s%u WHERE data1=?",
      {t.CHAR,255}},

   update_based_on_data1 = {
      "UPDATE %s%u SET data2=? WHERE data1=?",
       {t.CHAR,255},{t.CHAR,255}},

The tests were executed against the following table configurations:

default lua scripts – 100 tables – 10ML rows each – 220G
default lua scripts – 1000 tables – 10ML rows – 2.3T
modified lua scripts – 100 tables – 10ML rows each – 440G
modified lua scripts – 540 tables – 10ML rows each – 2.5T
modified lua scripts – 540 tables – 20ML rows each – 4.7T

But enough talk! Let’s see some graphs…!

I am not going to show all the graphs from all the tests – I will just highlight the most important ones.

Default Sysbench – Read/Write – 220G Datasize

With Default schema and Sysbench we used 100 tables. Each table had 10M rows and the data size was roughly 220G. We can see that MySQL actually performs slightly better on the Intel drive, ScaleFlux only takes the lead at 96 threads. It is important to note that as the ScaleFlux drive is atomic, we disabled InnoDB Double Write Buffer. With the Intel drive, it is not recommended to do that, so we were using Double Write Buffer enabled.

Modified Sysbench – Read/Write – 440G Datasize

In this test, we had the two additional varchar fields, which basically doubled our data size and made the data much more compressible. This is where ScaleFlux claims that their drive should perform better.

As claimed, MySQL clearly performs better on ScaleFlux when the dataset is more compressible. With higher thread numbers this is more obvious. But again, Double Write (aka DW) is disabled for ScaleFlux but not for intel – so let’s see how it looks for write-only workloads, when DW is disabled/enabled for both drives.

In both cases when Double Write is enabled for both drives, or when Double Write is disabled for both drives, MySQL performs better on the ScaleFlux drive. Remember, though, that we wouldn’t want to turn off Double Write when using any drive that doesn’t support Atomic Writes.

Modified Sysbench – Read/Write – 2.5T Datasize

Both drives have 3.2T capacity and we used 2.5T on them. I also did an additional test here. I recreated all the tables with InnoDB table-level compression, as well on the Intel Drive. The graph shows that MySQL performs better on the ScaleFlux drive. One potential explanation could be the page cleaning. In SSD drives there is an internal garbage collector running if the drives reach a certain amount of used capacity.

Because ScaleFlux drive has built-in compression, and the data can compress around 2x, physically it only takes up around 1.4T space on the drive. As that is less than half of the drive capacity garbage collection doesn’t kick in on ScaleFlux drive. The InnoDB table-level compression has a big performance impact compared even to Intel without compression.

Disk Latency

If we look at the disk latency reported by the Operating System for ScaleFlux it is quite steady, but we see a big jump in Intel.

CPU Usage

ScaleFlux – Read/Write – Modified Sysbench – 540 tables – 2.5T

Intel – Read/Write – Modified Sysbench – 540 tables – 2.5T

We also see much higher IOwait with the Intel drive. The server is just wasting resources by waiting for the IO.

Disk Operations

These are the disk operations that the OS reported during the test. We saw more IO operations on the ScaleFlux drive, but because MySQL performs more QPS this is expected. Performing more IOPs means MySQL can actually do more work. It can read and write more. We clearly see this in the next graph.

InnoDB Row Operations

These numbers were collected from InnoDB – how many rows were inserted/updated/deleted per minute.

MySQL is able to perform almost twice as many write operations on the ScaleFlux drive than on the Intel one. As InnoDB does not have to double write the pages twice, it means it can perform more operations.

Conclusion

Based on my tests the ScaleFlux drive has real potential and they do what they claim. With a large compressible data size, it performs really well. However, as we could see in the first test, with a smaller data set that does not compress well, the Intel drive has a clear advantage.

If you would like more information on our testing and the results you can download our new whitepaper here. If anyone is using ScaleFlux drive in production I would like to hear the real-life experiences in the comments.

Also, stay tuned for our next blog post where we show you how can you write more than the Logical Capacity of the drive.

Download “Testing the Value of ScaleFlux Computational Storage Drive (CSD) for MySQL”

by Tibor Korocz via Percona Database Performance Blog

Search This Blog

Small Business News