I would like to ask you a question to help me solve mySQL problem.
I will write down the current symptoms first, and then write down how I handled it in my own way.
Finally, I would appreciate it if you could tell me how to solve the problem.
[Current situation]
Currently, we are trying to obtain the necessary data from within DB using MySQL (5.6.29) .
We use a simple code like "select * from table where xxx" to where xxx".(Actually, we only read column(int) that matches where conditions from table with 7 million lines.) We have verified that we can use this code to access DB and obtain accurate data from our own GO program.
However, within this GO Program, you will need to change the where conditions hundreds of times and obtain additional data each time.After accessing DB many times, Error 1040 (Too many connections) will eventually appear and the GO program will be terminated in the middle of the calculation.
[How to deal with it yourself]
Therefore, when I did my own research on the web, I was advised that changing the conditions of my.cnf file would solve this problem, so I inserted the following two points into my.cnf.
(a) max_connections=200
(b) wait_timeout=100
We have changed the above numbers (200,100) (but we only try numbers up to 1000). Also, I think it doesn't matter, but just in case, "flush query cache;" is also included in the code.However, we are experiencing the same problem now.
[Question]
I looked for it on the web, but I couldn't find a solution to this problem except for the above two points.
I have two questions.
(1) I did it myself, but if I change the number (200,100) to a large number (e.g., 200000), will the problem be solved?In that case, will there be another problem?
(2) Is there a solution other than the above two conditions?
I'm very sorry, but I'd appreciate it if you could teach me.
Thank you for your cooperation.
When using MySQL from a client program,
That's the flow.The error indicates that the number of connections that are open at the same time as Too many connections exceeds the number configured in max_connections.At this time,
increase max_connections by to allow many connectionswait_timeout is shortened to quickly disconnect from the server sideand so on.Therefore, if wait_timeout is to be adjusted for the above error, it will be shortened, but the client is responsible for disconnecting it at the right time, and the timeout is a precautionary measure in case of a problem.If this error occurs constantly, you should first investigate the cause and see if it is used incorrectly or if there is another solution.
max_connections also requires that much resources to maintain many connections, so the limits vary from environment to environment.Also, by keeping the number down to a sufficient number, you will be more likely to notice anomalies in the above errors.This value is well introduced and should be adjusted to meet the requirements of the environment and applications that you want to run, not .
In this case, it seems that this error occurred while running one program, so I think there is a so-called resource leak that is creating many new connections without reusing or disconnecting.Of course, it's a good idea to increase max_connections...
If you are asking questions about that area again, you can ask questions with a
I don't know if the problem is completely resolved, but
Considering that the problem has not occurred even if we calculate 100 times the amount of data we used when the above problem occurred, we believe that the problem can be avoided by using the method shown below.I'll leave it here for your reference only.
In conclusion, the conditions of my.cnf are as follows.
(a) max_connections=500
(b) wait_timeout=3600
(After saving, reflect the condition in service mysql restart.This is a Linux (Debian) case.)
The reason why I set the above conditions is that after further research on the web, people who had the same problem
max_connections is set from 200 to 500 and
The wait_timeout was often set from 10 to 3600.
As for max_connections, there are two groups: the higher the number, the better, and the problem.Wait_timeout was similar.
If you don't know how to set it up, we recommend that you refer to the following site.It is written very carefully.http://koexuka.blogspot.com/2010/03/blog-post.html
Sorry for the trouble.Thank you for your continued support.
© 2024 OneMinuteCode. All rights reserved.