Optimize database

[Mecha Dude]

I've looked at the --help output for mysql but it dosn't present what I think I need.

What is the command needed to optimize a database?

I plan to make a cron job with this.

[Mecha Dude]

Ah I lucked out and found a way:
mysqlcheck -pMYPASSWORD --optimize --databases MYDB

[Chris Cromer]

Does that optimize all the tables in the database?

[Mecha Dude]

It does, I ran it, then went in and ran the one built into YaBB SE, and clicks was the only one that needed to be optimized (as usuall o.O).

I have it running every 2 hours, yay!

[Overseer]

Is that really neccesary?

[Joshua Dickerson]

how about searching the mysql manual

[[Unknown]]

SMF has an option to auto optimize the database every x days .

-[Unknown]

[Spaceman-Spiff]

wont all these timed automated stuff like auto prune, auto optimize slows down the performance of SMF?
imo the best way to auto optimize is by using a cron job, like what Mecha Dude do

[[Unknown]]

wont all these timed automated stuff like auto prune, auto optimize slows down the performance of SMF?
imo the best way to auto optimize is by using a cron job, like what Mecha Dude do

Have you noticed any performance problems?

As long as you right write it correctly, it only adds one if.  That's not too bad.

-[Unknown]

[dennis]

i just run a cron job for backing my database up... but i dont really have a  need to optimise it

Hi bostasp,

I'm pretty new to MySQL, what is command for setting a cron job for backing up the database?

I don't have shell access to my network account, I only have web-base CPanel type of control panel, where a cron job can be settup.  I just need the MySQL command.  By the way, is it possible to backing it up into different file name each time?

Thanks,
Dennis

[dschwab9]

I don't acutally think it is possible to backup to a different file name each time, I tried to do that with my last hosts, who said it couldn't be done. I could be wrong though...

Why not create a variation on the current Optimizetables php file that writes the resulting sql file to disk instead of outputting to the browser, then call the php file via Crontab?

[Spaceman-Spiff]

Code Select Expand

mysqldump -pPASSWORD -uUSERNAME DBNAME > "`date +"%F"`.sql"

you can run a shell script if you want to backup multiple databases in different files

[Mecha Dude]

I've noticed no performance hit, it's allways snappy now.  However I had it running every 4 hours, and decided that's too much cron mail spam, so it only runs once a day.  At the moment my DB is just around 900 KB, so that might effect the server with larger databases?

[Joshua Dickerson]

during optimising, it will be very slow because it should be locked.