Improve MySQL start time

MySQL & MariaDB Percona Server for MySQL 5.6
1

A simple MySQL restart is taking too much time. No, it is not doing recovery.

We have tried over 10 restarts one after other.

MySQL stop takes short time but start takes very long time.

We noticed that it is spending too much time reading ibd files.

watch -n 1 'ls -l /proc/26980/fd | grep .ibd'

Above command shows status of MySQL reading .ibd files.
We noted that for first few minutes, MySQL was very fast but then it started becoming slow and slower.
We have few thousand small databases with total .ibd files about 800000.

It takes 2.5 hours to start MySQL and it is not recovery.

Is there any way we could speed this up?

PS : We never noticed this with 5.5. This problem is after we upgraded 5.6.

2

Anyone with any suggestion or comment?

3

In our multiple tests.

In first minutes speed of mysql is about 2 databases per second but after about an hours speed gradually drops to 1 database per 10 seconds.

We tried playing with table_open_cache, table_definition_cache, tmp_table_size but it does not seems to affect speed.

Anyone have any idea about what variable(s) to look to maintain mysql speed of scanning through all database tables while it starts?

4

I have faced this issue on Amazon long ago, as it used to start MySQL after going through each and every database on the server. What platform are you using and how many databases (and how large) you have got on your server?

5

Old thread, but if the engine is InnoDB, I recommend looking into increasing innodb_read_io_threads, innodb_write_io_threads, and innodb_io_capacity.