MySQL Data Dumper

What exactly is 'inconsistent' in the context of --no-locks and how does it interact with --use-savepoints?

Asked by Phil Stracchino

It's pretty obvious that if mydumper --no-locks is used, MyISAM tables will not be consistent. But what if, "hypothetically speaking", I have a large (approaching 1TB) customer database in XtraDB Cluster 5.6 for which the customer data is entirely in InnoDB tables, with no MyISAM tables existing outside the mysql system schema, and the mysql schema can be considered static? Will --no-locks still give me all of the InnoDB data as a consistent single transaction? In this scenario, how does --no-locks interact with --use-savepoints?

In general, in this scenario in which there can be assumed to be no MyISAM data changes, all data is in InnoDB, and locks should be avoided at all costs if possible, what combination of options will give me a consistent snapshot of all of the InnoDB data with the least possible locking?

The invocation I'm testing out right now is as follows:
mydumper -Cce --use-savepoints --success-on-1146 --no-locks -L /root/mydumper.log -h [host] -o /[dump directory]

The --success-on-1146 is in use because this customer has thousands of small schemas with a high turnover rate; our current mysqldump-based dump script FREQUENTLY reports dump anomalies — sometimes several in a single dump cycle — because by the time mysqldump gets around to dumping a particular schema, it doesn't exist any more.

Question information

Language:
English Edit question
Status:
Solved
For:
MySQL Data Dumper Edit question
Assignee:
No assignee Edit question
Solved by:
Phil Stracchino
Solved:
Last query:
Last reply:
Revision history for this message
Max Bubenick (max-bubenick) said : 		#1

Hi Phil.

Still in mydumper you need to coordinate all the threads, so a global lock is still needed until we have an option to start transaction using a snapshot from other. Also is still need it for a single thread case for binlog position, but here there are different option.

MariaDB since 5.3 have:
SHOW STATUS LIKE 'binlog_snapshot%'

Percona Server since 5.6.16 have backup locks to avoid FTWRL

But unfortunately still we don't have a full solution to avoid mydumper locks.

So going back to your scenario for a consistent backup you can use --trx-consistency-only which still will acquire a global read lock but only for the time to start the transactions for each thread. If you still wants to use --no-lock take in mind that you can loose data if any commit happen between the first start transaction and the last, also the binlog position can not be exact.

About --use-savepoints is not related to global locks but metadata locks, so is not affect by --no-lock --less-locking or --trx-consistency-only.

Revision history for this message
Phil Stracchino (phil.stracchino) said : 		#2

Max,
Thanks for the prompt response.

I was not aware of the --trx-consistency-only option, it's documented neither in 'mydumper --help' nor in the man page. I'll give it a try.

The database in question has roughly 214,000 tables in just under 2600 schemas. The XtraDB cluster node mydumper was running against has 64GB of RAM and open_files_limit set to 999999. mydumper ran for roughly 90 minutes without writing anything but its metadata file, then started trying to write data and immediately choked on a 'Got packet bigger than 'max_allowed_packet' bytes' error. (There is a max_allowed_packet line in the [mydumper] group in /etc/my.cnf, which is set to the same value (128M) as the cluster, but mydumper does not appear to honor it.) It then tried to start dumping tables and wrote about a thousand empty files, complaining that the MySQL server (or more accurately, its connection to same) had gone away and it could not create files. (There was plenty of space and it clearly had write permission, because it created the empty files; it simply didn't have any data to write into them. It apparently looked at the resulting "0 bytes written" and misreported it as "unable to create files".) I don't know whether the worker thread connections died on max-allowed_packet errors, or got timed out by mysqld during the 90 minutes the master thread sat there planning its moves. Finally after about a thousand failed writes, mydumper declared mission failure and shut down.

Suggestions:
1. mydumper really needs to honor max_allowed_packet.
2. Possibly mydumper should not create its worker threads until it is actually ready to start using them, to avoid having them killed by mysqld if wait_timeout is exceeded?
3. mydumper needs to be aware of, and correctly report, the difference between "I couldn't write any data because I can't create files" and "I couldn't write any data to the file because I had no data to write".

Revision history for this message
Phil Stracchino (phil.stracchino) said : 		#3

...And in fact, 0.6.2 says 'Unknown option --trx-consistency-only'.

Revision history for this message
Max Bubenick (max-bubenick) said : 		#4

Oh sorry my bad, that option will be in the new release, 0.9.1, hope to be soon.

About your previous issues mydumper set its own wait_timeout on every connection and should be reading [mydumper] section on .cnf but I will need to confirm is working as expected.

Revision history for this message
Phil Stracchino (phil.stracchino) said : 		#5

Max,
I hope the jump from0.6.x to 0.9.x means mydumper is nearly ready for release. I am REALLY looking forward to being able to deploy it in production. :)

Revision history for this message
Max Bubenick (max-bubenick) said : 		#6

https://blueprints.launchpad.net/mydumper/+spec/snapshot-from-session

^^ look into that, once implemented if you running Percona Server >= 5.6.17 no locks will be needed.