I have a mysql database that used to work perfectly fine, but now it is dead slow on startup. When I type in

$> mysql -u foo bar I get the following usual message for about 30 seconds before I get a prompt : Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Of course, I tried it and it goes a lot faster :$> mysql -u foo bar -A

But why do I have to wait so long in regular startup ? This is not a very big database (edit : <10 MB), and data does not seem to be corrupted (everything looks fine after startup). I have no other client connecting to the mysql server at the same time (only one process is shown with the command show full processlist) and I have already restarted the mysqld service.

What's going on ?

-

## closed as not a real question by Michael Hampton♦, Magellan, sysadmin1138♦Oct 19 '12 at 19:55

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

Based on the information you've provided: who knows? –  ceejayoz Oct 15 '12 at 15:37
@ceejayoz : what kind of other information should I provide ? The problem is I'm stuck and don't know what to look for. –  Fabien Oct 15 '12 at 15:45
What kind of system is this running on ? what kind of disk subsystem does it have ? is it busy, or heavily loaded ? is the disk almost full ? –  adaptr Oct 15 '12 at 15:51

Add the following to your [mysql] (notice it doesn't end with d) and it will greatly speed up your response time:

no-auto-rehash
-
Would be great if this answer was combined with @RolandoMySQLDBA. This has the "solution", and Roland has all the background info. –  Programster Nov 13 '13 at 12:16

I think the problem stems from the auto-rehash feature

I tried this out today

At the mysql prompt, I typed

mysql> desc outbound_

I hit the tab twice and got the following

mysql> desc outbound_
outbound_call_id                      outbound_log.ext                      outbound_log.template_id
outbound_log                          outbound_log.failed                   outbound_log.transfer_connected
outbound_log.VoipCallStatusType       outbound_log.isService                outbound_log.transfer_ended
outbound_log.call_duration            outbound_log.outbound_log_id          outbound_log.transfer_phone
outbound_log.call_ended               outbound_log.phone                    outbound_log.transfer_started
outbound_log.call_started             outbound_log.postback                 outbound_log.transfer_status
outbound_log.call_type                outbound_log.recording_url            outbound_log.vehicle_id
outbound_log.called                   outbound_log.remote_call_id           outbound_log_id
outbound_log.callnote_synced          outbound_log.sales_id
outbound_log.dealer_id                outbound_log.scheduled
mysql> desc outbound_

Every database and table came up for me to choose

Evidently, mysql client has to read the information_schema database. If your mysql instance contains a lot of InnoDB tables, I can see auto-rehash stalling the mysql client until it can read information_schema database.

-

Thank you all, I found the answer to my problem. One of the table from the database cannot be accessed :

mysql> show columns from foo;
ERROR 1033 (HY000): Incorrect information in file: './db/foo.frm'

The associated foo.frm file cannot be read anymore.

\$> cat foo.frm
cat: foo.frm: input/output error

Disk failure :/ Mysql takes so long on startup because it tries to access data from that file.

-