October 31, 2014

Time for Zero Administration effort at MySQL ?

Preparing Optimizing MySQL Configuration talk for Percona Live in Washington,DC I noticed how many Variables did MySQL get over years – it is pushing 400 these days even if we do not count variables/options which do not have matching SHOW VARIABLES value, and settings done by changing tables rather than command line options (like much of Performance Schema configuration). Innodb settings alone push 100. True you do not need to set nearly all of them in most cases, in fact I would argue in 95% cases you can get 95% of performance changing just about 10 settings from their defaults, yet so many changes do not help with perceived ease of use.

At the same time however I am happy many more options got exposed over last few years. Majority of options in MySQL have been hard code based on guesses or some experiments done 10+ years ago which means they could be really bad, especially in some cases and having them dynamic was first major step in making sure problems at least can be solved manually.

The next natural step I would like to see taken is having MySQL to chose sensible values for them automatically, based on system scale and workload. Yet I know as it is simple to say but hard to to especially in system like MySQL where a lot of things have been designed to be independent from each other – storage engines do not know about each other or about other components in MySQL which makes it hard to manage memory, a lot of resource allocation done per connection without taking state of system into account. Is not it silly for example MySQL would use the same sort_buffer_size whenever there is only one query running and so there is plenty of resources available or when there is a hundred of sorts are happening at the same time ?

That would be major effort with surely a lot of architecture changes to make it right. Yet I believe this is something MySQL surely needs at this point.

About Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.

Comments

  1. I agree. Also if MySQL could be made ‘learning’ or ‘adaptive’ we could get rid of the parasitic layer of tuning experts and consultants in the MySQL ecosystem (like GuessWho).

    :-)

  2. Peter,

    Jokes aside but this is exactly what we’ve been working on. Percona Toolkit helps to automate a lot of tasks so what people previously needed consultant for they can do themselves, or can hire consultant for a lot fewer hours.

    Yet I do not believe end result is what you describe – We as consultants just can be relieved from doing mundane work which can be easily automated and can focus on creative part of the job. Also technology which is easy (and less expensive) to use has more chances on Market Place. Making sure running MySQL is inexpensive is a great way to make it succeed.

  3. Thanks for understanding it as a joke!

    We are in the same situation and have exactly the same considerations. MONyog does quite a lot of things that could very well become unnecessary and irrelevant if the server was smarter as regards memory handling (adopting to what is possible and/or optimal in a specific environment and learning from its own history). But we also believe that we will still be in business after that. The borderline may move, and we will move – and are moving – along with it just like you.

    And it is not something that is coming tomorrow after all! As you wrote yourself the culprit is that “a lot of things have been designed to be independent from each other”. And with third-party plugin/storage engine -option it will require a complete redefinition of the plugin interface (as far as I can see – I am not well-versed in server code and not a C-programmer actually) so what you write here is definitely non-trivial. It took 5+ years to go from MySQL 5.0 to where we are now and this will also take time. It is also a question if Oracle wants to do it (there are lot of requests and pending tasks and they will have to prioritize.

  4. Zero administration sounds good. Certainly to do that requires several more static config settings to become dynamic. That’s perhaps hard to achieve but does allow us to maintain uptime especially on master servers where this can be more of an issue.

    Settings like innodb_buffer_pool_size is a typical setting which can be problematic, as is enabling or disabling performance_schema dynamically, or changing innodb_buffer_pool_instances. These and a few other settings are usually fine in their default setting, but once you have a problem restarting mysqld is really not the best way of solving the problem: you’d love to be able to change the setting dynamically, even if that is at the expense of a short “lock” while settings get “rearranged”. Much better than restarting mysqld, especially if we are talking about quite a number of mysqld instances which may be affected by the configuration change.

    Another thing that I have come across a few times is memory management. Mysqld assumes you have enough memory for everything. Most other RDBMSes allocate a set amount of memory for the database and then use different parts of that for different purposes. I’d prefer this way of doing things as many settings (such as max_connections) can affect the total memory usage and since you really have to know how all these settings work together it can be tricky knowing how the specific change will affect overall memory usage. This can lead to unexpected swapping of the server, or in the worst case perhaps the Linux kernel’s OOM may get triggered and decide to kill mysqld…

    So I’d like a way of seeing how much memory the different settings will use on the box and if possible perhaps have a comment attached to the setting (as is done with Sybase IIRC) which actually tells you on the box itself what the setting does and how it affects memory usage. That would be one way, another would be a simple script which looks at configuration settings (from my.cnf or SHOW GLOBAL VARIABLES) and just tells you how much memory will be allocated for those settings.

Speak Your Mind

*