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.
Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.
Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below.