Dan's picture

I have been using the new LAMP stack V15 in production about a week now... so far LOVE it.

I am running into an old issue... that I "fixed" for me with the V14 LAMP and that is the daily optimization of the MySQL tables....   my tables are so large that when it starts it brings my server to its knees and users can't work.

I see in the scheduled cron jobs this:


That runs at 6am every day....     

Is the 2nd line there "mysqloptimize' causing my issue?  and if so how can I remove just it from the job as it appear I can not edit that job.   

I could create a new job with everything BUT that in it....  

This question is in searching the web I find that many people seem to think its not necessary to optimize INNODB tables... only MYISAM tables....  which I am not using MYISAM at all in my tables.

Anyone have an opinion on this, or know something I dont?  




Dan's picture

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: 



And commented out the line that runs the command:


from this: 


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


to this:


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

Jeremy Davis's picture

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:


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! :)

Add new comment