MySQL Bugs: #39509: performance regression of "desc table0" from 5.0.46 -> 5.0.66a

Bug #39509	performance regression of "desc table0" from 5.0.46 -> 5.0.66a
Submitted:	18 Sep 2008 7:41	Modified:	28 Dec 2014 14:31
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S5 (Performance)
Version:	5.0.66a	OS:	Solaris (x86)
Assigned to:		CPU Architecture:	Any

Description:
running a "desc table0" performance noticeably slower on recent versions of mysqld, on solaris.  See the results after 10000 queries:

5.0.46:
-------
>php desc.php
time taken:     305.316596985
average qps:    32.752886999101

>php desc.php
time taken:     293.000993013
average qps:    34.129577163434

5.0.66a:
-------
>php desc.php
time taken:     386.711606979
average qps:    25.859063497266

>php desc.php
time taken:     348.642068148
average qps:    28.682711908865

the problem becomes worse and worse if you add more threads.

How to repeat:
create this table empty, then run the attached php script.

DROP TABLE IF EXISTS `table0`;
CREATE TABLE IF NOT EXISTS `table0`
(`col0` BOOL,
`col1` BOOL,
`col2` TINYINT,
`col3` DATE,
`col4` TIME,
`col5` SET ('test1','test2','test3'),
`col6` TIME,
`col7` TEXT,
`col8` DECIMAL,
`col9` SET ('test1','test2','test3'),
`col10` FLOAT,
`col11` DOUBLE PRECISION,
`col12` ENUM ('test1','test2','test3'),
`col13` TINYBLOB,
`col14` YEAR,
`col15` SET ('test1','test2','test3'),
`col16` NUMERIC,
`col17` NUMERIC,
`col18` BLOB,
`col19` DATETIME,
`col20` DOUBLE PRECISION,
`col21` DECIMAL,
`col22` DATETIME,
`col23` NUMERIC,
`col24` NUMERIC,
`col25` LONGTEXT,
`col26` TINYBLOB,
`col27` TIME,
`col28` TINYBLOB,
`col29` ENUM ('test1','test2','test3'),
`col30` SMALLINT,
`col31` REAL,
`col32` FLOAT,
`col33` CHAR (175),
`col34` TINYTEXT,
`col35` TINYTEXT,
`col36` TINYBLOB,
`col37` TINYBLOB,
`col38` TINYTEXT,
`col39` MEDIUMBLOB,
`col40` TIMESTAMP,
`col41` DOUBLE,
`col42` SMALLINT,
`col43` LONGBLOB,
`col44` VARCHAR (80),
`col45` MEDIUMTEXT,
`col46` NUMERIC,
`col47` BIGINT,
`col48` DATE,
`col49` TINYBLOB,
`col50` DATE,
`col51` BOOL,
`col52` MEDIUMINT,
`col53` FLOAT,
`col54` TINYBLOB,
`col55` LONGTEXT,
`col56` SMALLINT,
`col57` ENUM ('test1','test2','test3'),
`col58` DATETIME,
`col59` MEDIUMTEXT,
`col60` VARCHAR (232),
`col61` NUMERIC,
`col62` YEAR,
`col63` SMALLINT,
`col64` TIMESTAMP,
`col65` BLOB,
`col66` LONGBLOB,
`col67` INT,
`col68` LONGTEXT,
`col69` ENUM ('test1','test2','test3'),
`col70` INT,
`col71` TIME,
`col72` TIMESTAMP,
`col73` TIMESTAMP,
`col74` VARCHAR (170),
`col75` SET ('test1','test2','test3'),
`col76` TINYBLOB,
`col77` BIGINT,
`col78` NUMERIC,
`col79` DATETIME,
`col80` YEAR,
`col81` NUMERIC,
`col82` LONGBLOB,
`col83` TEXT,
`col84` CHAR (83),
`col85` DECIMAL,
`col86` FLOAT,
`col87` INT,
`col88` VARCHAR (145),
`col89` DATE,
`col90` DECIMAL,
`col91` DECIMAL,
`col92` MEDIUMBLOB,
`col93` TIME,
KEY `idx0` (`col69`,`col90`,`col8`),
KEY `idx1` (`col60`),
KEY `idx2` (`col60`,`col70`,`col74`),
KEY `idx3` (`col22`,`col32`,`col72`,`col30`),
KEY `idx4` (`col29`),
KEY `idx5` (`col19`,`col45`(143)),
KEY `idx6` (`col46`,`col48`,`col5`,`col39`(118)),
KEY `idx7` (`col48`,`col61`),
KEY `idx8` (`col93`),
KEY `idx9` (`col31`),
KEY `idx10` (`col30`,`col21`),
KEY `idx11` (`col67`),
KEY `idx12` (`col44`,`col6`,`col8`,`col38`(226)),
KEY `idx13` (`col71`,`col41`,`col15`,`col49`(88)),
KEY `idx14` (`col78`),
KEY `idx15` (`col63`,`col67`,`col64`),
KEY `idx16` (`col17`,`col86`),
KEY `idx17` (`col77`,`col56`,`col10`,`col55`(24)),
KEY `idx18` (`col62`),
KEY `idx19` (`col31`,`col57`,`col56`,`col53`),
KEY `idx20` (`col46`),
KEY `idx21` (`col83`(54)),
KEY `idx22` (`col51`,`col7`(120)),
KEY `idx23` (`col7`(163),`col31`,`col71`,`col14`)
)engine=innodb;

testcase, run and compare 5.0.46 vs 5.0.66a times (try multiple runs of each)

Attachment: desc.php (application/octet-stream, text), 936 bytes.

Thank you for the report.

I can not repeat described behavior with our binaries. Do you have rows in the table or is it empty? Did you compile server yourself? Which configure options did you use?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".