MyISAM – Performance Implications From The Meltdown Fix

RevengeFNF · February 15, 2018

Quote
MyISAM and KPTI – Performance Implications From The Meltdown Fix

Recently we had a report from a user who had seen a stunning 90% performance regression after upgrading his server to a Linux kernel with KPTI (kernel page-table isolation – a remedy for the Meltdown vulnerability).

A big deal of those 90% was caused by running in an old version of VMware which doesn’t pass the PCID and INVPCID capabilities of the CPU to the guest. But I could reproduce a regression around 40% even on bare metal.

This is the test case:
CREATE TABLE t1 (c1 INT PRIMARY KEY AUTO_INCREMENT, c2 INT) ENGINE=MyISAM;
INSERT INTO t1 (c2) VALUES (FLOOR(1000*RAND()));
INSERT INTO t1 (c2) SELECT FLOOR(1000*RAND()) FROM t1;
-- repeat last insert until there are at least 1024 rows in t1

SELECT COUNT(*) FROM t1 AS a JOIN t1 AS b WHERE b.c1>a.c1 AND b.c2<a.c1;
Let’s look at the execution time of the final select for different numbers of rows in t1, under a non-KPTI kernel and under a KPTI kernel.

execution time for the SELECT

Why?

If we look at the handler status variables, we can see that for 8K rows the query does more than 50 million calls to Handler_read_rnd_next. For MyISAM such a handler call results in a call to fget() which in turn results in a __fget syscall.

This is so, because the MyISAM engine does not have a row cache. While it caches index pages in the Key Buffer, there is no such cache for row data. For that it relies on the generic page cache in the operation system. That works pretty well, however since that cache is in the kernel, there is the syscall barrier between the MariaDB server and the cache.

The page table isolation introduced with KPTI increases the cost for a syscall. Hence a workload like the one above, where many MyISAM rows are read in a tight loop, becomes notably slower. The relative slowdown is actually bigger when the row is already in the page cache!

How To Get Around

There is no way to fix that in the MyISAM engine, as it would require a complete redesign. But the good thing is, that most other engines do have a row cache. For InnoDB it is the InnoDB Buffer Pool and for ARIA there is the ARIA Page Cache.

Using a different engine for our table makes the regression go away. Here are numbers for ARIA with the default 128M page cache:

execution time for the SELECT after switching to ARIA

Conclusion

MyISAM and KPTI don’t mix well. If you are running workloads with table scans on MyISAM tables then you might see a rather heavy performance impact from the KPTI fix for Meltdown. Switch to ARIA or InnoDB and make sure that the row cache is big enough.