| Bug #38185 | ha_innobase::info can hold locks even when called with HA_STATUS_NO_LOCK | ||
|---|---|---|---|
| Submitted: | 16 Jul 2008 22:03 | Modified: | 20 Jun 2010 17:25 |
| Reporter: | Neel Nadgir | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S5 (Performance) |
| Version: | 5.1.24, 5.1 BZR | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Contribution | ||
[16 Jul 2008 22:03]
Neel Nadgir
[17 Jul 2008 5:13]
Neel Nadgir
Proposed Fix
-------------
=== modified file 'storage/innobase/handler/ha_innodb.cc'
--- storage/innobase/handler/ha_innodb.cc 2008-06-12 00:08:07 +0000
+++ storage/innobase/handler/ha_innodb.cc 2008-07-16 22:13:03 +0000
@@ -5871,9 +5871,13 @@
stats.index_file_length = ((ulonglong)
ib_table->stat_sum_of_other_index_sizes)
* UNIV_PAGE_SIZE;
- stats.delete_length =
- fsp_get_available_space_in_free_extents(
- ib_table->space);
+ if (!(flag & HA_STATUS_NO_LOCK)) {
+ stats.delete_length =
+ fsp_get_available_space_in_free_extents(
+ ib_table->space);
+ } else {
+ stats.delete_length = 0;
+ }
stats.check_time = 0;
===
[17 Jul 2008 12:44]
Sveta Smirnova
Thank you for the report. Verified as described using test, gdb and code analysis. Backtrace for SELECT: #0 fsp_get_available_space_in_free_extents (space=0) at fsp/fsp0fsp.c:2850 #1 0x00305696 in ha_innobase::info (this=0x5fcf028, flag=18) at handler/ha_innodb.cc:5795 #2 0x00146044 in make_join_statistics (join=0x5a3d888, tables=0x80bfc58, conds=0x5a3ea78, keyuse_array=0x5a3e994) at sql_select.cc:2486 #3 0x0014a30a in JOIN::optimize (this=0x5a3d888) at sql_select.cc:945 #4 0x0014db4b in mysql_select (thd=0x5a3b818, rref_pointer_array=0x80bf604, tables=0x80bfc58, wild_num=0, fields=@0x80bf5a0, conds=0x80c0330, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2417248768, result=0x80c0430, unit=0x80bf288, select_lex=0x80bf508) at sql_select.cc:2346 #5 0x00152bdb in handle_select (thd=0x5a3b818, lex=0x80bf228, result=0x80c0430, setup_tables_done_option=0) at sql_select.cc:269 #6 0x000ce237 in execute_sqlcom_select (thd=0x5a3b818, all_tables=0x80bfc58) at sql_parse.cc:4765 #7 0x000cfcd3 in mysql_execute_command (thd=0x5a3b818) at sql_parse.cc:2073 #8 0x00163021 in Prepared_statement::execute (this=0x801c818, expanded_query=0xb0aba838, open_cursor=false) at sql_prepare.cc:3568 #9 0x001632e0 in Prepared_statement::execute_loop (this=0x801c818, expanded_query=0xb0aba838, open_cursor=false, packet=0x54af022 "", packet_end=0x54af028 "") at sql_prepare.cc:3241 #10 0x001637e2 in mysql_stmt_execute (thd=0x5a3b818, packet_arg=0x54af019 "\001", packet_length=15) at sql_prepare.cc:2462 #11 0x000d9c28 in dispatch_command (command=COM_STMT_EXECUTE, thd=0x5a3b818, packet=0x54af019 "\001", packet_length=15) at sql_parse.cc:1092 #12 0x000dafce in do_command (thd=0x5a3b818) at sql_parse.cc:794 #13 0x000c717a in handle_one_connection (arg=0x5a3b818) at sql_connect.cc:1115 #14 0x90024227 in _pthread_body ()
[17 Jul 2008 23:51]
Jeffrey Pugh
Neel also comments that "The regression was introduced in 5.1.24 as part of the fix for Bug#32440"
[18 Jul 2008 6:36]
Vasil Dimov
Proposed fix for this bug (untested)
Attachment: bug38185.diff (application/octet-stream, text), 1.16 KiB.
[18 Jul 2008 6:46]
Vasil Dimov
The proposed fix (by Neel) is safe, setting to R1. I have attached a derived patch here: http://bugs.mysql.com/file.php?id=9807 the difference from Neel's one is that delete_length is not set to 0 if HA_STATUS_NO_LOCK is specified and a comment is added. Please do test this patch but do not commit it directly (we will send a snapshot via the standard procedure once you confirm it fixes the performance problem you have found). If you are in doubt that this thing fixes the problem, you can apply the following patch instead: --- cut --- Index: handler/ha_innodb.cc =================================================================== --- handler/ha_innodb.cc (revision 2541) +++ handler/ha_innodb.cc (working copy) @@ -5832,15 +5832,13 @@ ha_innobase::info( stats.data_file_length = ((ulonglong) ib_table->stat_clustered_index_size) * UNIV_PAGE_SIZE; stats.index_file_length = ((ulonglong) ib_table->stat_sum_of_other_index_sizes) * UNIV_PAGE_SIZE; - stats.delete_length = - fsp_get_available_space_in_free_extents( - ib_table->space) * 1024; + stats.delete_length = 0; stats.check_time = 0; if (stats.records == 0) { stats.mean_rec_length = 0; } else { stats.mean_rec_length = (ulong) (stats.data_file_length / stats.records); --- cut --- it can help isolate the problem.
[18 Jul 2008 6:56]
Vasil Dimov
This is a performance issue, setting to S5. The code that executes is the same on all platforms but (probably?) has visible effects only on Solaris.
[22 Jul 2008 18:00]
Vasil Dimov
Can someone confirm that this patch: http://bugs.mysql.com/file.php?id=9807&text=1 fixes the problem?
[23 Jul 2008 6:50]
Vasil Dimov
The proposed patch (by me or Neel) has the problem that it effectively sets back the behavior to what it was before Bug#32440 was fixed. I.e. with this patch Bug#32440 would resurface. This is because ::info() is also called with HA_STATUS_NO_LOCK when a user issues SELECT * FROM information_schema.tables; I created a new patch that changes this, so HA_STATUS_NO_LOCK is not used when user SELECTs from information_schema.tables. The change is in get_schema_tables_record() which seems to be used only to fill information_schema.tables so there should be no performance degradation. The new patch touches a MySQL file: sql/sql_show.cc. Please test the new patch. PS Thanks to Tim for writing a test for Bug#32440!
[23 Jul 2008 6:50]
Vasil Dimov
New patch
Attachment: bug38185.diff (application/octet-stream, text), 1.41 KiB.
[25 Jul 2008 14:04]
Vasil Dimov
The part of the fix that changes storage/innobase/handler/ha_innodb.cc is committed in the InnoDB 5.1 repository under r2545.
The rest of the fix need to be committed at the MySQL end:
--- cut ---
--- sql/sql_show.cc.orig 2008-07-23 09:32:14.000000000 +0300
+++ sql/sql_show.cc 2008-07-23 09:32:19.000000000 +0300
@@ -3549,8 +3549,7 @@ static int get_schema_tables_record(THD
if(file)
{
- file->info(HA_STATUS_VARIABLE | HA_STATUS_TIME | HA_STATUS_AUTO |
- HA_STATUS_NO_LOCK);
+ file->info(HA_STATUS_VARIABLE | HA_STATUS_TIME | HA_STATUS_AUTO);
enum row_type row_type = file->get_row_type();
switch (row_type) {
case ROW_TYPE_NOT_USED:
--- cut ---
[20 Aug 2008 22:23]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/52096 2715 Timothy Smith 2008-08-20 Cherry-pick some changes from innodb-5.1-ss2545 snapshot. Includes fixes for Bug#37531, Bug#36941, Bug#36941, Bug#36942, Bug#38185. Also include test case from Bug 34300 which was left out from earlier snapshot (5.1-ss2387). Also include fix for Bug #29507, "TRUNCATE shows to many rows effected", since the fix for Bug 37531 depends on it.
[21 Aug 2008 17:59]
Bugs System
Pushed into 5.1.28 (revid:timothy.smith@sun.com-20080820221833-brmru9b77ddt3tfx) (version source revid:azundris@mysql.com-20080821081500-f2d61fh4u61owz3p) (pib:3)
[21 Aug 2008 23:41]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/52267 2794 Timothy Smith 2008-08-21 [merge] Merge up from 5.1-bugteam. Among other things, this applies some changes from the innodb-5.1-ss2545 snapshot into 6.0, fixing Bug#37531, Bug#36941, Bug#36942, and Bug#38185.
[21 Aug 2008 23:43]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/52268 2794 Timothy Smith 2008-08-21 [merge] Merge up from 5.1-bugteam. Among other things, this applies some changes from the innodb-5.1-ss2545 snapshot into 6.0, fixing Bug#37531, Bug#36941, Bug#36942, and Bug#38185.
[27 Aug 2008 1:42]
Paul DuBois
Noted in 5.1.28 changelog. Over-aggressive lock acquisition by InnoDB could result in performance degradation when multiple threads were executing statements on multi-core machines. Setting report to NDI pending push into 6.0.x.
[13 Sep 2008 23:42]
Bugs System
Pushed into 6.0.7-alpha (revid:timothy.smith@sun.com-20080820221833-brmru9b77ddt3tfx) (version source revid:john.embretsen@sun.com-20080808091208-ht48kyzsk7rim74g) (pib:3)
[16 Sep 2008 14:39]
Paul DuBois
Noted in 6.0.7 changelog. Revised changelog entry: Over-aggressive lock acquisition by InnoDB when calculating free space for tablespaces could result in performance degradation when multiple threads were executing statements on multi-core machines.
[5 May 2010 15:25]
Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[6 May 2010 15:57]
Paul DuBois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[28 May 2010 5:58]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:27]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 6:55]
Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[29 May 2010 23:58]
Paul DuBois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[17 Jun 2010 12:01]
Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:42]
Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:28]
Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
