Automatic SQL Optimise Query

[Shamil]

Hi, I think it might be beneficial to add to the cron, the option of an automatic optimise query, to help keep the SQL in good shape. Many forum admins neglect this wonderful feature to essentially "defragment" the overhead on the SQL table.

Perhaps once or twice a month?

 

[Koleckai]

Optimize should only be run if you delete a lot of information from your database. It is doubtful that running it on a regular basis will provide any benefit and you should definitely not run it during page loads.

 

[digitalpoint]

Definitely should NOT be automatic since it locks the table being optimized. My post table is 5.7GB... running an optimize on it requires the board be shut off for users.

If someone *wants* to do it, they can... doesn't need to be done by the forum software itself. They can set up their own cron job to do it.

 

[Tigratrus]

Definitely should NOT be automatic since it locks the table being optimized. My post table is 5.7GB... running an optimize on it requires the board be shut off for users.

If someone *wants* to do it, they can... doesn't need to be done by the forum software itself. They can set up their own cron job to do it.

Agreed. But it might be nice to provide an *option* for it (as the OP mentioned) in the Maintenance section, but it would certainly have to be carefully labeled regarding the potential issues. Not a critical first release issue as it's an easy add later, but time permitting it's potentially useful for some.

Personally I'd just use Navicat to go in and work with the DB directly .

 

[mlx]

I totally agree. Our post table is about 2 GiB ... so I guess the optimize query would lock the forum for several minutes ... i.e. something like this shouldn't be done automatically.

 

[Stuart Wright]

I think the philosophy of Kier and Mike has been, where there is a choice but an obviously better option, don't offer the choice. Just do the best option.

 

[Mike]

To be honest, if you can afford to do an optimize (because your data is small enough), you don't need to do it.

 

[Shamil]

To be honest, if you can afford to do an optimize (because your data is small enough), you don't need to do it.

Is there any performance issue with table with heavy overhead?

 

[Floris]

No.

The overhead worry is in regards to tables getting corrupt.

Running it manually every quarter should be enough for big sites who prune a lot.