How to set max_connections in MySQL Programmatically

Question

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?

Answer 1

How to change max_connections

You 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) 

To OP

timeout related

I 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

UPDATED

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.