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>
