Innodb usability and ease of use.Peter Zaitsev
It always surprised me how little Innodb team seems to think about product usability/ease of use, when it comes to settings, performance management etc.
I could understand many things 5 years ago, like a lot of information being available only in hard to parse SHOW INNODB STATUS output or even uglier hacks with creating tables such as innodb_lock_monitor to get more detailed information free space specified in table comments (which need to be parsed) etc. 5 years ago Heikki was along and he had a lot to do to make things work well so a lot of these things were just done quick and dirty way.
It is however hard for me to understand why so many years later with significantly increased team not only many of these things remain unfixed but things are still done similar way ?
Other the years variables like innodb_thread_concurrency were added with rather complicated history of changes for meaning of the values, which seems to now settled to more or less understandable with value 0 meaning disabling Innodb internal thread queuing.
Another one is innodb_flush_logs_at_trx_commit – initially it had only values 0 and 1 which was more or less obvious as 0 typically means “No” and 1 “Yes” in many MySQL options. When Value 2 was added which actually has a meaning between what 0 and 1 mean which is very hard to understand.
Proper way would be of course to use some string values which are more self explanatory so at least you can’t mix what do you currently have set in your my.cnf file – For example using values “none”, “disk”, “os” instead of 0,1,2 will be more explanatory.
Furthermore MySQL even has infrastructure to support both string and integer values, which would allow to preserve compatibility. Look for example on query_cache_type variable which can be set to ON/OFF/DEMAND as well as to 0 and 1.
Interesting enough in some cases Innodb team does get things right. innodb_flush_method variable does not use value 0,1,2,3,4,6 which you have to lookup in the manual each time, but it uses more understandable values such as O_DIRECT, O_DSYNC, fdatasync etc.
However Look at new variable innodb_autoinc_lock_mode freshly added in 5.1.22 (which is what motivated me to write this post) – looking at the manual
we again get values 0,1,2 which have to be translated to “traditional”, “consecutive” and “interleaved” ?
Using string values would make things much more friendly with my.cnf and “show variables” output being more obvious and self documenting.