Not the answer you need?
Register and ask your own question!

Improve MySQL start time

jaiguptajaigupta ContributorCurrent User Role Beginner
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.

Comments

  • jaiguptajaigupta Contributor Current User Role Beginner
    Anyone with any suggestion or comment?
  • jaiguptajaigupta Contributor Current User Role Beginner
    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?
  • jagatveerjagatveer Entrant Inactive User Role Beginner
    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?
  • ChaseChase Entrant Legacy User Role Beginner
    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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.