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
Re: mysqldump with single-transaction with high-concurrency DB
Mark, You're correct that --single-transaction does create a consistent non-blocking read; I've used the same set of options (--single-transaction --master-data=1 --all-databases) to create point-in-time snapshots from which to create new replication slaves. However... I have also seen behavior similar to what you describe (system /seems/ to grind to a halt). Do you have any logs from the time this occurred, and do you recall any details such as: how long after you began the consistent dump did you notice the deadlock; were you writing the dumpfile to the same disk (array) as either your data or log files? Not knowing any details of your situation, I am guessing based on my experience, but here goes. Either, you have your datafiles set to autoextend, and the device they're on filled up with the dump file, or (and I think this is probably what happened, b/c it has happened to me) the deadlock you experienced happened long after you began the backup, and is due to InnoDB's multi-versioning. I'll try to explain... In order for InnoDB to maintain the consistent state of data from the moment the backup was begun, it must keep separate all modifications to all data after that point in time. Every subsequent connection/transaction will see the new (modified) version. In high-concurrency situations, it takes increasingly more work to keep the oldversion of the data as the undo logs fill up with new data, until eventually InnoDB can barely service new transactions since it is so busy trying to maintain the multiple versions. About a year ago, I had to create a new replication slave from a ~150GB InnoDB database -- iirc, it took about 3 days to complete the dump. After the first day, MySQL slowed to a crawl, serving less than half of it's usual transactions per sec. By the end, it was even slower. Now, I'm not much of a morning person, so I hope the above explanation made at least some sense... in case it didn't, here's a reference from the docs: http://dev.mysql.com/doc/refman/5.0/en/innodb-multi-versioning.html Update undo logs are used also in consistent reads, but they can be discarded only after there is no transaction present for which |InnoDB| has assigned a snapshot that in a consistent read could need the information in the update undo log to build an earlier version of a database row. If this is what happened, then it wasn't a true deadlock, and there may be a few simple solutions -- you may be able to adjust your configuration (my.cnf) to get better performance during the dump, or you may need to get a faster set of disks to write the dump to, or some other solution specific to your situation. If that's not what happened in your case, I apologize for the long-winded email. You'll have to provide more details about what error messages were reported, and your system configuration, for people to help more. HTH, Devananda Mark Steele 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
Re: mysqldump with single-transaction with high-concurrency DB
Argh! I should stop writing emails this early in the morning - I always miss something. I just noticed that, at the end of your first email, you did say the backup process was stuck on flush tables. The only way that I can think of for this to happen is if another thread were holding a table open for a long transaction. As soon as that thread were to COMMIT or close, the table should be flushed. Every client that connected after the backup process /should/ have to wait for the FLUSH TABLES to complete, but /that/ has to wait for all previous connections. Does that seem to fit what happened? Regards, Devananda Mark Steele 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
RE: mysqldump with single-transaction with high-concurrency DB
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
RE: mysqldump with single-transaction with high-concurrency DB
I have no long running transactions. I believe the deadlock to have been caused by a high concurrency of transactions. As soon as the backup started, mysqldump got the global lock, and seemed to hang during the flush tables (while hundreds of other clients try to execute queries). Should be easy enough to reproduce, but unfortunately I cannot as this is my production DB. -Original Message- From: Devananda [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 11, 2006 10:59 AM To: Mark Steele Cc: mysql@lists.mysql.com Subject: Re: mysqldump with single-transaction with high-concurrency DB Argh! I should stop writing emails this early in the morning - I always miss something. I just noticed that, at the end of your first email, you did say the backup process was stuck on flush tables. The only way that I can think of for this to happen is if another thread were holding a table open for a long transaction. As soon as that thread were to COMMIT or close, the table should be flushed. Every client that connected after the backup process /should/ have to wait for the FLUSH TABLES to complete, but /that/ has to wait for all previous connections. Does that seem to fit what happened? Regards, Devananda Mark Steele 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
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]
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
RE: mysqldump with single-transaction with high-concurrency DB
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
