Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have few thousand small databases with total .ibd files of about 800000.

We noticed that MySQL takes couple of minutes to stop but about two hours to start.

For e.g. while MySQL is starting and it's process ID is 26980 and if we look at following command we can see MySQL scanning through all database tables alphabetically.

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

In first few minutes speed of mysql is about 2 databases per second but after about an hours speed gradually drops to 1 database per 10 seconds, because it keeps getting slower, maybe there is some kind of cache that has exhausted.

My questions is, What variable(s) to look for to maintain MySQL speed of scanning through all database tables while it starts?

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

Update 1

We copied all data files to an testing server and installed Percona Server 56-5.6.15 in place of Percona Cluster. Server start time is normal, it is starting in few minutes whereas all nodes of our Cluster takes about 2 hours to start.

We tried starting cluster in an isolated mode where it does not connect with other nodes in cluster. Startup time is still 2 hours.

Update 2

We installed Percona Cluster on a testing server with same data. It worked normally and server started in few minutes even though there were same number of databases. Testing server is a small VPS whereas main server is huge. We copied my.cnf from main server to VPS to ensure all variables same.

After Update 2, we have no idea why all our nodes are behaving like this.

share|improve this question
    
Post in Percona : percona.com/forums/questions-discussions/… –  Jai Mar 5 at 10:42
    
What version of Percona Server are you using ? –  RolandoMySQLDBA Mar 5 at 14:11
    
We are using Percona Cluster 56-5.6.15-25.4.731 –  Jai Mar 6 at 1:19

2 Answers 2

SPEEDING UP SHUTDOWN / STARTUP

Since you are dealing with the InnoDB Storage Engine, you need to think about what MySQL does during a startup and during a shutdown.

During a shutdown, InnoDB tends to freeze everything in terms of transactions Dirty pages in the InnoDB Buffer Pool are flushed. During a startup, InnoDB crash recovery rolls forward any leftover changes in the transaction logs. The InnoDB Double Write Buffer may also be scanned during InnoDB crash recovery.

The first variable I think you should set innodb_fast_shutdown to 0.

Please note the MySQL Documentation:

The InnoDB shutdown mode. If the value is 0, InnoDB does a slow shutdown, a full purge and an insert buffer merge before shutting down. If the value is 1 (the default), InnoDB skips these operations at shutdown, a process known as a fast shutdown. If the value is 2, InnoDB flushes its logs and shuts down cold, as if MySQL had crashed; no committed transactions are lost, but the crash recovery operation makes the next startup take longer.

The slow shutdown can take minutes, or even hours in extreme cases where substantial amounts of data are still buffered. Use the slow shutdown technique before upgrading or downgrading between MySQL major releases, so that all data files are fully prepared in case the upgrade process updates the file format.

Use innodb_fast_shutdown=2 in emergency or troubleshooting situations, to get the absolute fastest shutdown if data is at risk of corruption.

OK that takes care of a faster startup. The tradeoff is longer shutdown.

Is there anything you can do speed up a shutdown ? Yes !!!

Get dirty pages from the InnoDB Buffer Pool written to disk more frequently. How ?

You need to set innodb_max_dirty_pages_pct = 0. By doing this, the shutdown process takes less time to flush dirty pages to the .ibd files.

Is there anything else to help startup ? Yes, there is one more.

When you startup mysql, usually the InnoDB Buffer Pool is empty after its allocation.

You could make mysql save the block numbers of all 16KB pages that were in the the buffer pool. Just add innodb_buffer_pool_load_at_startup and innodb_buffer_pool_dump_at_shutdown. This will not time a long time since it writes block numbers to a binary files, not the blocks themselves.

LARGER NUMBER OF FILES

InnoDB's default for open files (innodb_open_files) is 300 for MySQL 5.5. For MySQL 5.6, the ideal number is set for you. You may need to increase this value as well. InnoDB may cache the open files once you reach this limit.

You may have to compensate by raising the value to 100000 or 200000. You could also increase the ULIMIT in the OS to allow MySQL to have more file handles and set open_files_limit to that max value.

I would further suggest increasing RAM in the DB Server.

SUMMARY

Here are the options you need to add

[mysqld]
innodb_fast_shutdown=0
innodb_max_dirty_pages_pct=0
innodb_buffer_pool_load_at_startup=1
innodb_buffer_pool_dump_at_shutdown=1
innodb_open_files=(much higher value)
open_files_limit=(much higher value)

Depending on the environment, you may not need all four options. Perhaps setting innodb_fast_shutdown to 2 is better than 0. Maybe the default setting for innodb_max_dirty_pages_pct is fine if you have low-write activity. In any case, test which options help your shutdown and startup concerns.

Keep in mind that innodb_buffer_pool_load_at_startup and innodb_buffer_pool_dump_at_shutdown are for MySQL 5.6. Therefore, check to see if the version of Percona Server you are using has those two options.

Give it a Try !!!

share|improve this answer
    
innodb_fast_shutdown is already zero. We don't have problem with shutdown but only on startup. open_files_limit is very high. Changing innodb_open_files does not make a difference on startup. Current value is 60000. We have tried making it 300 and raising it to 200000. It does not seems to change startup time of database. –  Jai Mar 6 at 1:24
up vote 0 down vote accepted

Problem was open_files_limit

open_files_limit = 1048570

After removing this line, startup time reduced from about 2 hours to only 1.5 minutes. It is surprising that default value server is talking is actually a lot higher than what was given.

mysql> show global variables like 'open_files_limit';
+------------------+---------+
| Variable_name    | Value   |
+------------------+---------+
| open_files_limit | 3145728 |
+------------------+---------+
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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