Description:
When there are many tables in a database and each table has numerous fields, executing "SHOW FULL COLUMNS FROM table" under high concurrency results in very poor performance.
How to repeat:
Create 80 tables with the following structure as shown in the table below.
CREATE TABLE sbtest(
id INTEGER NOT NULL AUTO_INCREMENT,
k INTEGER DEFAULT '0' NOT NULL,
c CHAR(120) DEFAULT '' NOT NULL,
pad CHAR(60) DEFAULT '' NOT NULL,
field1 VARCHAR(255),
field2 INT,
field3 VARCHAR(255),
field4 INT,
field5 VARCHAR(255),
field6 INT,
field7 VARCHAR(255),
field8 INT,
field9 VARCHAR(255),
field10 INT,
field11 VARCHAR(255),
field12 INT,
field13 VARCHAR(255),
field14 INT,
field15 VARCHAR(255),
field16 INT,
field17 VARCHAR(255),
field18 INT,
field19 VARCHAR(255),
field20 INT,
field21 VARCHAR(255),
field22 INT,
field23 VARCHAR(255),
field24 INT,
field25 VARCHAR(255),
field26 INT,
field27 VARCHAR(255),
field28 INT,
field29 VARCHAR(255),
field30 INT,
field31 VARCHAR(255),
field32 INT,
field33 VARCHAR(255),
field34 INT,
field35 VARCHAR(255),
field36 INT,
field37 VARCHAR(255),
field38 INT,
field39 VARCHAR(255),
field40 INT,
field41 VARCHAR(255),
field42 INT,
field43 VARCHAR(255),
field44 INT,
field45 VARCHAR(255),
field46 INT,
field47 VARCHAR(255),
field48 INT,
field49 VARCHAR(255),
field50 INT,
field51 VARCHAR(255),
field52 INT,
field53 VARCHAR(255),
field54 INT,
field55 VARCHAR(255),
field56 INT,
field57 VARCHAR(255),
field58 INT,
field59 VARCHAR(255),
field60 INT,
field61 VARCHAR(255),
field62 INT,
field63 VARCHAR(255),
field64 INT,
field65 VARCHAR(255),
field66 INT,
field67 VARCHAR(255),
field68 INT,
field69 VARCHAR(255),
field70 INT,
field71 VARCHAR(255),
field72 INT,
field73 VARCHAR(255),
field74 INT,
field75 VARCHAR(255),
field76 INT,
PRIMARY KEY (id)
) /*! ENGINE = innodb */;
set global slow_query_log = on;
Then execute "show full columns from tablename" with a concurrency of 512.
Check the slow.log
