Sunday, June 11, 2006

Information_schema query taking more than 7 minutes

The biggest current problem that I know in the MySQL servers is the performence of information_schema. This is reported as bug 19588:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1829 to server version: 5.0.22-max-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT TABLE_SCHEMA,
-> sum((DATA_LENGTH + INDEX_LENGTH) / (1024 * 1024)) as size_mb
-> FROM information_schema.TABLES
-> GROUP BY TABLE_SCHEMA
-> HAVING size_mb > 10
-> ORDER BY size_mb DESC;

...

xxx rows in set (7 min 34.71 sec)


Even though this server hosts a lot of data - more than 7 minutes for this query is tough.

1 comment:

Anonymous said...

Yeah its quite painful indeed. This information needs to be cached in a more optimal way. I presume right now its scanning all the .opt and .frm files on the fly? Definately feels like it.