Add the most common parameters used to tune MySQL to the server config screen

Currently the Webmin MySQL Server admin page allows the following MySQL parameters to be tuned and I am adding some of the default values you may want to adjust Based on this official MySQL presentation from 2012 (see http://www.slideshare.net/shinguz/pt-presentation-11465700):

- Key buffer size - Max. packet size: adjust default to 1 GB otherwise restoring a virtual server with a Wordpress DB fails - Sort buffer size: 2 - 16 MB recommended - Network buffer size: adjust default to 1 MB otherwise restoring a virtual server with a - Wordpress DB fails - MyISAM sort buffer size - Tables to cache: 64 to 2048 recommended . I use 512. - Max. number of connections - Query cache size: 32 - 128 MB recommended

Based on this official MySQL presentation from 2012 (see http://www.slideshare.net/shinguz/pt-presentation-11465700) I am summarizing the most important or common variables that need tuning and their value ranges to give you an indication of recommended defaults:

The big three: - innodb_buffer_pool_size: recommended range 80% of RAM. I personally use 40-50%, but it should be set to something big to start with like 25% of RAM. - innodb_log_file_size: 32 - 128 MB recommended - key_buffer_size: 25-33% of RAM recommended (relevant for MyISAM only) The next two most important parameters: - Query Cache is already available in Webmin. See default value above. - thread_cache_size: 8 - 128 MB recommended (this one should not be set too high, like 512 MB) Some additional ones that often need adjusting: - max_heap_table_size: 16 - 256 MB recommended. - tmp_table_size: 32 -512 MB recommended - log_slow_queries: set filename - long_query_time: 1 (Boolean) - log_queries_not_using_indexes: 1 (Boolean)
Status: 
Active

Comments

Virtualmin's post-install wizard can actually set a lot of these already - although it simplifies the choices down to choosing a MySQL "size" based on your requirements. You can see this by forcing the wizard to re-appear by adding the line wizard_run=0 to the end of /etc/webmin/virtual-server/config , then logging out of Virtualmin and logging in again.

Thanks for that tip about the wizard. The wizard is a great idea if it sets the MySQL parameters based on available RAM, diskspace etc. Is this the case?

Tuning MySQL is a tricky business: - in some cases like the innodb_buffer_pool_size and key_buffer_size it is important to increase them to make use of the available RAM

  • but other variable like the table_cache seem to be tricky because there seem to be dependencies on the max. number of file descriptors that can be open in Linux. Does the wizard take this into account?

The default suggestion should be based on the amount of RAM. but file descriptor limits aren't yet taken into account. However, even the "huge" mode in Virtualmin only sets table_cache to 512 .. and from memory, the kernel default is at least 1024.

The wizard is a great idea if it sets the MySQL parameters based on available RAM, diskspace etc. Is this the case?

I don't think that's the case currently, while adding such a feature would be very useful - it would also mean re-inventing the wheel. Given the amount of benchmarking/tuning related discussions, it would probably be better to integrate support for the mysqltuner and apachetuner scripts instead.

These are commonly recommended on the forum, too:

mysqltuner/apachetuner could be run directly as part of the post-install wizard to help come up with sensible default values, but also on-demand in a recurring fashion (performance being usually subject to change/server load).

One could also run these via the apache/mySQL module and show recommended values next to each textbox.

Overall, based on what can be seen in the forum, there seems to be quite some interest in benchmarking/profiling and tweaking running webmin/virtualmin systems, but it would definitely be better to integrate existing tools here, instead of coming up with custom workarounds.

But it's worth keeping in mind that these things are going to change over time, i.e. based on server load - so there's no such thing as a "static" and perfect configuration, it would be better to re-run these diagnostics regularly - either as a cron job, or at least when logging into webmin, and show some hints based on parsing mysqltuner & apachetuner.

Also, any webmin instances running inside a VPS (i.e. VM/OpenVZ containers) should probably run such a check by default, i.e. a daily/weekly cron job, simply because on a VPS, resources are much more likely to change (allocated RAM/CPU allowances).

Just my 2c...

The wizard already looks at available memory to select the MySQL size. It is only run once though, so if your system's RAM changes you would need to adjust the MySQL settings manually (at Webmin -> Servers -> MySQL Database).

The wizard already looks at available memory to select the MySQL size. It is only run once though maybe it would be useful to expose the "post-install wizard" via the webmin menu so that it can be easily run to update things later on - obviously, one could just as well navigate to the corresponding modules directly, but then again those are not integrated with system specifics such as installed RAM - and given the increasing improtance of VPS hosting it isn't all that far-fetched that things like available RAM may change during the lifetime of a server - even without requiring a restart/reboot.

That's a good idea. You can trick Virtualmin into re-showing the wizard by adding the line wizard_run=0 to the end of /etc/webmin/virtual-server/config , but it isn't currently shown in the UI.

Thanks for the hint, very helpful!

Besides, I was looking into wizard-lib.pl and wizard.cgi and was wondering if you guys have any plans to generalize this a bit and make it available as core part of webmin itself?

"wizards" are such a common and useful thing these days, and making the concept of a "wizard" available for use in other webmin modules would mean that people could more easily extend modules with custom wizards (i.e. multi-stage setup steps/pages, along with parameter/input validation for each field and page).

Conceptually, a simple "wizard framework" would just need to encapsulate the concept of having an array with "pages", where each single page may consist of an array of "input fields" with optional validation routines for each field (server-side via Perl or client-side via JavaScript).

So these would be simple linear wizards where navigation is pre-determined - but this would greatly simplify setting up things, especially if this could be made available to use modules.

For example, there was recently a discussion about having a way to easily create webmin/virtual-min modules by wrapping CLI tools - having a "wizard" framework in webmin available, would mean that CLI tools like rsync or Percona's xtrabackup could be integrated by your end-users, just by coming up with a wizard template that declares:

  • software prerequisites (i.e. packages to check/install)
  • pre-validation (i.e. running $TOOL --version and grep to validate the installed binary)
  • post-validation (for sanity checks like running dig to check the TTL of a domain)
  • various wizard pages, mapped to CLI commands with variables

I think the main thing here is the current assumption that the wizard will always be run as a CGI, if this could be generalized, wizards could also be run by the virtualmin CLI tool - i.e. using readline() instead, but also via clustered webmin through RPC.

I really belive that this would be a worthwhile addition, as it would enable you guys to focus on providing lower-level building blocks, while empowering your end-users to easily come up with new wizard-driven modules that are nothing more than wrappers for existing CLI tools.

If done properly, there could be a single framework that works for a main use-cases, i.e. web-based webmin/virtualmin, CLI use, but also clustering and RPC.

Given that I recently had a chance to look at your installer scripts, I am also inclined to believe that there are quite a few opportunities here for improving code sharing/reuse, i.e. by getting rid of "custom" wizards, and instead using a single generic framework.

Basically, we would end up with install scripts that are driven by wizards, that could even work via CLI/RPC

And these wizards could be easily reused, i.e .when cloning/customizing a vhost - simply because there would no longer be hard coded assumptions anymore.

At that point, you could also simply invite your users to contribute "wizards" for tools like apachtuner or mysqltuner, instead of jus filing feature requests here :-)

There are quite a few feature requests that could be solved that way, i.e. think in terms of supporting CloudDNS etc.

What do you think ?

Agreed, a custom wizard framework would be a good idea. Oddly I haven't used wizards much in Webmin/Virtualmin previously, which is likely why this hasn't been done before.

Regarding your suggestion about wrapping shell commands, there is a module for this in Webmin already. It can be found at Webmin -> Others -> Custom Commands, and lets you create buttons with associated input fields that are passed to commands as parameters.

that sounds great, if these two things (custom commands + wizards) could be integrated, this could help clean up and unify lots of existing features, while also paving the way towards more community involvement.

Edit: WOW!!! I just had a brief look at the "custom commands" feature you mentioned, that looks AWESOME - much more flexible than I had anticipated, if this could be extended with some validation hooks it could actually provide the foundation for future wizards - i.e. a wizard could be comprised of pages, where each page would be comprised of steps that are mapped to custom commands - that way, there'd be a maximum degree of code reuse and encapsulation.

In other words, some of the recently discussed feature requests would not need to be implemented as dedicated modules any longer but could be based on wizards driven by a handful of custom fields, and I believe that would be a great opportunity to generalize the underlying infrastructure, i.e. "custom commands" to be powerful and extensible enough to be usable for these purposes.

After some more testing, here's some initial feedback:

  • there's already support for command ordering, but not for ordering parameter fields - that would be good to have
  • having a field to specify an optional help string for each field would make sense
  • optional parameter validation would be useful to have (e.g. eMail, domain, hostname, IP) - that would obviously be a requirement for any GUI/CLI-based wizard
  • support for recursion, i.e. being able to call other custom commands to chain things together, i.e. not just shell commands, but also other custom commands (nested commands)
  • separating the UI part from the implementation, so that the same custom command/wizard can be run via CLI/RPC (this is already done in CGI files apparently)
  • an ability to register wizards in the main menu

Need to play some more with this - but it seems we're almost half way there already, many ideas and feature requests recently discussed could then be tackled by adding wizards that use chained custom commands - including support for xtrabackup, mysqltuner, apachetuner or rsync.