|
nutballs
|
 |
« on: January 02, 2010, 11:08:29 PM » |
|
I run a master slave database setup.
master is basically for everything.
Slave is just to have a live clone in case of crash that I can warm switch to, as well as run backups from.
The problem I am having is that when I mysqldump the slave, it gets about 10000 seconds behind the master and causes about 8 hours of lag. This really is not a big issue in my current setup, since all I am doing it using the slave as only that, a slave.
However, I am looking to use the slave as a readonly box as well if possible, to spread a really heavy lookup that I have to do.
Any thoughts on how to backup mysql without causing table locks or at least keep the database readable even though its being backed up?
|
|
|
|
|
Logged
|
I could eat a bowl of Alphabet Soup and shit a better argument than that.
|
|
|
|
perkiset
|
|
« Reply #1 on: January 03, 2010, 12:33:56 PM » |
|
a 10K second lag? What are you moving meng? And how far away from each other are these boxes?
If you have no changes pending, does the change move straight away, or is it that you're bogging the slave replication with so much activity? Is there so VERY much activity that the master cannot do the slave at that moment (processor overload) and so it gets way behind? I'm not sure I understand what you mean when you say, "mysqldump the slave..." are you manually trying to dump? Is it not autoreplicating?
|
|
|
|
|
Logged
|
It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
|
|
|
|
nutballs
|
|
« Reply #2 on: January 03, 2010, 01:26:59 PM » |
|
no no. the slave catches up.
The problem is that when I run mysqldump (or actually mk-parallel-dump), it locks the tables so they cant be used.
This is why I do not backup from the master. It takes SOOOO LONG to backup. So, i backup the slave instead. On the slave, since I only currently use it for replication, and nothing live uses it, its no big deal that it falls behind while being backed up. However, I would love to use it for reads... In which case, i cant while the backup is running since the tables get locked.
So every day the slave is fully caught up with 0 seconds of lag. But once the backup starts, on the slave, it starts falling behind until done, and then starts catching up.
On MSsql, this is not a problem generally, or at least i never experienced it. So WTF? Is there no way to backup a live mysql database without bringing everything to a grinding halt?
Oh and they are right next to each other on a 1 gig switch. But thats irrelevant. I actually backup to the local disk first, then copy over to an archive server when done.
|
|
|
|
|
Logged
|
I could eat a bowl of Alphabet Soup and shit a better argument than that.
|
|
|
|
perkiset
|
|
« Reply #3 on: January 03, 2010, 01:30:34 PM » |
|
InnoDB. It's the MyISAM table locking problem, I'm pretty durn sure. Inno will do row-lock and you're golden.
|
|
|
|
|
Logged
|
It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
|
|
|
|
nutballs
|
|
« Reply #4 on: January 03, 2010, 05:08:52 PM » |
|
the innodb tables are also locking. it because the backup routines, mysqldump and mk-parallel-dump, both enable table locks first it seems. Though that does make me wonder... There must be a switch somewhere...
guess i gotta go diggin around in the docs.
|
|
|
|
|
Logged
|
I could eat a bowl of Alphabet Soup and shit a better argument than that.
|
|
|
|
bottomless
|
|
« Reply #5 on: March 31, 2010, 04:48:48 PM » |
|
First use InnoDB, then use the --single-transaction switch of mysqldump to avoid locking the table.
The command may also eat up all the CPU so it's even better if you use the nice command with the lowest priority as I do here:
blog dot bottomlessinc dot com/2010/02/dumping-innodb-tables-without-causing-the-slave-to-fall-behind/
|
|
|
|
« Last Edit: March 31, 2010, 04:56:33 PM by perkiset »
|
Logged
|
No links in signatures please
|
|
|
|
perkiset
|
|
« Reply #6 on: March 31, 2010, 04:57:09 PM » |
|
Welcome to The Cache bottomless.
Let's hang out a while before we go all linkswappy on each other, shall we?
|
|
|
|
|
Logged
|
It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
|
|
|
|
kurdt
|
|
« Reply #7 on: March 31, 2010, 10:48:12 PM » |
|
Or ditch MySQL and start learning new databases that are built for replication  Not very viable solution in your situation though  |
|
|
|
|
Logged
|
I met god and he had nothing to say to me.
|
|
|
|
