Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Fix
-
10.1.26, 10.0.32
-
None
-
GNU/Linux Debian 8 Jessie
GNU/Linux Debian 9 stretch
Description
Hello
I have just applied the PTI patch on the OS and it is causing a massive difference in the execution time of a specific query.
Disabling and reenabling PTI enables me to reproduce the difference at will :
PTI disabled : 7s
PTI enabled : 87s
The query :
SELECT * FROM `test` as b1 WHERE b1.`dateFin` > '2018-01-24' AND NOT EXISTS (SELECT 1 FROM `test` as b2 WHERE b2.`ip` = b1.`ip` AND b2.`dateFin` > b1.`dateFin`); |
Explain doesn't help much :
+------+--------------------+-------+------+---------------+------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+-------+------+---------------+------+---------+------+------+-------------+
|
| 1 | PRIMARY | b1 | ALL | NULL | NULL | NULL | NULL | 4499 | Using where |
|
| 2 | DEPENDENT SUBQUERY | b2 | ALL | NULL | NULL | NULL | NULL | 4499 | Using where |
|
+------+--------------------+-------+------+---------------+------+---------+------+------+-------------+
|
Can't see any report matching that kind of difference so far so i'm wondering what's up
Attachments
Issue Links
- relates to
-
-
- Closed
-
Activity
Hi Jean-Marc,
I have found the reason why your case is so heavily impacted by PTI: you are using MyISAM tables and the query reads rows from that table in a tight loop (the subquery).
The MyISAM engine does not have a cache for row data in the database server process, only for indexes. Instead it relies on the operating systems page cache for data reads. This works pretty well, however for the server process this cache is at least one syscall away. And the syscall operation is, what becomes more expensive with the PTI fix in the kernel.
I reproduced the same effect with simple SELECT workload on MyISAM tables. However then the effect wasn't very big, because there is extra overhead for sending the query to the server, parsing it, etc. And this overhead is the same for PTI and non-PTI kernels. But when the row-reads happen in a tight loop like a subquery or join, then the effect becomes quite visible.
There is no solution for the underlying problem - it would require a redesign of the MyISAM engine. But a simple workaround exists: switch to an engine with a row cache. If the table is switched to ARIA or InnoDB, then there is no longer any difference between a PTI and non-PTI system:
| Engine | non-PTI | PTI |
|---|---|---|
| MyISAM | 3.77s | 6.48s |
| ARIA | 0.95s | 0.79s |
| InnoDB | 1.55s | 1.54s |
For that test I used your my.cnf with innodb_buffer_pool_size = 128M. The ARIA engine uses it's own buffer, which size can be configured with aria_pagecache_buffer_size. The default is 128M. If you intend to switch many or even all your MyISAM tables to ARIA, you might need to increase it.
Reading rows from a MyISAM table in a tight loop (subquery or join) emits a massive number of fget() calls, resulting in __fget syscalls. Hence this type of workload gets a significant performance hit from the KPTI workaround for the Meltdown vulnerability.
The workaround for this problem is to switch to an engine with a row cache inside the server, i.e. ARIA or InnoDB.
Hi Jean-Marc,
do you agree that I use this report for a blog post? You can see a preview here:
This is an amazing in-depth analysis !
We did find a workaround in the meantime by reworking the db but i've tried to switch to INNODB and i go from 52sec to 2sec !
You can absolutely use the data i sent you for a post, it's all been anonymized.
Thx ver much for your help !
I reproduced a regression, though not as big as reported.
non-PTI kernel (3.16.0-77): 1403 rows in set (3.74 sec)
PTI-kernel (4.4.0-111): 1403 rows in set (6.50 sec)
-> around 42% slower