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.
Best Answer
PROBLEM
It is logically possible because --single-transaction gets "thrown under the bus" if any
ALTER TABLEcommands are launched intermittently during the mysqldump (See this post from mysqlperformanceblog.com).What happens when a dump faces off against an ALTER TABLE ?
mydb.mytable, anALTER TABLEfrom session #2 may pause because it needs to perform a table lock.ALTER TABLEonmydb.mytable, it could produce spurious results from session #2 trying to run mysqldump on that table (Possible empty table [missing data])It is not so much a deadlock in the traditional sense, but it safe to say that the
--single-transactionoption can get its loyalty displaced and lose it point-in-time window from the first victimized table and all other tables after it.SOLUTION
Setup Master/Slave Replication
You should never encounter deadlocks as long as the Slave is used for reads and backups only.
GIVE IT A TRY !!!