[MDEV-15072] Massive performance impact after PTI fix - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Won't Fix
Affects Version/s: 10.1.26, 10.0.32
Fix Version/s: N/A
Component/s: Data Manipulation - Subquery, Storage Engine - MyISAM
Labels:
None
Environment:
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

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

test.sql.gz
43 kB
2018-02-01 12:22
test.sql.gz
6 kB
2018-01-26 14:30

Issue Links

relates to

MDEV-15163 Massive performance impact after PTI fix 2

Closed

Activity

Ascending order - Click to sort in descending order

Jean-Marc Messina created issue - 2018-01-25 15:00

Sergei Golubchik made changes - 2018-01-25 16:07

Field	Original Value	New Value
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 `blacklist` as b1 WHERE b1.`dateFin` > '2018-01-24' AND NOT EXISTS (SELECT 1 FROM `blacklist` 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	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 : {code:sql} SELECT * FROM `blacklist` as b1 WHERE b1.`dateFin` > '2018-01-24' AND NOT EXISTS (SELECT 1 FROM `blacklist` as b2 WHERE b2.`ip` = b1.`ip` AND b2.`dateFin` > b1.`dateFin`); {code} Explain doesn't help much : {noformat} +------+--------------------+-------+------+---------------+------+---------+------+------+-------------+ \| 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 \| +------+--------------------+-------+------+---------------+------+---------+------+------+-------------+ {noformat} Can't see any report matching that kind of difference so far so i'm wondering what's up

Jean-Marc Messina made changes - 2018-01-25 17:08

Affects Version/s		10.1.26 [ 22553 ]
Environment	GNU/Linux Debian 8 Jessie	GNU/Linux Debian 8 Jessie GNU/Linux Debian 9 stretch

Axel Schwenke made changes - 2018-01-25 19:53

Assignee

Axel Schwenke [ axel ]

Jean-Marc Messina made changes - 2018-01-26 11:23

Attachment

cossi-ip.sql.gz [ 45016 ]

Jean-Marc Messina made changes - 2018-01-26 13:44

Comment

[ exactly the same output on the execution plan with and without KPTI
Here are the tables (attached to this ticket) if you want to reproduce the problem [^cossi-ip.sql.gz] ]

Jean-Marc Messina made changes - 2018-01-26 13:44

Attachment

cossi-ip.sql.gz [ 45016 ]

Jean-Marc Messina made changes - 2018-01-26 14:26

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 :
{code:sql}
SELECT * FROM `blacklist` as b1 WHERE b1.`dateFin` > '2018-01-24' AND NOT EXISTS (SELECT 1 FROM `blacklist` as b2 WHERE b2.`ip` = b1.`ip` AND b2.`dateFin` > b1.`dateFin`);
{code}
Explain doesn't help much :
{noformat}
+------+--------------------+-------+------+---------------+------+---------+------+------+-------------+
| 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 |
+------+--------------------+-------+------+---------------+------+---------+------+------+-------------+
{noformat}
Can't see any report matching that kind of difference so far so i'm wondering what's up

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 :
{code:sql}
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`);
{code}
Explain doesn't help much :
{noformat}
+------+--------------------+-------+------+---------------+------+---------+------+------+-------------+
| 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 |
+------+--------------------+-------+------+---------------+------+---------+------+------+-------------+
{noformat}
Can't see any report matching that kind of difference so far so i'm wondering what's up

Jean-Marc Messina made changes - 2018-01-26 14:30

Attachment

test.sql.gz [ 45019 ]

Axel Schwenke made changes - 2018-01-26 16:20

issue.field.resolutiondate

2018-01-26 16:20:27.0

2018-01-26 16:20:27.824

Axel Schwenke made changes - 2018-01-26 16:20

Fix Version/s		N/A [ 14700 ]
Resolution		Cannot Reproduce [ 5 ]
Status	Open [ 1 ]	Closed [ 6 ]

Jean-Marc Messina made changes - 2018-02-01 12:22

Attachment

test.sql.gz [ 45129 ]

Jean-Marc Messina made changes - 2018-02-01 12:29

Link

This issue relates to ~~MDEV-15163~~ [ ~~MDEV-15163~~ ]

Axel Schwenke made changes - 2018-02-08 13:47

Resolution	Cannot Reproduce [ 5 ]
Status	Closed [ 6 ]	Stalled [ 10000 ]

Axel Schwenke made changes - 2018-02-08 13:47

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Axel Schwenke made changes - 2018-02-12 12:53

Component/s		Storage Engine - MyISAM [ 10600 ]
Resolution		Won't Fix [ 2 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:46

Workflow

MariaDB v3 [ 85139 ]

MariaDB v4 [ 153651 ]

People

Assignee:: Axel Schwenke

Reporter:: Jean-Marc Messina

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2018-01-25 15:00

Updated:: 2018-02-13 10:14

Resolved:: 2018-02-12 12:53

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration