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.
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?