Hello: Can someone tell me how I can check for number of open connections on a mysql db? I have a application that uses a mysql db and at times this app freezes up and I notice that the error I get is that there are too many open connections. What could the reason be? Thanks for the help.
A
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
You can increase this value in main config file (e.g., /etc/my.cnf) using this syntax:
[mysqld]
set-variable=max_connectio
http://dev.mysql.com/doc/r
you can see what this is currently set to by using
show variables like 'max_connections';
if you have mysql administrator you can see who is currently connected to the database.
Here is some helpful information on the command.
http://dev.mysql.com/doc/r
Hello Scott: That is the command I used, this is the kind of output I get...What does this mean?
Id | User | Host | db | Command | Time | State | Info |
+-----+------+------------
| 81 | root | localhost:32838 | rcable | Sleep | 1557 | | |
| 147 | root | localhost:32896 | rcable | Sleep | 1314 | | |
| 148 | root | localhost:32897 | rcable | Sleep | 1314 | | |
| 187 | root | localhost:32938 | rcable | Sleep | 1162 | | |
| 195 | root | localhost:32946 | rcable | Sleep | 1162 | | |
| 196 | root | localhost:32947 | rcable | Sleep | 1161 | | |
| 197 | root | localhost:32948 | rcable | Sleep | 1162 | | |
| 198 | root | localhost:32949 | rcable | Sleep | 1162 | | |
Thanks,
A
to see the max connection you need to use the command
show variables like 'max_connections';
What are you connecting with? it seems to be weird to have so many connections from the same box all sleeping - especially fro the amount of time they have been dormant (that is measured in seconds)
It seems that you need to clean up your connections. The max connections may not solve your problem if your output looks like what you have above
Hi,
Normally applications open and then release connections, I do not program with jsp but I have seen something similar using web services and .net where connection pooling was activated - and provided the connection to the web service was still open then there were multiple inactive connections. Are your jsp pages closing the database ceonnection once they have finished loading?
The connections you have listed are all for the root user. Is your JSP application using the root account? It would be a good idea to create an application-specific account. This will allow you to track the connections more effectively, but more importantly, control access.
JSP applications will generally create a seperate thread for each HTTP request. If your web container (Tomcat?) is serving 10 pages, for instance, you could have multiple connections.
@Venabili - I am not a MySQL expert but I know that my comment at http#18072802 answers the first part of this question. I suggest the other part can be answered by Raynard7 {http:#18072889} or {http:#18072976} and todd_farmer {http:#18074486}. A three way split would seem appropriate to me.
I hope this helps. Thanks for all of your time cleaning and helping here. Let me know if there are any questions.
bol
Business Accounts
Answer for Membership
by: b0lsc0ttPosted on 2006-12-04 at 15:17:47ID: 18072802
aej1973,
One command you can use is ...
show processlist;
Let me know if you have any questions or need more information.
b0lsc0tt