Increase MySQL connection limit

MySQL’s default configuration sets the maximum simultaneous connections to 100. If you need to increase it, you can do it fairly easily:

For MySQL 3.x:

# vi /etc/my.cnf
set-variable = max_connections = 250

For MySQL 4.x and 5.x:

# vi /etc/my.cnf
max_connections = 250

Restart MySQL once you’ve made the changes and verify with:

echo "show variables like 'max_connections';" | mysql

WHOA THERE: Before increasing MySQL’s connection limit, you really owe it to yourself (and your server), to find out why you’re reaching the maximum number of connections. Over 90% of the MySQL servers that are hitting the maximum connection limit have a performance limiting issue that needs to be corrected instead.

Comments

  1. jamtech338 says

    Hello,

    I have MYSQL version: Mysql Ver 14.12 Distrib 5.0.51a, for suse-linux-gnu (i686)

    and I need to increase my connection limit from 100 to 250-500 and I wanted to know which command to type.

    This is urgent!
    Thanks

  2. says

    jamtech338 – Did you actually read the post above? :-)

    Open your MySQL configuration file in your favorite text editor and set the variable “max_connections” to the value you need. Once that’s done, restart your MySQL daemon with /etc/init.d/mysqld restart (the init script path may differ on your system).

  3. jamtech338 says

    Hi Major Hayden,

    Thanks for your quick response.

    Truth is, I don’t know anything about MYSQL however, I need to change the default max connection of 100 to something else.
    From the command line using PuTTY to connect to MySQL which is on Linux box. I typed both the following:

    # vi /etc/my.cnf
    max_connections = 250

    max_connections = 250

    and both returned and error message:
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘max_connections = 250′ at line 1

    With that said, can you please tell me how to open MYSQL in notepad because I dont have a G.U.I. tool; I install both mysql-workbench-oss-5.1.18a-win32 and mysql-gui-tools-5.0-r17-win32 on my Windows XP computer and I am unable to connect successfully due to ‘access denied errors – I use the same password to connect with PuTTY.

    Jamtech

  4. says

    jamtech338 – I would highly recommend getting in contact with a linux systems administrator who can help you. There may be some underlying issues that may not be fixed by the max_connections adjustment (and the adjustment may make things worse).

  5. Chris S says

    jamtech338 – is this under the [mysqld] section of your my.cnf?

    You may be better copying and pasting your my.cnf at http://pastebin.com, and popping the link on here so we can give it a once over.

    Chris

  6. says

    Hi, I was sent this way by @standaloneSA.

    Based on the error and the “at line 1″ it sounds to me as if you are typing these commands at a MySQL prompt (that is to say, at the prompt of the interactive MySQL query tool) rather than a command line prompt.

    Can you repeat the procedure you use to connect, and then type ‘show databases;’ (without the quotes, with the semi-colon) and report back what is returned? If you get a “box” showing you all of the databases on your machine then you are in a MySQL prompt.

    The quick and easy way to edit your configuration file from here is to type ‘system bash’ (again, without quotes). This will put you on a proper command line, from which you will be able to run the `vi` command to edit your configuration file.

    Beware, on some Linux distributions (certainly Debian-based_distros such as Ubuntu) the command should be `vi /etc/mysql/my.cf`.

    When you are done with editing `/etc/init.d/mysql restart` (Debian-based) or `/etc/rc.d/init.d/mysql restart` (most RPM-based, RHEL, Centos, etc.) will restart MySQL.

    Of course, you should discover *why* you need to increase these concurrent connections. If whatever is failing now fails again, only after a little more time, you’re likely to have an application that is leaking database connections.

  7. jamtech338 says

    Hi Sam,

    Many Thanks for your input.

    You are correct I was in fact in the MYSQL prompt.
    I think I am now at the correct command line

  8. jamtech338 says

    Sam,

    When I type ‘vi /etc/my.cnf ;

    It gives me options to Open, Edit, Recover, Delete, Quit and Abort

  9. says

    IIRC that is usually because a previous in-progress edit was interrupted (system reboot, terminal connection ended, etc, etc.).

    If you’ll excuse my crude way of putting it, I’m wary of giving you enough rope to hang yourself with here. By recovering the file (from the “backup” copy vi kept when it was nuked before) and then saving this over a copy that has been edited in the meantime you may regress a previous change or completely bork your MySQL configuration. Or everything might be fine.

    That said, if I were you I would:

    `cp /etc/my.cf $HOME/my.cf.backup.20100127`

    to create a backup copy of the existing configuration file in your home directory. Then edit the file in vi (discarding the changes held in the recovery version of the file), save, restart. this page may help you decide what is most appropriate to do at the vi prompt.

    If you manage to bork MySQL in any way then:

    `cp $HOME/my.cf.backup.20100127 /etc/my.cf` and a restart of MySQL should get you out of trouble.

    Finally, pride aside, please evaluate how valuable this system and anything that hangs off of it is against your knowledge and confidence in making these changes. I don’t want to put you off, but then again I don’t want to help you hose something you don’t quite understand!

  10. jamtech338 says

    Sam,

    From the command line I typed:

    vi /etc/my.cnf
    set-variable = max_connections = 200

    and it took me to a line 120,1 with the ‘#’ in green
    #bdb_max_lock = 100000

    is this the where I need to make the change from 100000 to 200000?

    Jamtech338

  11. says

    jamtech338 – You may be comfortable using ‘nano’ rather than vi since it’s a little easier for a beginner to navigate. However, “set-variable” is only relevant for old versions of MySQL. You would be able to just use this in your configuration:

    max_connections = 200

    It’s extremely important to find out why you need more connections. You may end up pushing your server into its swap memory and it will become highly unstable (or crash). Consult with a sysadmin/DBA and review:

    http://rackerhacker.com/2008/06/24/mysql-error-1040-too-many-connections/

  12. says

    This is not going to end well. @jamtech338 – please take RackerHacker’s advice and get expert help before making changes to your MySQL configuration.

  13. jamtech338 says

    Major Hayden,

    THE REASON FOR INCREASING THE CONNECTIONS

    I work in a call center and Monday we experienced a high volume of calls approx. 100 in the Que and Agents were getting an error stating that: ‘MySQL connect ERROR: Too many connections’ once the call volume died down normality was immediately restored.

    So in the future should we get a high volume of calls then there will be enough connections to go around.

    from the command this is what I did:
    1. nano> Enter
    2. max_connections = 200

    what would be my next step?

    Jamtech338

  14. jamtech338 says

    Okay Guys,

    I am taking your advice, anyone here does this sort of thing as a professional/consultant?

    I am now looking to hire someone.

    Jamtech338

  15. Arun says

    If you’re using debian, open (use an editor you’re comfortable with) /etc/mysql/my.cnf. Find the line starting with max_connections. Copy the line and comment it out – add a # at the beginning. Paste the line below the commented line and change the value or max_connections to something appropriate for your environment. Save the file. Restart mysql – /etc/init.d/mysql restart.

    To see if the changes, login to MySQL monitor, and type

    show variables like ‘max_connections’;

    That should show you the new value.

  16. jamtech338 says

    Arun,

    I am not using Debian in fact, I am using:

    MYSQL version: Mysql Ver 14.12 Distrib 5.0.51a, for suse-linux-gnu (i686)

    Jamtech338

  17. says

    jamtech338,

    As Major says:
    It’s extremely important to find out why you need more connections. You may end up pushing your server into its swap memory and it will become highly unstable

    Increasing the number of connections will require more resources to service. Unless you have a clear understanding of the consequences then this may be detrimental to your database server and/or application server performance.

    It might be more appropriate, for example, to change the connection pooling settings of your call center software to, for example, terminate idle connections more quickly, or delay rather than refuse connections until the pool has idle connections.

    In short: there is more involved here than making a configuration file change.

    If the software that has the problem has a vendor I would call them in the first instance. If you don’t have a maintenance contract, now might be the time to start one as the rates may be comparable to one-off consultancy fees and you’ll get on-going support. Failing that, you need to hire someone to analyze the problem and suggest a solution. Whilst you could hire someone to do this remotely, experience says you’re better off finding someone local to you and have them come in to do the work. That way you can also quiz/learn from them too.

    I’m sorry you’re not getting the quick fix you had hoped for, but having been in this game for nigh-on a decade I can confidently say that understanding the problem in detail is preferable to replacing one problem with another with a “simple” change.

    Best of luck with your endeavors.

    Sam.

  18. Arun says

    One more thing to be concerned about is the wait_timeout. It’s 8hrs by default IIRC. Reduce it to a sane value according to your needs, else you’ll find a lot of lingering connections that’ll eat up your max_connections.

  19. Arun says

    Jamtech338,

    Google tells me that on Suse, my.cnf resides in /etc, so edit /etc/my.cnf and change the values. Don’t change things blindly as it’ll eat up resources on your server quickly and lead to thrashing. Use something like mysql-tuner.pl (google for it) to see if the values are safe. But mysql has to be running for at least a week, before the script can tell you if the current values are safe.

  20. says

    thanx but this wont help me.
    I use win2003 server, mysql5, php5, apache2.2 w/SSL.
    I set it to larger than default (100), doesn’t work.
    I set it to unlimited (0), doesn’t work.
    I close using mysql_colse($connection) on every query, doesn’t work.
    stil mad :((

  21. Felix says

    issues with my app server … run mysql 5.5 , tomcat 6 and a J2ee app… have opened system files to 30,000 and mac mysql connection 1550.

    issue is that the app received like 5 million records a day from gps units .. note this is increasing day by day?

    Any help appreciated

Trackbacks

Leave a Reply

Your email address will not be published. Required fields are marked *