| Bug #69802 | dict_table_schema_check calls dtype_sql_name needlessly - wasting a lot of cpu | ||
|---|---|---|---|
| Submitted: | 20 Jul 2013 11:18 | Modified: | 17 Dec 2013 19:15 |
| Reporter: | Shane Bester (Platinum Quality Contributor) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S5 (Performance) |
| Version: | 5.6.12 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[20 Jul 2013 11:20]
MySQL Verification Team
profile of analyze table
Attachment: bug69802_5.7.2_profile.png (image/png, text), 35.10 KiB.
[5 Oct 2013 18:07]
MySQL Verification Team
fyi this was marked as a duplicate of internal bug Bug 16322739 - ACCESS TO INNODB_TABLE|INDEX_STATS IS SLOW DURING I_S QUERY
[17 Oct 2013 11:07]
MySQL Verification Team
The proposed patch works well in my test. The table is small, and datadir is on a ramdisk. Benchmark for 5000 analyze tables to be run: # unpatched: (49.06 sec) # patched: (21.81 sec)
[17 Dec 2013 19:15]
Daniel Price
Fixed as of 5.6.16, 5.7.4, and here's the changelog entry: "dict_table_schema_check" would call "dtype_sql_name" needlessly. Thank you for the bug report.
[3 Feb 2014 11:02]
Laurynas Biveinis
5.6$ bzr log -r 5671
------------------------------------------------------------
revno: 5671
committer: Vasil Dimov <vasil.dimov@oracle.com>
branch nick: mysql-5.6
timestamp: Fri 2013-12-06 13:49:49 +0200
message:
Backport from mysql-trunk to mysql-5.6:
** revision-id: vasil.dimov@oracle.com-20131203155950-qm0okr731tms81sy
** committer: Vasil Dimov <vasil.dimov@oracle.com>
** branch nick: mysql-trunk
** timestamp: Tue 2013-12-03 17:59:50 +0200
** message:
** Fix Bug#17193801 DICT_TABLE_SCHEMA_CHECK CALLS DTYPE_SQL_NAME
** NEEDLESSLY - WASTING A LOT OF CPU
**
** Avoid the calls to dtype_sql_name() if the results are not going to
** be used (they are used only in an event of an error).
**
** Reviewed by: Kevin (rb:3625)
Description: The verification of internal table structure for persistent stats does too much work. Function dict_table_schema_check prepares a type string by doing this: ... dtype_sql_name((unsigned) req_schema->columns[i].mtype, (unsigned) req_schema->columns[i].prtype_mask, (unsigned) req_schema->columns[i].len, req_type, sizeof(req_type)); dtype_sql_name(table->cols[j].mtype, table->cols[j].prtype, table->cols[j].len, actual_type, sizeof(actual_type)); ... But, the req_type and actual_type strings are only used in the case of an error, which is seldom or never used! How to repeat: Benchmark analyze table with persistent stats, for example: ---------- drop table if exists t1; create table t1( c0 mediumint,c1 mediumint,c2 mediumint,c3 mediumint, c4 mediumint,c5 mediumint,c6 mediumint,c7 mediumint, c8 mediumint,c9 mediumint,ca mediumint,cb mediumint, cc mediumint,key(c0),key(c1),key(c2),key(c3),key(c4), key(c5),key(c6),key(c7),key(c8),key(c9),key(ca),key(cb),key(cc) )engine=innodb STATS_PERSISTENT=1 STATS_AUTO_RECALC=1; insert into t1 values(1,1,1,1,1,1,1,1,1,1,1,1,1); analyze table t1; set global innodb_flush_log_at_trx_commit=0; drop procedure if exists p1; delimiter $ create procedure p1(a bigint unsigned) begin declare i bigint unsigned default 0; repeat analyze table t1; set i:=i+1; until i>a end repeat; end $ delimiter ; call p1(100000); ---------- Suggested fix: Things to consider: o) Don't verify the internal `innodb_table_stats` and `innodb_indexes_stats` tables every time. Do it once. o) Don't construct strings for error messages until they are needed. o) In function dtype_sql_name move the line "ut_snprintf(name, name_sz, "UNKNOWN");" to a default case of the switch statement.