Mysql – Too Many Connections

MySQLperformance

I'm using MATLAB to feed the database but after a time I'm getting a too many connections error. I always using opendb and closedb in pair. Is it to fast the feeding? Do I have to wait after I inserted a row?

Connecting to  host = localhost  user = root  password = ***
Uptime: 1591  Threads: 149  Questions: 2956294  Slow queries: 0  Opens: 196391  Flush tables: 1  Open tables: 81  Queries per second avg: 1858.135
Current database is "forwind"
Connection closed, current status:
No connections open
add row
Connecting to  host = localhost  user = root  password = ****
Uptime: 1591  Threads: 150  Questions: 2956402  Slow queries: 0  Opens: 196491  Flush tables: 1  Open tables: 75  Queries per second avg: 1858.203
Current database is "forwind"
Connection closed, current status:
No connections open
add row

Best Answer

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.

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

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

  3. 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".

Related Question