Details
-
Task
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
Description
The following test will lock 3 records and their preceding gaps inside InnoDB, even though only the 2 first records match the condition id<=20:
--source include/have_innodb.inc
|
CREATE TABLE t1(id INT PRIMARY KEY, c INT NOT NULL) ENGINE=InnoDB; |
INSERT INTO t1 VALUES(10,10),(20,20),(30,30),(40,40); |
SELECT * FROM t1 WHERE id<=20 FOR UPDATE; |
DROP TABLE t1; |
InnoDB cannot be blamed for this, because the SQL layer is not invoking ha_innobase::idx_cond_push(), so prebuilt->idx_cond will be NULL and InnoDB cannot know that the id=30 record does not match the WHERE condition.
To confirm this:
break ha_innobase::create
|
continue
|
finish
|
break lock_rec_lock
|
break ha_innobase::delete_table
|
continue
|
There will be 3 calls to lock_rec_lock() before the DROP TABLE. There should be only 2 calls, with type=LOCK_ORDINARY to lock the keys id=10,id=20 and the gaps preceding them.
Another reason to enable index condition pushdown (ICP) for table scan (or PRIMARY KEY scan) is that if the query would return BLOBs, it would be much more efficient to avoid copying and throwing away the BLOBs of non-matching rows.
Attachments
Issue Links
- relates to
-
MDEV-27999 select~for update skip locked locks unnecessary record
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Open
-
-
PERF-196 Loading...
Activity
In MDEV-28747, oleg.smirnov reported experimenting with this. The MySQL commit message mentions WL#6061, which is hidden from the public view.
The commit also mentions "several of the queries in sysbench". Which queries might those be? Would it be possible to avoid pushing down WHERE conditions that never actually filter anything? Could MDEV-16188 already serve those queries, making the MySQL commit redundant? If not, could removing some InnoDB prefetch logic in MDEV-16232 fix the performance problem?
The code change that was mentioned in a MDEV-28747 comment would correspond to the following patch for 10.5 or later:
diff --git a/sql/opt_index_cond_pushdown.cc b/sql/opt_index_cond_pushdown.cc
|
index 6a24fa95b68..c35345be5d5 100644
|
--- a/sql/opt_index_cond_pushdown.cc
|
+++ b/sql/opt_index_cond_pushdown.cc
|
@@ -340,7 +340,7 @@ void push_index_cond(JOIN_TAB *tab, uint keyno)
|
tab->join->thd->lex->sql_command != SQLCOM_UPDATE_MULTI &&
|
tab->join->thd->lex->sql_command != SQLCOM_DELETE_MULTI &&
|
tab->type != JT_CONST && tab->type != JT_SYSTEM &&
|
- !tab->table->file->is_clustering_key(keyno)) // 6
|
+ true)
|
{
|
DBUG_EXECUTE("where",
|
print_where(tab->select_cond, "full cond", QT_ORDINARY);); |
It makes sense to implement MDEV-16232 first. After that, the only remaining value of this optimization might be that we can avoid fetching BLOBs for rows that do not match the condition.
MDEV-16232 is a rather complex task. I think that this needs to be investigated independently of that.
The index condition pushdown was initially enabled on all indexes in MySQL, but it was disabled on PRIMARY KEY due to a performance regression in sysbench.
Unfortunately, InnoDB does not currently make use of the range limits, so it is unnecessarily locking (and possibly later unlocking) too many records.