MySQL Bugs: #60961: show tables very slow when not in system disk cache

Bug #60961	show tables very slow when not in system disk cache
Submitted:	23 Apr 2011 18:59	Modified:	17 May 2012 5:37
Reporter:	Galt Barber	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.6, 5.5, 5.1	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Contribution, regression, show tables, slow, uncached

Description:
Under mysql 5.5.11, when show tables is executed,
it can take up to 81 seconds to scan a database with
13,406 MyISAM tables.

Under mysql 5.0, this does not happen.
It usually finishes in less than 1 second.

Once these have entered the OS disk cache,
then it is fast.  But if the machine has
been re-booted, or enough time/activity passes
that the list leaves the os disk cache,
then the next user to hit that database
gets a weird long timeout, their cgi page
takes 90 seconds instead of 5 seconds.

Oddly enough, the INFORMATION_SCHEMA is not
affected by disk cache, and although 
information_schema has been drastically improved
by you all (kudos), it's still not as fast as show tables.

When we are waiting 81 seconds, show process list
says "checking permissions", but I don't know
if that can be taken at face value or not.

We only have 3 users in our tables
and the permissions are very simple like
users can access all databases.
We have no column or table priveleges, etc.

We are abandoning attempts to upgrade to 5.5
and will stick with 5.0 or 5.1.

Clearly this is a serious performance bug,
and it is completely unnecessary to be this 
slow, given that all older versions of mysql
worked fine. 

How to repeat:
Use mysql 5.5.11.

Reboot system or clear the system disk cache
(requires root priveleges on linux)
echo 3 > /proc/sys/vm/drop_caches

use <database with many myisam tables>
show tables;
(very slow like 81 seconds for 13000 files)

repeat:
show tables;
(nice and fast while cached)

Restarting myqsl has NO EFFECT on this speed problem.
It is purely an issue of whether these files
are in the system disk cache, and not about mysql's
own disk cache.

This does not happen in mysql 4.0,4.1,5.0,5.1,
so it's something you just broke in 5.5.

This huge uncached-slow-down does not happen
in 5.5.11 for information_schema.

Suggested fix:
Take out whatever you changed to ruin
"show tables" performance.

Otherwise your poor users will resort 
to absurd work-arounds like trying to 
get os directory listings.

Hopefully this is just some bug easily
fixed.  That seems likely given the 
evidence.

Turns out that the problem exists in mysql 5.1.
So here are the versions which exhibit the problem:
5.1 YES
5.5 YES

These do not have the problem.
4.x NO
5.0 NO

More on testing:

After clearing the system disk cache,
we run show tables on a large database.

Since there are 3 files for each table,
it looks like you are opening each file,
and causing the system to read in the first 4k block
from each.

I suggest you stop doing that.
It's pointless and slow and you don't do it for information_schema,
so obviously it's not really necessary.

Even more testing results:

You are reading the entire .frm file for each table
in 5.1 and 5.5 during show tables.

Even though we have no security requiring column-level checks.

My guess is that somehow while goofing around with I_S in 5.1
you accidentally entangled show-tables in your slow .frm fetching.

Although you later fixed I_S in 5.5, you still left show-tables 
crippled by wanting to read each and every .frm file,
even though you don't bother I_S with that overhead.

I have been looking at the source code.

You are checking the .frm file to find out if it is a table or a view.

Well, unless you have specified FULL in your show tables command,
you don't care whether it is a table or a view, and thus it should
not even be checking.  

It used to work in 5.0,
so something has been broken since then in 
checking the FULL flag for SHOW TABLES.

Please fix this soon.
Reading thousands of .frm files for no reason is a big waste.

I see that you ignore temporary files by having a temp-file prefix
for all the names of your temp-tables.

It probably would be a good idea to give all the views a different
prefix too so you can know what they are without having to open 
every single one of them.

You need to add a check for lex->verbose which gets set when FULL is
specified with the "show full tables" command.
(mysql5.0 has this check, but 5.1 on up do not)
If full is not specified, do not do add field[3],
and especially, do not call the dd_frm_type .frm checker.

Note this function is static and only called when 
schema_table_idx == SCH_TABLE_NAMES 
which means the SHOW-TABLES command.

(From sql/sql_show.cc:)
static int fill_schema_table_names(THD *thd, TABLE *table,
                                   LEX_STRING *db_name, LEX_STRING *table_name,
                                   bool with_i_schema)
{
 if (lex->verbose)    // added by me
  {
  if (with_i_schema)
  {
    table->field[3]->store(STRING_WITH_LEN("SYSTEM VIEW"),
                           system_charset_info);
  }
  else
  {
    enum legacy_db_type not_used;
    char path[FN_REFLEN + 1];
    (void) build_table_filename(path, sizeof(path) - 1, db_name->str,
                                table_name->str, reg_ext, 0);
    switch (dd_frm_type(thd, path, &not_used)) {
    case FRMTYPE_ERROR:
      table->field[3]->store(STRING_WITH_LEN("ERROR"),
                             system_charset_info);
      break;
    case FRMTYPE_TABLE:
      table->field[3]->store(STRING_WITH_LEN("BASE TABLE"),
                             system_charset_info);
      break;
    case FRMTYPE_VIEW:
      table->field[3]->store(STRING_WITH_LEN("VIEW"),
                             system_charset_info);
      break;
    default:
      DBUG_ASSERT(0);
    }
    if (thd->is_error() && thd->stmt_da->sql_errno() == ER_NO_SUCH_TABLE)
    {
      thd->clear_error();
      return 0;
    }
  }
 }   // added by me
 if (schema_table_store_record(thd, table))
   return 1;
 return 0;
}

I am trying to add the contribution tag.

Trying to expand the version to show multiple versions affected.

Thank you for the report.

Verified as described.

To repeat:

create database bug60961, then

for i in `seq 1 1 13500`; do ./bin/mysql -uroot -S /tmp/mysql_ssmirnova.sock bug60961 -A -e "create table t_$i(f1 int) engine=myisam;"; done

then

5.0:
$time ./bin/mysql -uroot -S /tmp/mysql_ssmirnova.sock bug60961 -A -e "show tables" >/dev/null

real    0m1.827s
user    0m0.016s
sys     0m0.004s

5.1:
$time ./bin/mysql -uroot -S /tmp/mysql_ssmirnova.sock bug60961 -A -e "show tables" >/dev/null

real    0m8.793s
user    0m0.023s
sys     0m0.004s

5.5.10
$time ./s1 bug60961 -A -e "show tables" >/dev/null

real    0m8.965s
user    0m0.017s
sys     0m0.006s

trunk:
$time ./bin/mysql -uroot -S /tmp/mysql_ssmirnova.sock bug60961 -A -e "show tables" >/dev/null

real    0m8.656s
user    0m0.032s
sys     0m0.005s

Jeez, 141 days with no action is kind of pathetic.
I even gave you the fix, and it's only one line of code:

if (lex->verbose)

Add that to the place noted above and you have one bug fixed.

Noted in 5.1.64, 5.5.25, 5.6.6 changelogs.

SHOW TABLES was very slow unless the required information was already
in the disk cache.

Very cool that you applied the fix!
Now we can use mysql 5.5.

Thanks a bunch!

Thanks for fixing this! It's common for me to have 20+ databases installed at any given time and switching between them was slow. This has saved me a lot of waiting.