Troubleshoot MySQL getting no connectivity issues

The application uses MySQL in the process. Tip The database connection is not available.

For example:

Connections could not being acquired from the underlying database!

by command:

Show status where Variable_name = ' Connections '

Check the current MySQL connection, and found that it was used almost.

This is especially true in research and development environments where many machines use a single database, or if the network is unstable.

　　reason :

• MySQL's connection is limited. The default number of connection is only 1024.
• Common applications now use connection pooling to manage database connections. It even uses a read-write separation, a sub-library table. Causes an app to start with a 100 or so database link.
• If the network jitter and so on, the link applied to MySQL has been disconnected. The app will reapply to MySQL for connection. Then the previous connection application is no longer in the pipeline, but MySQL waits a long time before releasing the connection that are no longer in use.

solve the problem of insufficient connection： Restart MySQL。 This is not a good way, research and development test environment can play like this, production environment must not be such a child. Maximum connection size increase。 MySQL default number of connections is only 1024. The increase of the number of connections can be very effective to alleviate the problem. In MySQL's main profile my.cnf, locate and modify:

Max_connections        2048

Restart MySQL after execution.

Small Wait TimeAs mentioned above, MySQL will wait for a period of time before releasing the useless connection. 　　We can adjust this value. Execute the command first and look at the wait time:

Show variables like "%timeout%"

As shown:

The default is 28,800 seconds, 8 hours ... Let's turn these 2 values to small. Input (or navicate, etc.) on the MySQL command line:

Set global Wait_timeout=60;set global interactive_timeout=60;

If you execute the above command in Navicate, you will need to close the current database connection before you see the effect.

Perfect monitoring of production environmentIn order to avoid this problem on the line, the MySQL monitor to add the current number of connections this item, more than a certain threshold to alarm. Reference: http://stackoverflow.com/questions/4284194/terminating-idle-mysql-connectionshttp://dev.mysql.com/doc/refman/ 5.0/en/server-system-variables.html#sysvar_wait_timeouthttp://dev.mysql.com/doc/refman/5.0/en/ Server-system-variables.html#sysvar_interactive_timeout

Troubleshoot MySQL getting no connectivity issues