MySQLdump leads to exceeding max connections. Skip-Quick as a solution

MySQLmysqldump

Every night I do a full mysqldump of a 17G DB in MySQL 5.7.32. This worke for years, now I am doing some heavy load on inserts during the night which caused at exactly the backup time the connections to rise to max_connections which led to connections errors.

As the Server has enough RAM 64G (30G free), I increased max_connections from 150 to 300 in a first reaction.

However looking at the dump command I found the option — quick (also enabled by default) which tells me that it is exporting row by row.

--single-transaction --routines --quick --compact

I am thinking of changing this to --skip-quick. but dare to change this since I would need to check the restore again and this is very time consuming.

Looking at the connections over time I also noticed that there are some interruptions around that time period. So maybe connections stack up since there is a block during MySQLdump?

MySQL error log shows a large amount of the following error, although not at this time points but continuously throughout the day:

Aborted connection 63182018 to db: 'mydb' user: 'test' host: 'myhost' (Got an error reading communication packets)

How would you approach this problem?

Best Answer

mysqldump takes only 1 connection.

There is 1 extra connection for root. (Note the 151 vs 152.) I hope you are not using root for your app; change that if you are. Then allow mysqldump to be the only root process running when it is doing a dump.

Limite the number of connections requested -- by adjusting the web server (or whatever is doing connections). Web servers tend to allow far more children than is efficient.

Disconnect (in the app) from MySQL when finished processing.

Lower wait_timeout -- this will lead to disconnecting idle threads or threads that are held unnecessarily long by a "connection pool".

Running mysqldump takes resources away from other queries; be sure to run it at off times.

Use the slowlog to find out what queries are the most impacted by the dump; then let's discuss improving them.

If you have an LVM snapshot, why bother with mysqldump? LVM is so much faster and less invasive.

Related Solutions

Mysql – “Too many connections” when running MySQLDump

If you convert all your tables to InnoDB, then you can perform your mysqldumps without disturbing any of DB Connections.

Here is a script to mass convert all MyISAM tables into InnoDB.

mysql -uroot -p -AN -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') InnoDBConversionSQL FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql','performance_schema') ORDER BY (data_length+index_length)" > /root/ConvertMyISAMToInnoDB.sql

Just execute /root/ConvertMyISAMToInnoDB.sql during a full maintenance. This needs to be done only once. Once all your tables are InnoDB, you could probably mysqldump your databases in parallel. I wrote an earlier post back in April 2011 on multiple ways to perform mysqldumps. You need option 2. You will also need to add the --single-transaction option to each mysqldump so that all the mysqldumps are launched from the same point-in-time.

However, you could still experience performance problems anyway because each table that is being mysqldump'd will push old data out of the InnoDB Buffer Pool to push in table data for each table being mysqldump'd.

Operations must slow down because queries being executed must share the InnoDB Buffer Pool with the mysqldumps that are thrashing it.

As for upping the max_connections you can do this without restarting mysql.

Please add this to my.ini

[mysqld]
max_connections=1000

You do not have to restart mysql. Just run this command in the mysql client:

mysql> SET GLOBAL max_connections = 1000;

You may need more RAM on the box to accommodate additional DB Connections as well as a Decent Sized InnoDB Buffer Pool.

MySQL Error Handling – log_warnings Not Working for Aborted Connections in MySQL 5.1

This seems to be an elusive bug nobody wants to fix

http://bugs.mysql.com/bug.php?id=24761 (2006-12-01 : Status Triaged)
http://bugs.mysql.com/bug.php?id=41602 (2008-12-18 : Status Closed)
https://serverfault.com/questions/235369/how-can-i-get-mysql-5-5-to-log-warnings-to-one-of-the-log-files (2011-02-14)

According to http://bugs.mysql.com/bug.php?id=24761, the aborted connect is recorded in the general log

[29 Sep 2008 8:03] Konstantin Osipov
OK, it was fixed differently:
      /*
        Log the command before authentication checks, so that the user can
        check the log for the tried login tried and also to detect
        break-in attempts.
      */
      general_log_print(thd, command,
                        (thd->main_security_ctx.priv_user ==
                         thd->main_security_ctx.user ?
                         (char*) "%s@%s on %s" :
                         (char*) "%s@%s as anonymous on %s"),
                        thd->main_security_ctx.user,
                        thd->main_security_ctx.host_or_ip,
                        db ? db : (char*) "");

So, this is logged in the general log at least.

I was reviewing a patch that added more logging,
but I can't remember the worklog task number.

We just have to wait on MySQL (I mean Oracle) to fix it. (That was sarcasm)

Best Answer

Related Solutions

Mysql – “Too many connections” when running MySQLDump

MySQL Error Handling – log_warnings Not Working for Aborted Connections in MySQL 5.1

Related Question