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.

I recently came across a change that Percona Server implemented over normal MySQL InnoDB. The new feature is innodb_fast_checksum

XtraDB can use a more CPU-efficient algorithm, based on 4-byte words, which can be beneficial for some workloads (for instance write-heavy workloads on servers that can perform lots of IO).

Sounds good, but I wonder if there are any workloads in which this change would perform worse than the old method.

The documentation goes on to say:

The original algorithm is checked after the new one, so you can have data pages with old checksums and data pages with new checksums. However in this case, you may experience slow reads from pages having old checksums.

So, assuming that you enable innodb_fast_checksum and rebuild all your InnoDB tables as suggested to remove the old checksums, are there any situations that the extra check on the old checksums would cause performance degradation?

In short, is there any reason not to run innodb_fast_checksum?

share|improve this question

2 Answers 2

up vote 3 down vote accepted

The wording you pasted is very specific:

The original algorithm is checked after the new one, so you can have data pages with old checksums and data pages with new checksums. However in this case, you may experience slow reads from pages having old checksums.

This means that it can still read the old checksum, but from now on in, only Percona Server will be able to read your data. This is later noted here:

Once enabled, turning it off will require table re-creation as well, since it will fail to start on data files created when innodb_fast_checksums was enabled.

You've got to put it in context - InnoDB only verifies the checksums when it reads a page from the block storage device, and updates it before flushing it back to the storage device. While in memory, the checksum is not maintained.

For many years, an IO to a disk has taken something on the order of 5-10ms (1ms = 1/1000th of a second). Computing a checksum probably takes somewhere around 50us (1us = 1/1000000th of a second). I don't have the actual data what it is in InnoDB's case, but if you Google "Numbers everyone should know", you'll hopefully agree I'm correct within an order of magnitude.

Enter an era now where we have things like Fusion-io flash devices, which on paper have a ~20-30us access time, and you can see that reevaluating the checksum makes sense.

My general advice: Don't ruin your backwards compatibility with MySQL releases unless you really need it. Most people do not need it yet.

share|improve this answer
    
Thanks for the insight, Morgan. +1/accepted mainly for the advice about not ruining backwards compatibility, especially if it's not going to help. –  Derek Downey Oct 3 '11 at 12:41

Morgan is correct. Unless you are running an extremely fast device with very low access latency, the I/O access will dwarf the checksum time, and turning on fast checksums will make only a negligible difference.

share|improve this answer
    
thanks for clarifying, @Baron –  Derek Downey Oct 3 '11 at 12:41

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.