Portal Home > Knowledgebase > Articles Database > Optimize tables on an SSD
Optimize tables on an SSD
| Posted by Noopy, 01-23-2012, 08:31 PM |
| Would it be considered bad to optimize tables on a weekly basis on an SSD? If not, would it be possible to have this set under crontab -e? I currently use this command to optimize the tables (which requires a pw to execute):
Last edited by Noopy; 01-23-2012 at 08:44 PM.
|
| Posted by Grumps, 01-24-2012, 01:59 AM |
| Regardless of disk type, optimizing helps. Optimizing will decrease the tree depth and that's the core purpose of optimizing. This means it needs to make less number of reads.
|
| Posted by nel$on, 01-24-2012, 03:15 AM |
| What effect will optimizing have on the flash memory though?
|
| Posted by pubcrawler, 01-24-2012, 03:42 AM |
| It won't ruin your SSD. Take it that is your concern, premature wear.
|
| Posted by Noopy, 01-24-2012, 06:25 AM |
| Good deal, anyway to automate this process?
|
| Posted by Noopy, 01-24-2012, 06:30 AM |
| Also, how often should one optimize their tables? I run a few forums and blogs that accumulate 3 - 5 million impressions /month. About 80% read/20% write.
|
| Posted by Larry David, 01-24-2012, 06:40 AM |
| I optimize everyday in the early hours. First repair then optimize all database / tables:
mysqlcheck -Ar -u Username -pPassword
mysqlcheck -Ao -u Username -pPassword
|
| Posted by Noopy, 01-24-2012, 09:38 AM |
| Thanks appreciate it Larry.
|
| Posted by FastServ, 01-24-2012, 09:41 AM |
| You can create a file '.my.cnf' with username and password defined in the /root directory and avoid the -u and -p switches. Be sure to chmod it 400 to stop another user from being able to read it.
|
| Posted by net, 01-24-2012, 09:54 AM |
| Moved > Hosting Security and Technology.
|
| Posted by Dr_Michael, 01-24-2012, 10:52 AM |
| I do not think it is needed with such a frequency.
Once per week should be more than enough.
Besides, if you have some huge tables, it may take long time and will make your sites not available during the perform.
|
| Posted by Noopy, 01-25-2012, 12:26 AM |
| I'm pretty sure this can be argued back and forth. My database is 1.5GB (images, css all in filesystem) and takes at most 20 seconds.
I would rather limit that even though it is just 20 seconds (if someone is compelled to write a book at 4am and post it during those 20 seconds, I'm sure they'd be a tad bit upset lol).
Will run it weekly (lowest traffic day)... the performance gain is minimal (from daily). I am a freak about speed and have tested for about a month now (daily, every other day, twice a week, weekly, bi-weekly). As for a standard HDD I don't know but the nanosecond seek rate of an SSD does indeed help.
I would be curious to hear what other forum owners are doing in this regard.
Last edited by Noopy; 01-25-2012 at 12:29 AM.
|
| Posted by linuxtechz, 01-25-2012, 12:40 AM |
| But I suppose its not advisable if you are running a large database and users are posting on it throughout the day regularly. AS when optimization is done, the tables are locked to prevent any kind of error or crash.
|
| Posted by pubcrawler, 01-25-2012, 12:44 AM |
| I forget the exact reason why optimization is necessary with MySQL. But I believe it's from record deletions mainly. It does make a big difference on old spinning disks. On SSDs, I suspect much less of an issue.
My rule has been to optimize infrequently as it can be a resource pig
and present a potential outage when dealing with large databases.
If you must optimize, the overnight low usage time is the way to go. But, only do such where your data will optimize in a quick process < 30 seconds. It will create page failures, timeouts, etc.
There is the potential of introducing a corrupted table due to optimization failure also. So, you need to check for and resolve such when and if it occurs.
|
| Posted by Noopy, 01-25-2012, 01:47 AM |
| Yup, this has happened to me before, didn't notice it until a day later. Did a quick repair and all was fixed. Guess the best practice would be optimize then repair.
|
Add to Favourites 
Print this Article
Also Read
