| Bug #68022 | DROP TABLE slow when it decompress compressed-only pages | ||
|---|---|---|---|
| Submitted: | 3 Jan 2013 18:00 | Modified: | 7 Jul 2013 11:28 |
| Reporter: | Mark Callaghan | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB Plugin storage engine | Severity: | S1 (Critical) |
| Version: | 5.1,5.5 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[3 Jan 2013 18:00]
Mark Callaghan
[3 Jan 2013 18:05]
Mark Callaghan
This is part of the problem stack trace. Why are pages getting decompressed given that btr_search_drop_page_hash_when_freed calls buf_page_get_gen with BUF_PEEK_IF_IN_POOL? inflate page_zip_decompress_clust page_zip_decompress buf_zip_decompress buf_page_get_gen btr_search_drop_page_hash_when_freed fseg_free_extent fseg_free_step btr_free_but_not_root buf_page_get_gen returns early for BUF_PEEK_IF_IN_POOL: 1) when the page is not in the buffer pool 2) when a disk read is pending for that page For the case when the page is compressed-only in the buffer pool, it will decompress the page. That can make DROP TABLE very slow. I don't know why it is done. From reading official code for 5.1 and 5.5 I think they do the same, so I don't think this problem is limited to the Facebook patch but I have not tried to reproduce the problem there.
[4 Jan 2013 12:30]
Nizameddin Ordulu
InnoDB goes through the in-memory pages of the to-be-dropped compressed table
in order to remove the table's records from adaptive hash index. While doing so
it decompresses those pages whose uncompressed copy is not in the memory.
This is wasteful and unnecessary because if the uncompressed copy of a page
is not in the memory then its records can not be in AHI. The uncompressed copy
of the page is removed by buf_LRU_free_block() and it already calls
btr_search_drop_page_hash_index() to remove all the records of the page from
AHI.
If a page is compressed-only, then buf_page_get_gen() can not return a buf_block_t*
without doing too much work so I decided to just return NULL. From the perspective
of the caller this should not make any difference because if the page doesn't have
any records in AHI then the caller doesn't do anything with the page.
patch:
diff --git a/storage/innodb_plugin/buf/buf0buf.c b/storage/innodb_plugin/buf/buf0buf.c
index d384f3f..4c127b5 100644
--- a/storage/innodb_plugin/buf/buf0buf.c
+++ b/storage/innodb_plugin/buf/buf0buf.c
@@ -1864,6 +1864,16 @@ loop:
case BUF_BLOCK_ZIP_PAGE:
case BUF_BLOCK_ZIP_DIRTY:
+ if (mode == BUF_PEEK_IF_IN_POOL) {
+ /* Do not decompress a page if the caller is only peeking to
+ * see if the page is in the buffer pool.
+ * In this case, the block object is invalid because
+ * buf_LRU_free_block() does not allocate a buf_block_t object
+ * for the half-freed page. We therefore return NULL.
+ */
+ buf_pool_mutex_exit();
+ return(NULL);
+ }
bpage = &block->page;
/* Protect bpage->buf_fix_count. */
mutex_enter(&buf_pool_zip_mutex);
[20 Apr 2013 13:43]
Laurynas Biveinis
Is the Verified status correct?
(5.1 is not pushed ATM)
5.5$ bzr log -r 4170 -n0
------------------------------------------------------------
revno: 4170 [merge]
committer: Marko Mäkelä <marko.makela@oracle.com>
branch nick: mysql-5.5
timestamp: Mon 2013-01-21 15:19:18 +0200
message:
Merge mysql-5.1 to mysql-5.5.
------------------------------------------------------------
revno: 2661.830.82
committer: Marko Mäkelä <marko.makela@oracle.com>
branch nick: mysql-5.1
timestamp: Mon 2013-01-21 14:59:49 +0200
message:
Bug#16067973 DROP TABLE SLOW WHEN IT DECOMPRESS COMPRESSED-ONLY PAGES
buf_page_get_gen(): Do not attempt to decompress a compressed-only
page when mode == BUF_PEEK_IF_IN_POOL. This mode is only being used by
btr_search_drop_page_hash_when_freed(). There cannot be any adaptive
hash index pointing to a page that does not exist in uncompressed
format in the buffer pool.
innodb_buffer_pool_evict_update(): New function for debug builds, to handle
SET GLOBAL innodb_buffer_pool_evicted='uncompressed'
by evicting all uncompressed page frames of compressed tablespaces
from the buffer pool.
rb#1873 approved by Jimmy Yang
[7 Jul 2013 11:28]
James Day
Updated status to closed, here's the missing status update from 29 Jan 2013: ----- Added to changelog for 5.1.69, 5.5.31, 5.6.11: The DROP TABLE statement for a table using compression could be slower than necessary, causing a stall for several seconds. MySQL was unnecessarily decompressing pages in the buffer pool related to the table as part of the DROP operation. -----
