Navicat Frequently Asked Questions
In our daily development process, in general, do not directly use the command line to operate MYSQL database, but will choose some graphical interface to help us to carry out such operations, commonly used are: SQLyog (Logo also dolphins), Navicat, or directly using an editor that comes with a graphical interface tool. I am here to develop using Navicat, he appeared in the daily use of the problem:
Too Many Connections – 1040
In Navicat’s interface, this is a very annoying message box, people often collaborative development time. There are two reasons:
First, the default maximum number of connections Mysql database are: 151,
The second is for single people to develop the project, although we are not at the same time in connection with more than 10, 100 more than enough in theory, but in addition we are using connection, there is a great part of Sleep connection, this It is the real culprit.
Analysis to the root of the problem, we need to prescribe the right medicine, in order to address:
Modify Mysql Maximum Number of Connections
First see what the current maximum number of Mysql connections is:
show variables like '%max_connections%';
I’ve modified it here, so it’s 1000, children’s shoes that haven’t been changed should still be 100,
And then view the mysql service from start to now, the same time the maximum number of concurrent connections:
show status like 'Max_used_connections';
For Mysql’s maximum number of connections, set a higher value at the first time, later in the process of use, the cycle query Max_USED_Connections then determine the maximum number of connections that are best suited to the current project based on its value and server performance.
Modify the maximum number of connections in two ways
Use sql statement (to take effect immediately, but after the restart the server failure):
set global max_connections = 1000;
/Etc/my.cnf modified. Add max_connections = 1000 permanent. After the restart to take effect
But changing the maximum number of connections will only solve the problem on the surface, so will sleep connections be more as we increase the number of developers. Do we have to change to 10000 if we reach the maximum of 1000 again? This is obviously very undesirable. So we need to treat not only the symptoms, but also the symptoms. Kill the extra Sleep connections is a cure for the symptoms.
Kill the Sleep Connection
We can see all the current connection status by show_processlist command
Can be found, Sleep connections majority.
Mysql database has a property wait_timeout connection is sleep maximum survival time, the default is 28800 s, in terms of hours is 8 hours, my God! This is too long! Seriously affect performance. Since the establishment of equivalent work today all over without a closed connection will not be cleaned up.
Execute the command:
show global variables like '%wait_timeout';
We modified it to a suitable value, here I changed it to 250s. Of course, it can also be modified in the configuration file, adding wait_timeout = 250. This value can be modified according to the project’s needs, in s. I configured 240s here in conjunction with navicat’s timeout request mechanism.
Execute the command:
set global wait_timeout=250;
In this way, we can solve the problem Too Many Connections from the fundamental
When we use Navicat, sometimes after querying the data once, we go to code and wait for the next time we come back to open the table: is preparing… , this query may consume more than 20s of time, which is intolerable.
This time we need to find ways to solve him, first of all I am thinking of connection mysql connection is cleaned up, now being prepared by the complex … and at the same time through the show processlist linux command to view the connection status found that not. This request does not establish a connection and mysql connection is preparing the first 10 seconds.
So my analysis might be a communication connection between the database server and broke navicat led, according to navicat documentation in connection Advanced Settings there is a gap remains connected (official document – to stay connected via ping you can edit when the box is provided between the ping and ping interval) long configuration: it is set to 240s. Thus every 240s, the software will send a heartbeat request as the server to ensure that the connection is not disconnected. So we do not appear being prepared. . . And other words of