2

For a certain project, it seems like the command use <database>; takes an extraordinarily long time (often a minute or more). (This seems to be the case across a couple of different instances on different versions of MySQL (5.3 and 5.5, I think)). I understand that this is caused by the Tab complete functionality, and I am aware that I could just use a -A flag, but I am interested in the cause of this delay.

My first guess was that this has to do with the speed of INFORMATION_SCHEMA, and that the sheer number of columns caused a delay, but at 4.7k columns (580 tables at ~8 columns a table), that seems a bit of a stretch. There are 3.6k indexes, but that does not strike me as a likely cause of the issue.

  • Is your server busy? – tadman Apr 30 '14 at 15:28
  • 1
    @tadman No. This happens on dev. boxes, so generally one user. – cwallenpoole Apr 30 '14 at 15:36
  • @tadman Can you put that into an answer? The second connection took probably 1/4 the time of the first. – cwallenpoole Apr 30 '14 at 16:00
2

You've pretty much hit the nail on the head.

If the use command results in a change of default database, then the MySQL client branches into build_completion_hash().

Provided that the client was not started with the -A option, that function:

  1. hashes all SQL commands;

  2. executes show databases and hashes the results;

  3. executes show tables and hashes the results; and

  4. for each table, calls mysql_list_fields() and hashes the results.

So, in your case, it is making 582 separate database requests (one for each of steps 2 and 3; and 580 for step 4), looping over the results of each one. Ouch.

Of course, it'd have been imminently more sensible to do (or at least first attempt to do) a single SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() but ours is not to ponder why…

| improve this answer | |
1

There's a few possibilities I can think of:

  • Loading the auto-complete data takes a really long time because you've got a schema that a very large number of tables and columns. Transmitting this isn't always very efficient, so as you note, -A might help. Is it slow connecting to the main mysql database?

  • Otherwise maybe MySQL was at rest and needs a moment to spin up and get ready for your requests, though this should mean a second connection is much faster. Once the caches are warmed up, which includes the OS-level disk cache, it should perform better. On a lightly-loaded system this will often be the case.

| improve this answer | |
  • In this case it was the latter. My guess is that use <db>; is triggering a complete rebuild of some indexes. Odd. – cwallenpoole Apr 30 '14 at 16:14
  • That shouldn't rebuild anything, though I'd keep an eye out for any unusual activity in SHOW PROCESSLIST. Maybe it's just having to really work to get going, as your filesystem might be cold. – tadman Apr 30 '14 at 18:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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