5

I'm running a MySQL server with a couple of customer databases, some of which are quite large. We are doing a complete mysql dump once a day. It takes about half an hour to complete.

During this time, every request to the MySQL server gets a ridiculously slow response (sometimes around 1 s instead of something like 10 ms). Even though there are no timeout errors, I would love to see the queries being completed in a decent amount of time, so customer websites are not slowed down by hanging MySQL queries.

I researched a lot and learned about data replication, LVM snapshots, various mysqldump flag options and other work-arounds, but nothing did really improve the situation.

Is there any option to set a query priority in MySQL?

If there was a way to define query priorities, I could give the backup process a lower priority to be sure all the other queries are executed faster. The mysqldump process could be temporarily paused (SIGTSTP) when it's challenged by another request and continued (SIGCONT) when the query is finished. However, I couldn't find a feature like this in MySQL natively.

Giving the mysqldump process a higher nice value doesn't work, because it just increases the time both, the backup process and the table locks will be present. (I'm locking per-table.) Also, limiting IO write rates just leads to longer pain.

Are there any solutions I missed, other than LVM and replication?

  • 'mysqldump' grabs data direct from datafiles which will make other transactions slower. What you are searching for is an online backup with minimal/no effect on database. Replication and Mirroring are great options. So we need to search for MySQL utilities that provide these functionalities. – Bibhuti Bhusan Padhi Aug 30 '16 at 10:43
  • "I'm locking per-table." Well... why? It's not needed with --single-transaction. Also: Are you running mysqldump on the server itself? What happens when you don't? Are you compressing the dump as you make it (e.g. pipe to gzip)? What is the impact if you redirect the backup output > /dev/null. (Yes, serious question.) All of these things can have unseen impacts, but I make dumps of all systems at least once a day with mysqldump without similar issues. This should be something that can be resolved using standard tools, if we can pinpoint the slowdown's cause. – Michael - sqlbot Aug 31 '16 at 12:28
  • @Michael-sqlbot Thanks for your input. You could be right that compression has an impact, however our servers are quite potent and gzip is mainly challenging CPU, which cannot be the bottleneck here. I think IO is. Maybe there are several factors. I will investigate and add my results here! – Gerald Aug 31 '16 at 23:23
  • Don't assume I'm necessarily saying that compression has an impact in the sense that you expect. When the compression utility's buffers are full, its input blocks while it chews through the data which causes mysqldump to periodically suspend accepting data from the server, which would be especially undesirable if you are locking tables and not ideal in any event. Seriously, piping to /dev/null will tell you more than you might expect. If you still have issues, it might be interesting to try slowing down the dump stream in a smooth fashion, using a tool like redir or pv. – Michael - sqlbot Aug 31 '16 at 23:52
6

Indeed the bottleneck was IO. Since I was using tar -czf to compress the files and the CPU was so performant, the drive simply couldn't handle all the data coming so fast (although we use mirrored RAID volumes).

The mysqldump itself is no problem at all, but tar was slowing down the whole system massively.

I'm now using pv to shape the pipe stream:

tar -czf - ./ | pv -q -L 10m > output.tgz
  • -q disables output of pv.
  • -L 10m limits the write operation to 10 MB per second. Just test which value you should use on your server by watching the gzip process via top. I wanted it to have around 50% CPU usage, so the IO impact is lowered to 50% as well.
  • CPU impact is lowered implicitly, because pv slows down everything (except of the dump itself, it is done before compression).

Many thanks to @Michael - sqlbot and others for pushing me into the right direction.

2

You most likely need to use the --single-transaction option so that the dump of every table is in one giant read transaction. This will allow for a consistent snapshot of your data.

Even though it is possible to do mysqldumps on a live system, it is best not to perform mysqldumps on your live system because the InnoDB Buffer Pool basically gets its most frequent used data wiped out.

Even if you use XtraBackup, keep in mind the difference between XtraBackup and mysqldump when it comes to the backup's point-in-time if you start a backup at 12:00 AM and the backup takes 30 minutes:

  • Using mysqldump --single-transaction creates backup whose point-in-time is the start of the backup (12:00 AM, even after 30 minutes)
  • Using XtraBackup creates a snapshot of the data and logs whose point-in-time is the end of the backup (12:30 AM)

SUGGESTION

This sounds like you really need to setup MySQL Replication. This will make the live DB the Master. Your Slave can be the source of the backup.

Once MySQL Replication is established, you can do all kinds of backups (mysqldump, ) on a Slave without disturbing your Master. You no longer have to worry about setting query priorities or worry about slow queries competing with the mysqldump or having the Buffer Pool obliterated by a mysqldump resulting in reading data form disk more often.

On that Slave, you can do the following at midnight

STOP SLAVE;
FLUSH TABLES;
<< Run your backup with whatever method you choose >>
START SLAVE;

or you can be more aggressive on the Slave

STOP SLAVE;
SET GLOBAL innodb_fast_shutdown = 0;
service mysql stop
Copy /var/lib/mysql to a backup folder
service mysql start
START SLAVE;    

Please my answer to the following posts for more suggestions

  • Even though your suggestions are correct, this is not the solution I was initially looking for. We already use --single-transaction and performance optimizations. However, I don't see why MySQL has no option for prioritizing queries, like mentioned. Slave DBs add another layer of redundancy that is—for the sole purpose of backing up data—superfluous. Also, copying /var/lib/mysql is something totally different, as you might probably know. I want to generate INSERT statements, like mysqldump does, but I want them to be generated in a non-blocking way, without replication. – Gerald Aug 31 '16 at 3:48
1

You should definitely have a look at XtraBackup by percona, which just copy the data directory instead of querying the DB.

If you want to continue using mysqldump you should check out the quick option:

mysqldump can retrieve and dump table contents row by row, or it can retrieve the entire content from a table and buffer it in memory before dumping it. Buffering in memory can be a problem if you are dumping large tables. To dump tables row by row, use the --quick option (or --opt, which enables --quick). The --opt option (and hence --quick) is enabled by default, so to enable memory buffering, use --skip-quick.

  • Thank you for the hint, I didn't know about XtraBackup (it is open source, which is good). However, we want to generate regular SQL code because of various reasons (exchangeability and the users need to be able to download an SQL representation of their DB backups). If you'd be able to provide a tool that converts DB files to SQL INSERTs, this was a viable solution to me. – Gerald Aug 31 '16 at 3:58
  • Ok, I see. I only know that you can recover a table from the .frm and .ibd files within a(nother) mysql server. But this makes no sense to you. – s.meissner Aug 31 '16 at 13:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.