What is mysql actually doing when the main thread state is "making checkpoint"?

Question

I've noticed that mysql (5.0.60) often freezes for up to minutes at a time under load, during which time the server is completely non-responsive. I've been able to isolate this to only happening when innotop shows the main thread state as "making checkpoint".

What is the server actually doing at this point?

Answer 1

This one answers your question and also has some solutions to your problems:

As you might know Innodb storage engines uses Fuzzy Checkpointing technique as part of it recovery strategy. It is very nice approach which means database never needs to “stall” to perform total modified pages flush but instead flushing of dirty pages happens gradually in small chunks so database load is very even.

http://www.mysqlperformanceblog.com/2006/05/10/innodb-fuzzy-checkpointing-woes/

Answer 2

Checkpointing is when the database server commits all in-memory changes to disk.

Answer 3

I thought I'd expand on Pradeep's comment:

InnoDB's (default) fuzzy checkpointing can get behind on flushing dirty pages to disk very easily*. This can create a problem when the end of a log file is approached, and a checkpoint is forced.

This is a well known InnoDB problem - and there are third party patches available to help. In XtraDB, innodb_adaptive_checkpoint speeds up the page flushing as the end becomes nearer:

http://www.percona.com/docs/wiki/percona-xtradb%3Apatch%3Ainnodb%5Fio

• The explanation why is a little more complex. The main thread has some hardcoded constants to determine if the server is to "busy" to flush pages, such as if more than 100 IO operations/second have already occurred ("IOPS"). 100 IOPS is of course about the number of operations a single 7200RPM disk can complete, and might not make sense if your server has RAID, and multiple disks. XtraDB also has the option for describing innodb_io_capacity.

A similar patch has been released by InnoBase in the 1.0.4 Plugin (not in an official MySQL release yet).