• Welcome to Simple Machines Community Forum. Please login or sign up.
October 16, 2021, 10:24:43 AM

News:

Join the Facebook Fan Page.


CRON script to optimise tables.

Started by nax, June 05, 2020, 08:53:54 AM

Previous topic - Next topic

nax

Hi does anyone have a script that can be run in CRON for optimising a database/tables?

I usually go into cPanel once a week and do this manually, the database is quite large (2.9Gb), and this function cannot be accomplished successfully from within the Admin functions of SMF.

The database called smf1 and is all MyISAM tables.
I am on SMF 2.0.17
cPanel Version   88.0 (build 6)
Apache Version   2.4.43
PHP Version   5.6.40
MySQL Version   5.7.30
Architecture   x86_64
Operating System   linux


There is mention of a script in the User manual
https://wiki.simplemachines.org/smf/Performance_enhancements
" and that optimize database is disabled if you are doing it with your own script (see mine below)."
Same thing here https://www.simplemachines.org/community/index.php?topic=293441.380
But there is no example "below" as far as I can see.

I there a simple query to select tables in a database having overheads and optimise them?
I've been googling examples but I don't really have the DB experience here.


Arantor

So you're using MyISAM... you'd actually be better off switching to InnoDB to be honest.

But every time you sit and do that optimisation, you lock the entirety of the forum for all users from doing anything, while the entire database is rewritten from top to bottom.

Would recommend NOT doing it.
No good deed goes unpunished
All helpful urges should be circumvented

nax

The DB is large but the number of users is now quite small, actually one or two only when I do the optimisation. I'm trying to automate as much as I can for this site (it's not mine I just volunteer to try and look after it). Backup and some other tidying up I've managed, this is all that's left of thing that I do on a regular basis apart form SMF upgrades.

Arantor

I'd suggest simply not running the optimise step outright. If the number of users is quite small, presumably the amount of change of posts is also quite small, so the change to the DB being "optimised" is minimal?

The only time optimise is actually of any help is when you mass edit or remove posts (like, thousands upon thousands of posts) from MyISAM tables - the advice was more sound in 2003 but things have moved on, not least MySQL's ability to not need to optimise tables all the time.

It really doesn't actually help all that much.
No good deed goes unpunished
All helpful urges should be circumvented

nax


Advertisement: