Sample Header Ad - 728x90

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

1 vote
1 answer
819 views
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? enter image description here enter image description here enter image description here
Asked by merlin (323 rep)
Dec 27, 2020, 08:08 AM
Last activity: Aug 5, 2025, 02:05 AM