6

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 ?

Fabien
  • 161
  • 1
  • 1
  • 4
  • 1
    Based on the information you've provided: who knows? – ceejayoz Oct 15 '12 at 15:37
  • 1
    @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
  • @Fabian I have the same problem: I used to get fast startup with completion and get slow startup unless I turn off auto rehashing (completion). We haven't changed major version (Percona 5.5). – Sam Brightman Feb 09 '15 at 08:49

3 Answers3

3

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.DealerVoiceProviderType  outbound_log.icallclick_alert         outbound_log.transfer_duration
outbound_log.VoipCallStatusType       outbound_log.isService                outbound_log.transfer_ended
outbound_log.VoipTransferStatusType   outbound_log.lead_id                  outbound_log.transfer_ext
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.

RolandoMySQLDBA
  • 16,364
  • 3
  • 47
  • 80
3

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
daemonofchaos
  • 1,201
  • 1
  • 8
  • 10
  • 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
  • This is the same workaround as providing -A on the command line. I don't think this solves the root cause, considering that it used to be fast. – Sam Brightman Feb 09 '15 at 08:47
0

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.

Fabien
  • 161
  • 1
  • 1
  • 4