Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'd like to know if a 'service mysqld restart' will automatically execute a clean flush tables before restarting the service.

I have Fedora/CentOS with 5.0.77 with mainly MyISAM tables, thousands of them.

share|improve this question
    
Updated my answer for MyISAM –  RolandoMySQLDBA Jul 21 '11 at 15:21

1 Answer 1

up vote 2 down vote accepted

Yes it will. In fact, if you have InnoDB all dirty pages in the InnoDB Buffer Pool are flushed to disk as part of the shutdown process.

If you want to speed up that operation, you should set the following parameter in /etc/my.cnf

[mysqld]
innodb_max_dirty_pages_pct=0

Setting innodb_max_dirty_pages_pct to zero(0) keeps the InnoDB Buffer Pool to the bare minimum of dirty pages but will slightly increase disk I/O by flushing the InnoDB Buffer Pool a little more frequently.

Default for innodb_max_dirty_pages_pct is 75 in MySQL 5.5, and 90 before MySQL 5.5.

Doing this will speed up mysql shutdown since there will be less to flush. This will even be more noticeable in version.

If you want to flush the InnoDB Buffer Pool without 'service mysqld restart' run this

SET GLOBAL innodb_max_dirty_pages_pct = 0;

If replication is involved, STOP SLAVE will automatically be executed as well.

If you have MyISAM, all MyISAM tables will be flushed as well because if a MyISAM table is not flushed (say in the event of a crash), then MyISAM tables that were open at the moment of a crash will still have a nonzero open file count in its headers. This marks a MyISAM table as crashed. You would need to run REPAIR TABLE again such a table.

Same goes with InnoDB if tables did not have committed data written but kept them in the ib_logfiles, thus crash recovery runs in the startup phase of mysqld.

Thus 'service mysqld restart' has to impose FLUSH TABLES for MyISAM and InnoDB.

share|improve this answer
    
Sorry I forgot to tell that I have MyISAM tables. –  Bastien974 Jul 21 '11 at 15:18

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.