Bug #13762 SHOW CREATE TABLE slowdown with InnoDB

• Submitted: 5 Oct 2005 7:37
• Modified: 4 Dec 2007 21:17
• Reporter: Hartmut Holzgraefe
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.14
• OS: Linux (linux)
• CPU Architecture: Any

[5 Oct 2005 7:37] Hartmut Holzgraefe

Description:
The execution time for SHOW CREATE TABLE on InnoDB tables rises over time when the table is being used, execution time seems to depend on table contents although it should depend on the table schema only

After creation of a test table SHOW CREATE TABLE returns right away (0.00s),
after inserting some 50.000 rows it already takes about 0.3s to complete.
After a TRUNCATE TABLE it even takes more than 3s to complete although
the table is now empty again.

How to repeat:
see attached files

[5 Oct 2005 14:42] Heikki Tuuri

Hartmut,

this probably is because SHOW CREATE TABLE refreshes table statistics, and does those 8 random dives in every index tree of the table.

Hmm... should we make SHOW CREATE TABLE not to refresh the statistics?

Regards,

Heikki

[5 Oct 2005 14:43] Heikki Tuuri

Hartmut,

TRUNCATE is internally performed as a DELETE FROM in 4.1.You have to wait for purge to clean up if you want the table to be physically empty.

Regards,

Heikki

[9 Nov 2005 20:35] Heikki Tuuri

Changing the category to a 'MySQL Server' bug, since the MySQL server should not ask InnoDB to update stats in SHOW CREATE TABLE.

Regards,

Heikki

[24 Nov 2005 18:54] Sergei Golubchik

Fixed in 5.0.17

[24 Nov 2005 18:56] Sergei Golubchik

Besides, server does not ask InnoDB to *update* stats, InnoDB is misusing HA_STATUS_TIME to update the statistics, while it only means to tell the time the table was updated for the last time, mtime basically.

[2 Dec 2005 20:35] Paul DuBois

Noted in 5.0.17 changelog.

[4 Dec 2007 21:17] Konstantin Osipov

Only S1 bugs are fixed in 4.1, fixed in 5.0