I have been battling with some performance issues on one of my Xataface applications of late and I think I just found the cause of periodic slow-downs:
The output of this command includes useful information about all of the tables in the database including such things as creation time, update time, average row length, and number of rows. In Xataface I primarily use this command to:
- Find the modification time of tables so that I can perform smarter caching operations.
- Determine if a view exists.
When working exclusively on MyISAM tables, this command is very fast as all of the information returned is cached all the time. However, when we start to throw InnoDB tables (and possibly views .. haven’t looked into it yet) into the mix this command becomes quite slow because much of the data returned needs to be calculated (e.g. the number of rows). I was facing an issue where this command could take upwards of 10 seconds to return when the application hasn’t been used in a while. It would also periodically hang even when the application was in frequent use. Presumably this is because MySQL does some caching of the values in this command, but the cache doesn’t last long.
In addition, InnoDB doesn’t keep track of modification times so despite the fact that it is performing calculations in this command, it still returns NULL for table update times. Which renders the function altogether useless for InnoDB tables.
Xataface has long had a back-up strategy for keeping modification times in InnoDB tables. It keeps its own table of modification times. This table is updated whenever a record is updated from within Xataface. It doesn’t work for updates performed outside of the application. In most cases this is good enough. Even with this back-up solution, the primary method of retrieving table modification times was still the “SHOW TABLE STATUS” mysql command.
Solution #1: Use the Information Schema
My first attempt to rectify the involved a direct query of the INFORMATION_SCHEMA. You can obtain the modification times of all tables in the database with the following query:
select TABLE_NAME as Name, UPDATE_TIME as Update_time from information_schema.tables where TABLE_SCHEMA='my_database_name'
Unfortunately, it turns out that this query is also quite slow (though a bit faster than show table status). My initial tests showed that on a database with about 60 tables it would take about 0.2 seconds to return. Far to slow for an operation that doesn’t contribute directly to the building of the page. What I need is something that returns in less than 0.01 seconds so that it is effectively negligible.
Solution #2: Use SHOW TABLES
SHOW TABLES simply returns a list of the tables in the current database. It doesn’t include any stats or information about those tables other than the table names. It is also very fast (generally returns in 0.00 seconds …. i.e. too small to matter). This is enough information to build my own modification times or check for the existence of a table/view.
References:
- How can i tell a mysql table was last updated (Stack Overflow) – Includes an example of using the INFORMATION_SCHEMA to get the update time for a table.
- About slow SHOW TABLE STATUS – An issue tracker thread for another project that is talking about the slow performance of SHOW TABLE STATUS when used on InnoDB tables.
- When Does InnoDB Update Table Statistics? (And When It Can Bite) – Talks about InnoDB’s statistics updating. It also mentions the innodb_stats_auto_update flag introduced in MySQL 5.1.17 that can be used to turn off the auto stats calculations for innodb tables.