MySQL – Start and Stop Procedures

myisamMySQLperformanceshutdown

I'm seeking help to understand what happens when the following command line is executed:

root@prodn$ service mysqld stop

Yes, it shuts down the MySQL server so access to it is no longer available until the service is started again. However, more specifically, is there anything else that happens when the service is stopped? Forgive my novice-ness here but when mysqld is restarts, does it mean that logs were flushed, some memory freed, caches emptied, etc.?

The reason I ask is the following:

Our data warehouse DB is a MySQL DB and in the past 4 months, it has taken on average 8.5 hours.

Last Wednesday, I've stopped the mysql service, and then restarted it after 30 minutes. Since, I begin to notice a massive improvement on overall performance — SELECT/INSERT/UPDATE/DELETE processes were more efficient. DW finished almost 4 hours earlier with the same number of rows of data

However, with each passing day, 15-20 minutes is somehow added to the finish time. So, I suspect I may have to restart the service weekly.

Is there an explanation for this behavior? I don't know what other questions are relevant but it'd be brilliant to know what happens when mysqld service restarts.

Can anyone shed some light on this please?

Best Answer

When you issue service mysql stop, a lot more happens than just cutting off DB Connectivity. The link in the comment from @ethrbunny already explains what things happens.

I would like to focus on one particular aspect: The InnoDB Buffer Pool. InnoDB has to flush the InnoDB Buffer Pool's dirty pages. If you want to know how much, run this before shutdown:

SELECT CONCAT(dpbytes/power(1024,expo),' ',SUBSTR(units,expo*2+1,2)) DirtyPages FROM
(SELECT dpbytes,FLOOR(LOG(dpbytes)/LOG(1024)) expo FROM
(SELECT dirty_pages*page_size dpbytes FROM
(SELECT VARIABLE_VALUE dirty_pages FROM information_schema.global_status
WHERE VARIABLE_NAME='Innodb_buffer_pool_pages_dirty') AAA,
(SELECT VARIABLE_VALUE page_size FROM information_schema.global_status
WHERE VARIABLE_NAME='Innodb_page_size') BBB) AA) A,(SELECT ' BKBMBGBTB' units) B;

This will tell you how much data needs to be flushed from the InnoDB Buffer Pool.

Please keep in mind that InnoDB has many moving parts in the system tablespace (the file ibdata1). Click here to see the Pictorial Representation of the entire InnoDB Infrastructure.

Some of transactional information is written in such a way that Crash Recovery is performed when you run service mysql start.

OPTIONAL

You can get all data flushed and all transaction cleanly committed from ibdata1 and the Transaction Logs (ib_logfile0,ib_logfile1) by running this

SET GLOBAL innodb_fast_shutdown = 0;

before running

service mysql stop

Give it a Try !!!

UPDATE 2013-04-24 07:17 EDT

Since you have MyISAM as the main storage engine, then the only thing happening is the flushing of index changes to all .MYI files that have changes pending.

I would also like recommend that the next you shutdown mysqld, run this beforehand in another session:

tail -f /var/log/mysqld.log

and watch the messages that go by and see it it tells you what mysqld is doing with any storage engine. If you have absolutely no InnoDB tables in use, then you should think about disabling InnoDB with

[mysqld]
skip-innodb

as this will make for a quicker startup and possibly a quicker shutdown.

Related Question