MySQL Must Be Able to Auto-optimize Itself

Note: The below is a rant – we just wanted to let you know beforehand in case you’re not in the mood for a rant on a Friday afternoon.

One of the things that we do when optimizing a large Joomla website is tweaking MySQL’s settings in the my.cnf configuration file (this file is typically located under the /etc folder of the server). The most important globals that we tweak in that file are tmp_table_size, max_heap_table_size, key_buffer_size, and myisam_sort_buffer_size.

Every time we do these tweaks, we can’t help but wonder how come MySQL doesn’t automatically guess what the optimal settings should be depending on the usage and automatically set them (or at least recommend them – by the way, and for the records, phpMyAdmin does make some recommendations, but these recommendations are often ambiguous especially for those with a humble MySQL experience).

For example, the key_buffer_size can be calculated by issuing the following command in the Linux shell (as root):

du -sch /var/lib/mysql/*/*.MYI | grep "total"

The key_buffer_size should be slightly above the total number calculated from the above command (it’s 396M on the server that we are currently working on).

How come MySQL doesn’t automatically configure (or, again, recommend) the exact number? Of course, this issue is not only about the key_buffer_size, but it is also about nearly every global setting in the my.cnf file. We know that MySQL is able to determine the right settings much more accurately than a programmer/system administrator who reaches the right (but never the ideal) combination through the tricky path of trials and errors.

The first thing any programmer learns is to progressively make things easier for the other programmers. While MySQL is an excellent database, it has a lot of potential to make many things easier for all the system administrators (and the programmers) in the world, but, for some reason, it stops short of that.

We have no idea why they do that. If they’re afraid that companies will go after them if their websites fail because of the automatic (or the recommended) settings then they’re wrong and they know that. Many products out there have recommended settings (even cars have automatic/recommended settings – and cars mean lives – literally) and they’re not afraid of a customer backlash if an automatic/recommended setting fail. We’re just not sure why. But what we are sure of is that MySQL is able to know what the optimal settings should be for each server based on the usage.

If you’re looking into optimizing the MySQL server for your Joomla website, then please contact us. We have optimized the MySQL database server for many large sites and we are definitely able to optimize yours. Please note that our super affordable fees apply!

No comments yet.

Leave a comment