Version: , by kontrabass
Developer Last Online: Sep 2013
Version: Unknown
Rating:
Released: 08-01-2006
Last Update: Never
Installs: 0
No support by the author.
My DB is nearing 3 million posts, and I do not use fulltext searching. So my database is quite large (I do have attachments/images stored outside of the database though). I use mysqlhotcopy to do a nightly backup at 2am Central time. Problem is, my site stops responding for about 2 minutes at this time, and then for the next 3 minutes or so users get "server too busy" errors. The site is back up and running around 2:07am according to user reports.
I know this might seem like a small price to pay for a nightly backup... but my Australian visitors are getting a bit irked, as it happens every single day during their peak usage time.
My DB server is quite robust with dual opterons and 15k drives - during peak usage during the day loads never begin to reach .5. But the backup sure does overload things. Is there anything I can do to tweak mysqlhotcopy? Or some other means to do a reliable nightly backup? Thanks!
Show Your Support
This modification may not be copied, reproduced or published elsewhere without author's permission.
Be aware that eventually even a master / slave setup will not be all that optimal for searching. It alievates the table locking issues but doesn't make searching any faster, and the more posts you have the slower searching will be. The best solution I found for searching was to switch to sphinx... the best change I ever made.
That is true, I have also switched my larger sites over to Sphinx. That's not to say though that there still isn't a use for the master/slave setup. Quite a few queries get offloaded to the slave (about 20% on my sites) and you can run backups without having to take the site down.
While a slave server might be the best solution, perhaps only backing up non-recoverable data might help. Our forum DB is about 1.1 gigabyte, but without indexes (both sql and vBulletin search index tables) it shrinks by about 400 Mb to just over 700 Mb.
Obviously rebuilding the vBulletin search index will take time though, so if you need to be back online fast, don't skip it in the backup. But rebuilding the sql indexes can be done relatively fast using myisamchk.
I use MysqlDump myself and it seems to work relativly fast and efficiently, the way i have it now, it runs the dump while my forum is online during a slow time.
Im concerned that as the foum gets even bigger doing the dump while the forum is active may cause problems here and there
So I'm wondering if anyone knows a way to turn the forum off (from the command line) and then do the mysqldump.
My cron script would
1) turn off vbulletin
2) do mysql dump
3) turn vbulletin back on
I use MysqlDump myself and it seems to work relativly fast and efficiently, the way i have it now, it runs the dump while my forum is online during a slow time.
Im concerned that as the foum gets even bigger doing the dump while the forum is active may cause problems here and there
So I'm wondering if anyone knows a way to turn the forum off (from the command line) and then do the mysqldump.
My cron script would
1) turn off vbulletin
2) do mysql dump
3) turn vbulletin back on
I have a web server and a separate DB server. The web server is also a write-only slave to the DB server. Since it's write only and vBulletin does far more reads than writes the extra load to run this slave is minimal. I've got 750,000 posts and 6,000 unique visitors a day.
I then backup the slave, using mysqldump in a cron job, during an offpeak time. Zero impact to users.
How to backup DB without overloading server? Details »»
About Developer
No support by the author.
03-09-2007, 08:27 PM
