Periodic aria checkpoints prevent power management

[Philip Stoev]

A customer reports that even in the absence of any workload, there are periodic writes to the Aria log file, which in turn prevents his laptop from sleeping.

It seems possible to not do a checkpoint if no queries have been executed since previous checkpoint.

[Michael Widenius (monty)]

Having aria doing checkpoints should not stop a laptop from sleeping.
A 'sleep' will suspend all processes as first part of the sleep so mysqld should be doing nothing.
I have a Lenovo laptop running OpenSuse 11.3 and there is no problem with putting it to sleep even if mysqld is running (I am doing that frequently).

Here is how the checkpoint works:

repeat
 Sleep 'checkpoint-time' seconds.
 If enough new data in transaction log file
   Do checkpoint

In other words, if there is nothing happening on the laptop, there is just a very short check once in a while if there is something that should be done (which is the feature that was requested).

InnoDB is doing something similar and there are dozen of processes in Linux that checks things once in a while (For example any 'clock' or process watching application will wake up once a second/minute but will not stop a laptop from sleeping).

Summary: I don't know of anything special in Aria checkpoint that would interfere with laptop sleep and I don't know of any way to test/prove that.

[Philip Stoev (pstoev-askmonty)]

Maybe the original author of the bug report was refering not to a system-wide sleep, but to the HDD not being able to spin down due to some periodic HDD activity that was going on.

[Michael Widenius (monty)]

There should not be any spin up because of checkpoints in Aria.
If there is no write transactions in Aria, there is no checkpoint written to disk.

I did very this on my machine and noticed that it works as expected on 5.1 but on 5.2 there was a bug in the test
if changes has happened and the aria_log files was touched. As they where just touched and not getting larger I had not noticed this before.

Will now fix this in 5.2 and above.