Troubleshoot MySQL getting no connectivity issues

Source: Internet
Author: User
Tags connection pooling mysql command line

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 connectionRestart 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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.