Advice on MySQL (MariaDB) and optimize.

Ok... regarding disabling the daily MySQL optimize I think perhaps the better solution was to edit the file that runs rather than the job....

so I edited the file referenced in the job: 

/etc/cron.daily/mysqloptimize

And commented out the line that runs the command:

from this: 

#!/bin/sh

mysqloptimize --defaults-file=/etc/mysql/debian.cnf --all-databases > /dev/null

to this:

#!/bin/sh

#mysqloptimize --defaults-file=/etc/mysql/debian.cnf --all-databases > /dev/null

Obviously what you've done will be effective, but the convention is to disable a cron.hourly/daily/weekly/monthly job by making it non-executable (only executable scripts within any of those directories run). FWIW, the same is not true of scripts in /etc/cron.d - scripts in that dir must be cron scripts (i.e. explicitly define when they run, rather than just plain shell scripts). /etc/cron.d scripts will run on the schedule defined (within the file) regardless of whether they're executable or not.

So the recommended way to disable the daily mysqloptimize cron job is like this (from the commandline):

chmod -x /etc/cron.daily/mysqloptimize

(-x makes a script non-executable, +x makes it executable).

The beauty of doing it that way is that (from the commandline) you can see which jobs are enabled from a quick glance. E.g.:

ls -l /etc/cron.daily/

All the green scripts are enabled, any in plain white are disabled.

The other thing about doing it that way, is that generally other tools will expect that convention to be followed. I haven't checked, but I'd expect that the cron jobs in Webmin would show the "mysqloptimize" script still being enabled, despite your script now essentially being non-functional.

As to the specifics of the value of running that script daily. TBH, I'm not really clear. I should probably do some further reading and get up to speed on it. FWIW, it has always been in TurnKey for as long as I've been involved (see here - it was added 7 years ago by Alon!).

As to it's value specifically in relation to INNODB vs MyISAM, the man page (note: mysqloptimize is essentially an alias for mysqlcheck --optimize) says:

The MyISAM storage engine supports all four maintenance operations, so mysqlcheck can be used to perform any of them on MyISAM tables. Other storage engines do not necessarily support all operations.

The man page is unclear on whether and/or how that relates to mysqloptimize. My guess is that mysqloptimize is still relevant to INNODB tables. Although if you wanted to double check, then you could manually run it and see what happens - it should spit out any relevant errors or warnings.

As to the actual value of running mysqloptimize daily, it seems that it is somewhat contentious. I'm assuming that Alon added it for a reason, although this blog post (from 2010) suggests that at best it may not be improving things that much, at worst may even be making them worse! This page (from late 2011) suggests that:

When you delete lot of rows from a table, optimizing it helps to get the unused space and defragment the data file. This might improve performance on huge tables that has gone through several updates.

So even if we do keep it as a default in TurnKey, perhaps it'd be better as a weekly (monthly?) job, rather than a daily one?!

With regards to more generally improving performance of MySQL/MariaDB, note that TurnKey includes the mysqltuner script. If you use it though, please note the warning. It can be run from the commandline like this:

mysqltuner

This relatively recent tutorial should give you a fairly good primer on usage if you're keen. I suggest that Database Administrators StackExchange is probably a great place to get any specific questions answered.

If you find anything of interest, please share! :)