I have a server where a lot of users will connect to it and use a database there, and I am using MySQL. I know that the default number of max_connections in MySQL is 100 or 150 but I am sure I need way beyond that number, therefore I used the following to increase the number:
SET global max_connections = 1000000
Now I try to check the max_connections as follows:
show variables like 'max_connections'
It gives me the following:
max_connections; 100000;
Which is a sign that it succeeded (unless I am understanding it wrong). When my users start to connect I am receiving an error from the server when the number of connected users exceeds 110. The error is:
error connecting: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
Why am I getting this error, and how to fix it?
max_connectionsYou can change max_connections while MySQL is running via SET:
mysql> SET GLOBAL max_connections = 5000;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE "max_connections";
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 5000 |
+-----------------+-------+
1 row in set (0.00 sec)
timeout relatedI had never seen your error message before, so I googled. probably, you are using Connector/Net. Connector/Net Manual says there is max connection pool size. (default is 100) see table 22.21.
I suggest that you increase this value to 100k or disable connection pooling Pooling=false
he has two questions.
Q1 - what happens if I disable pooling
Slow down making DB connection. connection pooling is a mechanism that use already made DB connection. cost of Making new connection is high. http://en.wikipedia.org/wiki/Connection_pool
Q2 - Can the value of pooling be increased or the maximum is 100?
you can increase but I'm sure what is MAX value, maybe max_connections in my.cnf
My suggestion is that do not turn off Pooling, increase value by 100 until there is no connection error.
If you have Stress Test tool like JMeter you can test youself.
You can set max connections using:
set global max_connections = '1 < your number > 100000';
This will set your number of mysql connection unti (Requires SUPER privileges).
Source: Stackoverflow.com