Solved

MYSQL Max Concurrent Connections

Posted on 2009-07-09
12
2,038 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
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.

 

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
 

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

733 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