[MDEV-22456] Dropping the adaptive hash index may cause DDL to lock up InnoDB - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
Fix Version/s: 10.5.4, 10.2.33, 10.3.24, 10.4.14
Component/s: Storage Engine - InnoDB
Labels:
- performance

Description

If the InnoDB buffer pool contains many pages for a table or index that is being dropped or rebuilt, and if many of such pages are pointed to by the adaptive hash index, dropping the adaptive hash index may consume a lot of time.

The time-consuming operation of dropping the adaptive hash index entries is being executed while the InnoDB data dictionary cache dict_sys is exclusively locked.

(Data file deletion also takes place while the dictionary cache is locked. That is being covered by ~~MDEV-8069~~.)

It is not actually necessary to drop all entries from the adaptive hash index when a table or index is being dropped or rebuilt. We can let the LRU replacement policy of the buffer pool take care of this gradually. For this to work, we must detach the dict_table_t and dict_index_t objects from the main dict_sys cache, and once the last adaptive hash index entry for the detached table is removed (when the garbage page is evicted from the buffer pool) we can free the dict_table_t and dict_index_t object.

Related to this, in ~~MDEV-16283~~, we made ALTER TABLE...DISCARD TABLESPACE skip both the buffer pool eviction and the drop of the adaptive hash index. We shifted the burden to ALTER TABLE...IMPORT TABLESPACE or DROP TABLE. We can remove the eviction from DROP TABLE. We must retain the eviction in the ALTER TABLE...IMPORT TABLESPACE code path, so that in case the discarded table is being re-imported with the same tablespace identifier, the fresh data from the imported tablespace will replace any stale pages in the buffer pool.

Attachments

Issue Links

causes

MDEV-22611 Assertion btr_search_enabled failed during buffer pool resizing

Closed

MDEV-22790 Race between btr_page_mtr_lock() dropping AHI on the same block

Closed

MDEV-23233 Race condition for btr_search_drop_page_hash_index() in buf_page_create()

Closed

MDEV-23332 Assertion failure in file innobase/btr/btr0sea.cc, btr_search_drop_page_hash_index

Closed

MDEV-23370 innodb_fts.innodb_fts_misc failed in buildbot, server crashed in dict_table_autoinc_destroy

Closed

MDEV-23452 Assertion `buf_page_get_io_fix(bpage) == BUF_IO_NONE' failed in buf_page_set_sticky

Closed

MDEV-25527 ERROR: AddressSanitizer: attempting double-free in __interceptor_free nearby dict_mem_table_free

Closed

MDEV-26383 SUMMARY: AddressSanitizer: heap-use-after-free storage/innobase/btr/btr0sea.cc:209 in btr_search_lazy_free

Closed

MDEV-27700 SUMMARY: AddressSanitizer: heap-use-after-free storage/innobase/btr/btr0sea.cc:1305 in btr_search_drop_page_hash_index(buf_block_t*)

Closed

MDEV-29384 Hangs caused by innodb_adaptive_hash_index=ON

Closed

MDEV-35049 fix innodb-adaptive-hash-index scalability with multiple threads

Closed

is blocked by

MDEV-22523 index->rtr_ssn.mutex is wasting memory

Closed

relates to

MDEV-21936 Assertion `!btr_search_own_any(RW_LOCK_S)' failed in btr_search_drop_page_hash_index

Closed

MDEV-23223 If you cancel CTAS Query on MariaDB Server, it will not be canceled and Rollback will be executed

Closed

MDEV-23556 Truncate hangs InnoDB when innodb_safe_truncate is off

Closed

MDEV-24188 Hang in buf_page_create() after reusing a previously freed page

Closed

MDEV-24505 innodb.innodb-ucs2 test failed, assertion: list.count > 0, in file storage/innobase/include/ut0lst.h line 334

Closed

MDEV-24863 InnoDB: Failing assertion: index == cursor->index in btr_search_update_hash_on_delete with innodb_evict_tables_on_commit_debug

Closed

MDEV-8069 DROP or rebuild of a large table may lock up InnoDB

Closed

MDEV-16283 ALTER TABLE...DISCARD TABLESPACE still takes long on a large buffer pool

Closed

MDEV-26599 Assertion `!table->id' failed in btr_search_lazy_free

Open

mentioned in: Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...

(6 causes, 1 is blocked by, 9 relates to, 26 mentioned in)

Activity

Ascending order - Click to sort in descending order

Marko Mäkelä created issue - 2020-05-04 13:28

Marko Mäkelä made changes - 2020-05-04 13:28

Field	Original Value	New Value
Link		This issue relates to ~~MDEV-8069~~ [ ~~MDEV-8069~~ ]

Marko Mäkelä made changes - 2020-05-04 13:28

Link

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

Manjot Singh (Inactive) made changes - 2020-05-04 17:59

Link

This issue causes MDEV-22457 [ MDEV-22457 ]

Marko Mäkelä made changes - 2020-05-08 15:25

Status

Open [ 1 ]

In Progress [ 3 ]

Marko Mäkelä made changes - 2020-05-11 09:19

Link

This issue is blocked by ~~MDEV-22523~~ [ ~~MDEV-22523~~ ]

Marko Mäkelä made changes - 2020-05-14 06:58

Assignee	Marko Mäkelä [ marko ]	Thirunarayanan Balathandayuthapani [ thiru ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Julien Fritsch made changes - 2020-05-14 17:45

Fix Version/s		10.1 [ 16100 ]
Fix Version/s		10.2 [ 14601 ]
Fix Version/s		10.3 [ 22126 ]
Fix Version/s		10.4 [ 22408 ]

Manjot Singh (Inactive) made changes - 2020-05-14 20:04

Link

This issue causes MENT-771 [ MENT-771 ]

Thirunarayanan Balathandayuthapani made changes - 2020-05-15 05:50

Assignee	Thirunarayanan Balathandayuthapani [ thiru ]	Marko Mäkelä [ marko ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Marko Mäkelä made changes - 2020-05-15 11:10

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Marko Mäkelä made changes - 2020-05-15 16:11

issue.field.resolutiondate

2020-05-15 16:11:29.0

2020-05-15 16:11:29.42

Marko Mäkelä made changes - 2020-05-15 16:11

Fix Version/s		10.2.33 [ 24307 ]
Fix Version/s		10.3.24 [ 24306 ]
Fix Version/s		10.4.14 [ 24305 ]
Fix Version/s		10.5.4 [ 24264 ]
Fix Version/s	10.2 [ 14601 ]
Fix Version/s	10.1 [ 16100 ]
Fix Version/s	10.3 [ 22126 ]
Fix Version/s	10.4 [ 22408 ]
Fix Version/s	10.5 [ 23123 ]
Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Marko Mäkelä made changes - 2020-05-18 09:46

Link

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

Marko Mäkelä made changes - 2020-05-18 10:29

Link

This issue causes ~~MDEV-22611~~ [ ~~MDEV-22611~~ ]

Marko Mäkelä made changes - 2020-05-18 10:29

Link

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

Marko Mäkelä made changes - 2020-05-19 10:49

Link

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

Marko Mäkelä made changes - 2020-06-03 17:16

Link

This issue causes ~~MDEV-22790~~ [ ~~MDEV-22790~~ ]

Marko Mäkelä made changes - 2020-07-20 07:05

Link

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

Marko Mäkelä made changes - 2020-07-24 11:16

Link

This issue causes ~~MDEV-23233~~ [ ~~MDEV-23233~~ ]

Thirunarayanan Balathandayuthapani made changes - 2020-07-30 07:55

Link

This issue causes ~~MDEV-23332~~ [ ~~MDEV-23332~~ ]

Marko Mäkelä made changes - 2020-08-14 12:56

Link

This issue causes ~~MDEV-23452~~ [ ~~MDEV-23452~~ ]

Thirunarayanan Balathandayuthapani made changes - 2020-08-24 12:03

Link

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

Marko Mäkelä made changes - 2020-10-22 07:03

Link

This issue causes ~~MDEV-23370~~ [ ~~MDEV-23370~~ ]

Marko Mäkelä made changes - 2020-11-12 10:02

Link

This issue causes ~~MDEV-24188~~ [ ~~MDEV-24188~~ ]

Marko Mäkelä made changes - 2020-11-13 14:10

Link

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

Marko Mäkelä made changes - 2020-11-13 14:10

Link

This issue causes ~~MDEV-24188~~ [ ~~MDEV-24188~~ ]

Marko Mäkelä made changes - 2020-12-31 13:53

Link

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

Marko Mäkelä made changes - 2021-02-15 08:37

Link

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

Marko Mäkelä made changes - 2021-04-27 08:14

Link

This issue causes ~~MDEV-25527~~ [ ~~MDEV-25527~~ ]

Rob Schwyzer (Inactive) made changes - 2021-06-23 20:50

Labels

performance

ServiceNow performance

Rob Schwyzer (Inactive) made changes - 2021-07-02 19:52

Labels

ServiceNow performance

76qDvLB8Gju6Hs7nk3VY3EX42G795W5z performance

Sergei Golubchik made changes - 2021-08-13 22:34

Labels

76qDvLB8Gju6Hs7nk3VY3EX42G795W5z performance

performance

Marko Mäkelä made changes - 2021-08-17 12:23

Link

This issue causes ~~MDEV-26383~~ [ ~~MDEV-26383~~ ]

Marko Mäkelä made changes - 2021-09-13 16:30

Link

This issue relates to MDEV-26599 [ MDEV-26599 ]

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

Workflow

MariaDB v3 [ 108204 ]

MariaDB v4 [ 157717 ]

Rob Schwyzer (Inactive) made changes - 2022-02-23 23:30

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 33203 ]

Rob Schwyzer (Inactive) made changes - 2022-02-24 00:38

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 33214 ]

Rob Schwyzer (Inactive) made changes - 2022-03-02 02:00

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 33203 ]

Rob Schwyzer (Inactive) made changes - 2022-03-02 23:31

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 33236 ]

Rob Schwyzer (Inactive) made changes - 2022-03-03 00:00

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 33245 ]

Rob Schwyzer (Inactive) made changes - 2022-03-10 18:05

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 33236 ]

Marko Mäkelä made changes - 2022-08-02 10:27

Link

This issue causes ~~MDEV-27700~~ [ ~~MDEV-27700~~ ]

Marko Mäkelä made changes - 2022-08-25 11:07

Link

This issue causes ~~MDEV-29384~~ [ ~~MDEV-29384~~ ]

Rob Schwyzer (Inactive) made changes - 2022-11-10 20:10

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 34629 ]

Rob Schwyzer (Inactive) made changes - 2022-11-17 17:54

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 34644 ]

Rob Schwyzer (Inactive) made changes - 2022-11-30 22:57

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 34659 ]

Rob Schwyzer (Inactive) made changes - 2022-12-01 17:49

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 34659 ]

Rob Schwyzer (Inactive) made changes - 2022-12-08 21:02

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 34684 ]

Rob Schwyzer (Inactive) made changes - 2023-01-11 22:54

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 34698 ]

Rob Schwyzer (Inactive) made changes - 2023-01-11 23:23

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 34698 ]

Rob Schwyzer (Inactive) made changes - 2023-06-08 22:26

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 35437 ]

Rob Schwyzer (Inactive) made changes - 2023-07-13 18:36

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 35615 ]

Rob Schwyzer (Inactive) made changes - 2023-07-20 18:33

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 35636 ]

Rob Schwyzer (Inactive) made changes - 2023-09-14 18:53

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 35956 ]

Rob Schwyzer (Inactive) made changes - 2023-09-19 20:37

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 35961 ]

Rob Schwyzer (Inactive) made changes - 2023-09-20 22:27

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 35975 ]

Rob Schwyzer (Inactive) made changes - 2023-09-27 23:11

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 36014 ]

Rob Schwyzer (Inactive) made changes - 2023-10-13 21:06

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 36157 ]

Rob Schwyzer (Inactive) made changes - 2023-10-31 20:23

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 36220 ]

Rob Schwyzer (Inactive) made changes - 2023-11-01 18:41

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 36222 ]

Rob Schwyzer (Inactive) made changes - 2023-11-02 21:37

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 36236 ]

Rob Schwyzer (Inactive) made changes - 2023-12-05 19:16

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 36317 ]

Rob Schwyzer (Inactive) made changes - 2023-12-28 18:13

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 36346 ]

Rob Schwyzer (Inactive) made changes - 2024-03-18 20:44

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 36651 ]

Rob Schwyzer (Inactive) made changes - 2024-03-29 01:19

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 36674 ]

Rob Schwyzer (Inactive) made changes - 2024-04-04 23:04

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 36754 ]

Rob Schwyzer (Inactive) made changes - 2024-04-10 20:16

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 36780 ]

Rob Schwyzer (Inactive) made changes - 2024-04-10 20:30

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 36786 ]

Rob Schwyzer (Inactive) made changes - 2024-04-17 23:11

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 36801 ]

Rob Schwyzer (Inactive) made changes - 2024-05-02 01:31

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 36820 ]

Rob Schwyzer (Inactive) made changes - 2024-05-08 21:11

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 36849 ]

Rob Schwyzer (Inactive) made changes - 2024-05-15 22:32

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 36868 ]

Rob Schwyzer (Inactive) made changes - 2024-05-21 21:22

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 36780 ]

Jira Automation (IT) made changes - 2024-07-04 03:06

Zendesk Related Tickets		201658 196904 184471
Zendesk active tickets		201658

Marko Mäkelä made changes - 2024-11-06 15:56

Link

This issue causes ~~MDEV-35049~~ [ ~~MDEV-35049~~ ]

People

Assignee:: Marko Mäkelä

Reporter:: Marko Mäkelä

Votes:: 1 Vote for this issue

Watchers:: 9 Start watching this issue

Dates

Created:: 2020-05-04 13:28

Updated:: 2024-11-06 15:56

Resolved:: 2020-05-15 16:11

Git Integration

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