Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.6, 10.11, 11.4
Description
The function btr_cur_t::search_leaf() that was refactored in MDEV-30400 is executing redundant page lookups when it would suffice to upgrade an already held index root/leaf page latch to exclusive.
monty noticed this while working on MDEV-34571.
I can reproduce the anomaly with the following test case:
--source include/have_innodb.inc
|
|
SET GLOBAL innodb_max_purge_lag_wait=0; |
CREATE TEMPORARY TABLE t1(a SMALLINT PRIMARY KEY) ENGINE=InnoDB; |
BEGIN; |
SET @read0= |
(SELECT CAST(variable_value AS INTEGER) FROM information_schema.global_status |
WHERE variable_name = 'innodb_buffer_pool_read_requests'); |
INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); |
SET @read1= |
(SELECT CAST(variable_value AS INTEGER) FROM information_schema.global_status |
WHERE variable_name = 'innodb_buffer_pool_read_requests'); |
INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19); |
SET @read2= |
(SELECT CAST(variable_value AS INTEGER) FROM information_schema.global_status |
WHERE variable_name = 'innodb_buffer_pool_read_requests'); |
COMMIT; |
|
DROP TABLE t1; |
SELECT @read1-@read0, @read2-@read1; |
Without my fix, the last SELECT would report 33 and 34. With my fix, the numbers would reduce by 10, corresponding to the number of inserted rows. The remaining numbers (23 and 24) roughly correspond to writing one undo log record and one index record for each row.
We could do significantly better if we retained the mini-transaction open between individual row operations (MDEV-16232).
Attachments
Issue Links
- blocks
-
MDEV-34431 More fine grained control of spin loops could be useful
- In Progress
- is blocked by
-
MDEV-34759 buf_page_get_low() is unnecessarily acquiring exclusive latch on secondary index pages
- Closed
- relates to
-
MDEV-16232 Use fewer mini-transactions
- Stalled
-
MDEV-30400 Assertion `height == btr_page_get_level(page_cur_get_page(page_cursor))' failed in btr_cur_search_to_nth_level on INSERT
- Closed
-
MDEV-34571 Add page accessed and pages read from disk to table_stats
- Closed