5.0.22 -----Original Message----- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 11, 2006 9:55 AM To: Mark Steele Cc: mysql@lists.mysql.com Subject: Re: mysqldump with single-transaction with high-concurrency DB
Sorry Mark, appears you're right. --opt is the default now; didn't used to be, I don't think. Also, my experience with MyISAM is a total lock on all tables across all databases during a mysqldump ... but you are using InnoDB obviously. I think you're right in your reading of the docs - that you should be able to keep going during a dump. Bug? What version are you on? Dan On 7/10/06, Mark Steele <[EMAIL PROTECTED]> wrote: > > Dan: The options I specified are correct (according to the > documentation) to get a consistent non-blocking snapshot. > (--single-transaction disables --lock-tables, --opt is the default > behavior for mysqldump). > > My question was more in the nature of will these options work in high > concurrency situations or will they cause a deadlock. (or am I missing > something here) > > The documentation states that --single-transaction will get a global > lock 'for a short period of time', which I thought to mean that it'll be > short enough to not disturb normal operations (which is what is implied > in the documentation). > > If this isn't the case in high-concurrency situations, anyone have > another method to get a consistent snapshot? > > Cheers, > > Mark > > -----Original Message----- > From: Dan Buettner [mailto:[EMAIL PROTECTED] > Sent: Monday, July 10, 2006 3:21 PM > To: Mark Steele > Cc: mysql@lists.mysql.com > Subject: Re: mysqldump with single-transaction with high-concurrency DB > > Sorry Mark - I thought your question was more of a "does this seem > right" and "how do I" than a "something's wrong here" post. > > I think your problem is coming in with the use of --opt. The article > you reference, where it says "This is an online, non-blocking backup", > makes no mention of --opt, which as you note includes --lock-tables. > From mysqldump man page: > > --lock-tables, -l > > Lock all tables before starting the dump. The tables are > locked with > READ LOCAL to allow concurrent inserts in the case of MyISAM > tables. > For transactional tables such as InnoDB and BDB, > --single-transaction is a much better option, because it does > not > need to lock the tables at all. > > Please note that when dumping multiple databases, > --lock-tables > locks tables for each database separately. So, this option > does not > guarantee that the tables in the dump file are logically > consistent > between databases. Tables in different databases may be dumped > in > completely different states. > > Try running without --opt, possibly specifying the included options > you need individually, and see if that works better for you. > > I understand what you're saying about MySQL replication; hence the > need for monitoring the replication to ensure good backups. > > Dan > > > > > On 7/10/06, Mark Steele <[EMAIL PROTECTED]> wrote: > > Hi Dan, > > > > > > --single-transaction > > Creates a consistent snapshot by dumping all tables in a > > single transaction. Works ONLY for tables stored in > > storage engines which support multiversioning (currently > > only InnoDB does); the dump is NOT guaranteed to be > > consistent for other storage engines. Option > > automatically turns off --lock-tables. > > --opt > > Same as --add-drop-table, --add-locks, --create-options, > > --quick, --extended-insert, --lock-tables, --set-charset, > > and --disable-keys. Enabled by default, disable with > > --skip-opt. > > > > See http://dev.mysql.com/doc/refman/5.0/en/backup-policy.html > > > > These options should produce a non-blocking consistent database > > snapshot. > > > > I can already accomplish this on a slave server, however MySQL > > replication can lead to slave drift as it is statement based (as > opposed > > to row-based replication). The only safe way to guarantee a real > backup > > in a MySQL replication setup is via snapshots on the master. > > > > -----Original Message----- > > From: Dan Buettner [mailto:[EMAIL PROTECTED] > > Sent: Monday, July 10, 2006 2:42 PM > > To: Mark Steele > > Cc: mysql@lists.mysql.com > > Subject: Re: mysqldump with single-transaction with high-concurrency > DB > > > > Mark, that's the expected behavior of mysqldump with --opt and > > --single-transaction; it locks all databases and all tables for the > > duration of the dump, ensuring a consistent snapshot. > > > > With a database this size (100 GB), it's an area where throwing > > hardware at the problem may be your best bet. I suggest one of two > > approaches as possible solutions: > > > > 1) Buy a *really fast* disk array and set it up as striped on a > > superfast connection, like Ultra320 SCSI or fibre. This will lower > > the amount of time required to write the mysqldump output (which will > > likely exceed 100 GB data size due to overhead within the file). You > > might even look at 2 disk arrays on 2 channels, striping across both > > the disks in the array and across the arrays. Pros: fairly easy to > > do, not terribly expensive. Cons: You still lock up your main > > database server for backups, though possibly for less time than you do > > now. > > > > 2) Buy a second physical server for MySQL and set up replication. > > Then use the replication server to do your backups - provided you > > never let people connect directly to it, no one will notice when it > > locks up for a few hours dumping data. Once it's done dumping, > > replication will catch up on its own. This doesn't even have to be a > > very fast box, depending on your needs. If it falls behind from time > > to time that may be acceptable - depends on your needs. Pros: > > possibly less expensive than superfast arrays, no lockups of your main > > server, backup server in case of primary failure. Cons: requires > > monitoring of replication, and still requires a one-time consistent > > dump as a starting point for replication. > > > > HTH, > > Dan > > > > On 7/10/06, Mark Steele <[EMAIL PROTECTED]> wrote: > > > Hi folks, > > > > > > > > > > > > I've recently tried to do a database backup on a database server > that > > > has a fairly high concurrency rate (1000+ queries/sec) and have > > noticed > > > that the backup process seemed to deadlock the machine and I had to > > > resort to extreme measures to get the database back up (killed the > > > process and had to restart it in recovery mode). > > > > > > > > > > > > The command: > > > > > > mysqldump --all-databases --opt --single-transaction --master-data=1 > > > >dump.txt > > > > > > > > > > > > All my tables use InnoDB, and the database is about 100 gigabytes in > > > size. > > > > > > > > > > > > Does anyone have any suggestions for getting consistent database > > > snapshots? > > > > > > > > > > > > I tried the InnoDB binary backup tool in the past, but that lead to > a > > > corrupted database, and I'm not sure that it'll lead to a different > > > outcome as both single-transaction and the binary backup tool use > the > > > same mechanism (versionnning). The documentation describes the > > > single-transaction as taking a short global lock, which is the root > > > cause of the deadlock I saw I believe. > > > > > > > > > > > > When the server was deadlocked, all the connections were 'waiting on > > > table', and the backup process was apparently stuck on 'flushing > > > tables'. > > > > > > > > > > > > Cheers, > > > > > > > > > > > > Mark Steele > > > Information Systems Manager > > > > > > Zango > > > > > > E: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > > > P: 514.787.4681 | F: 514.787.4707 > > > > > > www.zango.com <BLOCKED::http://www.zango.com> > > > > > > Read our blog at http://blog.zango.com > > <BLOCKED::http://blog.zango.com> > > > > > > > > > > > > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
