MySQL Table Optimization, which ones?

[X-ologist]

Hello IPB,

I have a BIG forum, which has my CPU usage pretty high.

I have already optimized a load of things, but high cpu usage remains.

My Host has told me that I should optimize my forums MySQL Database / it's tables.

I would like to know, which tables can and/or should be optimized.. and hopefully lower my CPU usage a bit without breaking the forum in some way.

Please let me know, I need this ASAP.

[The Old Man]

Hi

If you use PhpMyAdmin its very easy. View your database in PhpMyAdmin, using the Structure tab. Scroll down and look at the very bottom of your board's database.

It will show you the amount of any overhead (which is inevitable and builds up the more your database gets used) in Mebibytes (Mib).

(highlighted in yellow on the attached image)

Just click on the link that says "Check tables having overhead" and then click on Optimise table from the drop down list of options where it says "With Selected".

It may take a few minutes depending upon the size of your database, after which it will be optimised.

You may wish to back it up first.

[X-ologist]

I'll back it up, thanks, but

does this usually run smoothly, or cause some crashes, etc..

Please let me know.

Trying to lower my CPU usage:

[3DKiwi]

If you're not optimizing your database regularly then first time around I would suggest doing a few tables at a time. If you do the whole lot you may run into problems.

I do mine once a week as described above. Theoretically you shouldn't need to do this if you're running 3.4 as there's a scheduler task that does this once a day. I find however that I still need to optimize manually as my database is too big, checking the scheduler task log, the optimize task runs every day but no tables are optimized.

[GreenLinks]

You should never use PHP scripts to optimise your tables. That task should be done through SSH

[crmarks]

How large is your forum database, in MB or GB?

If we're talking GB, you should be very careful about optimizing large tables without first analyzing the size of each table, and understanding the resources and time required to optimize them.

It's generally a smooth process, but only if your MySQL server is optimized to handle large datasets, you don't want MySQL to run out of tmp space when working on rebuilding or optimizing large tables. If you're optimizing your posts table, likely the largest part of the database, it's may lock your forum database for quite some time while it works, your site will be unusable and may display database errors. It's best to take your forum offline, because the process can take a few minutes.

[X-ologist]

It's about 70MB.

I have blocked some bots right now in CPanel on IP.Deny, and tell you the truth, has done a GAZILLION TIMES better effect than that garbage .httaccess file

I'm still looking for ways to reduce it more.

[crmarks]

At 70MB, you should be able to safely optimize your tables without much worry, as per the advice The Old Man gave you above. Considering putting your forums in 'offline mode' just to be safe, but I imagine it will be a quick process to optimize.

[AlexJ]

Run this from shell and be done with it:

mysqlcheck -u root -p --optimize --all-databases
 

[3DKiwi]

Run this from shell and be done with it:

mysqlcheck -u root -p --optimize --all-databases
 

Thanks. Quicker than doing it via PhpMyAdmin however this appears to optimize all tables whether they need or not and places a big load on my server.

[AlexJ]

Thanks. Quicker than doing it via PhpMyAdmin however this appears to optimize all tables whether they need or not and places a big load on my server.

If the table is already optimized then it would say nothing to optimize. I optimize 2Gb of database in just few minutes, It's boring for me to do one by one.. and I do't have innodb tables much so it's fast for me.

I had read someone where that never optimize innodb tables cos it would put super high load on server.