MySQL, by default, only allows 151 connections (plus one more for a super user).
In your MATLAB output, you show 149 then 150 threads. I presume that the majority of those are connecting to the database. 150 MATLAB threads will generate 150 connections.
There are few ways you could try to fix this.
One possibility is to limit the number of threads in MATLAB. You can do that using the maxNumberCompThreads function. Note that this is only available in R2007b through R2010a (and it will give you warning as of R2009b). Alternatively, the -singleCompThread option will work as well, but limits you to one thread.
Try to limit the time that you have a connection open to the absolute shortest interval possible: Open the connection, process the database work, close the connection. This may work, depending upon how much other (non-database) work each thread is doing.
Finally, you could increase the number of connections allowed to the database. You will need to change the max_connections server option. Just use the command "SET MAX_CONNECTIONS = 500".
First you need to do is run this query:
SELECT user,host FROM mysql.user
WHERE super_priv='Y' AND
CONCAT(user,'@',host) <> 'root@localhost';
This will list all users that have SUPER privilege. Most users that do application-related DB processing do not require this privilege. According to the MySQL Documentation, those with SUPER privilege can do the following:
- Run CHANGE MASTER TO for controlling replication coordinates
- KILL or
mysqladmin kill
to kill threads belonging to other accounts
- PURGE BINARY LOGS to systemically delete binary logs
- Make configuration changes using SET GLOBAL to modify global system variables
- mysqladmin debug command
- enabling or disabling logging
- performing updates even if the *read_only* system variable is enabled
- starting and stopping replication on slave servers
- specification of any account in the DEFINER attribute of stored programs and views
- HERE IS THE MOST IMPORTANT ONE FOR YOUR PROBLEM: : Enables you to connect (once) even if the connection limit controlled by the max_connections system variable is reached.
You will need to login as root@localhost and revoke SUPER privilege as follows:
UPDATE mysql.user SET super_priv='N'
WHERE super_priv='Y' AND
CONCAT(user,'@',host) <> 'root@localhost';
FLUSH PRIVILEGES;
Once you do this, whenever all users flood mysql connections, only root@localhost
can login. After all, if everybody and his grandmother had SUPER privilege, this would bar root@localhost
from ever connecting ahead of everybody else. If max_connections is at 200 and you need to raise it to 300 without having to restart mysqld, you can dynamically increase the max_connections with this command:
mysql> SET GLOBAL max_connections = 300;
That will allow more connections effective immediately, but don't just arbitrarily increase the number on whim. You have to make sure mysql has enough RAM to accommodate the increase.
CAVEAT : If you change max_connections dynamically to 300, please put it in /etc/my.cnf
[mysqld]
max_connections=300
You can run mysqltuner.pl on your MySQL DB Server. If you do not have it, then run the following:
cd
wget mysqltuner.pl
perl mysqltuner.pl
The 3rd line under Performance Metrics has this
-------- Performance Metrics -------------------------------------------------
[--] Up for: 8d 20h 46m 22s (8M q [10.711 qps], 129K conn, TX: 90B, RX: 19B)
[--] Reads / Writes: 4% / 96%
[--] Total buffers: 2.1G global + 5.4M per thread (2000 max threads)
[OK] Maximum possible memory usage: 12.6G (80% of installed RAM)
See the 5.4M per thread? That is multipled by max_connections. In this example, that would be a maximum of about 10.8G of RAM. Therefore, each time you bump up max_connections, you should run mysqltuner.pl and check if you are pressing the OS for too much memory.
In any case, limiting who has SUPER privileges give such users opportunity to mitigate flooding mysqld with DB Connections.
Best Answer
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.
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
You do not have to restart mysql. Just run this command in the mysql client:
You may need more RAM on the box to accommodate additional DB Connections as well as a Decent Sized InnoDB Buffer Pool.