Solved

MYSQL Max Concurrent Connections

Posted on 2009-07-09
12
2,009 Views
Last Modified: 2012-05-07
Can someone tell me if the variable setting
max_user_connections is the same as max. Concurrent users?

In my database the max_user_connections is set to 0 in variable settings,
but go to status in myphpadmin it is says Max Concurrent users = 11.

I need to increase this setting if it is only 11. Can someone tell me how to do this?

Thanks
0
Comment
Question by:danjen
  • 5
  • 4
  • 3
12 Comments
 
LVL 9

Expert Comment

by:michofreiha
ID: 24817186
In order to increase the number of concurrent connections to your MySQL server please do the following:
1- vi /etc/my.cnf
2- add the following line:  set-variable=max_connections=30
3- Restart mysql

Like that you'll increase the number of concurrent connections to 30...

By the way if you do not have this value(set-variable=max_connections) in my.cnf then the number of concurrent connections is unlimited

Regards
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24817189
My understanding is the the mysql ini value limits concurrent connections per user account when set to non-zero value:
http://dev.mysql.com/doc/refman/5.0/en/user-resources.html

Not sure on the max concurrent for phpMyAdmin, that may be for all connections.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24817259
The default for max_connections is 100, so should not have to increase on the mysql side.  Have to check what this signifies in phpMyAdmin and increase there is that is not per user connections.
0
 

Author Comment

by:danjen
ID: 24817286
what is the difference between max connections and max user connections? my site is going down periodically and my hosting company is saying that it is because I over the maximum number of concurrent users. So  I need to increase that.
0
 
LVL 9

Expert Comment

by:michofreiha
ID: 24817324
login as root (ssh), and in /etc/my.cnf change

max_user_connections=11

to

max_user_connections=30


rebooting to solve this problem is pretty dumb when you can just do

/etc/init.d/mysqld restart
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24817325
max connections == max number of clients connected concurrently, default is 100.
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_max_connections

max user connections == max number of connections a specific mysql account can have concurrently, default is 0 which is no limit (up to bounds of max connections).
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:danjen
ID: 24817511
ok - so I updated the max_user_connections, but phpmyadmin says 14 now. See below

Connections        ø per hour        %
max. concurrent connections       14       ---       ---
Failed attempts       1       0.85       0.16%
Aborted       130       109.88       20.73%
Total       627       529.98       100.00%

However the variable says set to 30
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 250 total points
ID: 24817702
which variable?
Remember max_user_connections is for individual, if you are not worried about your per user connections or especially if all your queries (web application for example) happen from the same mysql account, then leave the max_user_connections = 0 for no limit.

max_connections controls the total number of clients that can be connected to mysql period.  If that is set too low, then you will have issues if you have multiple users to the web application or connections to db are not be released quick enough...change max_connections to appropriate value.  Again this should have been 100 by default, so can change back to that unless your hosting provider doesn't allow it.
0
 

Author Comment

by:danjen
ID: 24817865
max_user_connections is set to 30, but I will set it back to 0 becuase that is what it was set to before. All of my queries do happen through 1 account, so it sounds like the number that I need to change is the max_connections. Any idea what the max.concurrent users means?
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24817954
I don't have phpMyAdmin on my system currently to check, but that may be a graphical way to set the max_connections system variable from phpMyAdmin or it can simply control the max number of concurrent users to phpMyAdmin itself for administration which you wouldn't need many as probably just you.
0
 
LVL 9

Assisted Solution

by:michofreiha
michofreiha earned 250 total points
ID: 24818147
Dear Sir,

please chek the below...it'll help you to understand everything

http://dev.mysql.com/doc/refman/5.0/en/too-many-connections.html

Regards
0
 

Author Closing Comment

by:danjen
ID: 31601788
Thank you very much! All of your answers were helpful
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now