Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a fairly large database on the Amazon Relational Database Service (RDS) running on MySQL 5.1.57. There are 900+ tables, so that running the "SHOW TABLE STATUS" query can take up to 30 seconds.

We have an integration with the UPS Worldship software connecting through ODBC. It seems that this software running the "SHOW TABLE STATUS" query before every function, such that clicking a button requires a 45 second wait. We had the same database running prior to the new year on dedicated hardware, and we didn't have this problem.

My questions are these:

1.) Is there a way to improve the performance of this query? 2.) What might be causing such a problem?

share|improve this question
add comment

1 Answer

up vote 4 down vote accepted

You may want to disable the variable innodb_stats_on_metadata.

According to the MySQL Documentation on innodb_stats_on_metadata

When this variable is enabled (which is the default, as before the variable was created), InnoDB updates statistics during metadata statements such as SHOW TABLE STATUS or SHOW INDEX, or when accessing the INFORMATION_SCHEMA tables TABLES or STATISTICS. (These updates are similar to what happens for ANALYZE TABLE.) When disabled, InnoDB does not update statistics during these operations. Disabling this variable can improve access speed for schemas that have a large number of tables or indexes. It can also improve the stability of execution plans for queries that involve InnoDB tables.

Since you are using Amazon RDS, ask your DB Administrators to disable it. If your DBs are in a Shared Resource, my condolences...

UPDATE 2012-07-25 14:45 EDT

If you can download the RDS CLI, you can adjust innodb_stats_on_metadata in a customized DB Parameter Group. You will have to do the following:

  • mysqldump data out of RDS Server
  • destroy DB Server Instance
  • create DB Server Instance using your custom DB Parameter Group adjusted for innodb_stats_on_metadata
  • reimport the mysqldump into new DB Instance

Since Amazon RDS does not give away SUPER privilege, that's the only way to change a GLOBAL static option.

UPDATE 2012-07-27 16:34 EDT

Without SUPER Privilege, you can modify a DB Parameter Group for a given MySQL RDS Instance, and launch a reboot with one of the following:

  • rds-reboot-db-instance <DBInstanceName>
  • Click Reboot Button in the RDS Management Console
share|improve this answer
    
I'll be trying your solution later today, but I have to wait for some downtime as this requires a reboot to change the variable on RDS. For reference I'll be updating via the CLI tools: rds-modify-db-parameter-group amreg --parameters "name=innodb_stats_on_metadata, value='0', method=immediate" –  Trey Reynolds Feb 27 '12 at 20:04
    
I tested the disabling the innodb_stats_on_metadata variable and "SHOW TABLE STATUS" went from taking 30-60seconds to around 750ms. Thanks! –  Trey Reynolds Feb 28 '12 at 8:00
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.