Too many connections? No problem!

Too many connections? No problem!

PREVIOUS POST
NEXT POST

Did that ever happen to you in production?

Just happened to one of our customers. Want to know what we did?

For demo purposes I’ll use sandbox here (so the ./use is actually executing mysql cli). Oh and mind it is not a general-purpose best-practice, but rather a break-and-enter hack when the server is flooded. So, when this happens in production, the problem is – how do you quickly regain access to mysql server to see what are all the sessions doing and how do you do that without restarting the application? Here’s the trick:

And here’s the result:

Credit for the gdb magic goes to Domas.

Few notes:

  • You would usually have one connection reserved for SUPER user, but that does not help if your application is connecting as a SUPER user (which is a bad idea anyway).
  • This worked for me on 5.0.87-percona-highperf, but use it at your own risk and better test it before you actually have to do it in production.
  • This example assumes you had less than 5000 max_connections configured 😉
PREVIOUS POST
NEXT POST

Share this post

Comments (32)

  • peter Reply

    Aurimas,

    One question one may have is how does GDB able to execute MySQL command “set max_connections=5000” – the thing is it is not MySQL but GDB command in this case which just happen to look the same which sets global variable with max_connections name to value of 5000.

    March 23, 2010 at 3:56 pm
  • Gerry Reply

    Great tip!

    The only caveat is that if you have a runaway app that it is trying to create as many connections as it can, you have to be *very* quick to get your connection before the run away application does.

    Does this method work for other global variables? It’d be great if you can set / reset ‘wait_timeout’ to automatically clean sleeping connections, in my experience, a small wait_timeout can close ‘sleeping’ connections before too many are created.

    My $.02
    G

    March 23, 2010 at 5:11 pm
  • Brian Moon Reply

    We always configure with settings like:

    set-variable = max_connections=1500
    set-variable = max_user_connections=300

    Where there are < 10 users that will connect to the server. This leaves connections open for super users. We typically have 2-3 application users for our mysql servers. So, with 3, only 900 connections could be used up by our users. You can additionally restrict per user connections if you want to tweak it even further.

    March 23, 2010 at 8:27 pm
  • Istvan Podor Reply

    @Brian:

    Yes,when I talked to one of my friend he just mentioned the same.

    @Aurimas:

    Great great article. Thanks for sharing! 🙂

    March 23, 2010 at 10:10 pm
  • Aurimas Mikalauskas Reply

    Brian,

    thanks – indeed, there are many things you can do upfront to avoid running into this, unfortunately most of the systems we end up working with are systems that were not configured by us just yet.

    March 24, 2010 at 3:16 am
  • Aurimas Mikalauskas Reply

    Gerry,

    yeah, that’s so true. An alternative to being quick is setting max_connections to a much higher value than you expect your app to reach.

    Regarding other global variables – it should work but symbols may not match the names in mysql server so you have to check the source to figure out what’s what. To change global wait_timeout and interactive_timeout one would set global_system_variables.net_wait_timeout and global_system_variables.net_interactive_timeout however each thread has a local variable set which I’m not sure how to modify.

    March 24, 2010 at 5:43 am
  • peter Reply

    Brian,

    Right this is generally good configuration advice. Also Aurimas mentioned there is connection reserved for SUPER user… why that would not be used ? Because there are many cases when we have to deal with badly configured systems 🙂

    March 24, 2010 at 10:44 am
  • Mark Callaghan Reply

    In sql/set_var.cc a function is called when max_connections is changed. I think it is resize_thr_alarm. You have not done that here.

    March 24, 2010 at 10:47 am
  • mikeh Reply

    Doesn’t this imply you’re running a build of MySQL with debugging symbols? Surely most people aren’t.

    March 24, 2010 at 7:19 pm
  • Aurimas Mikalauskas Reply

    Mark,

    any idea why it worked without calling that function?

    March 25, 2010 at 7:10 am
  • Mark Callaghan Reply

    It frequently works for me without calling that function. It occasionally doesn’t and I get obscure error messages that go away after I call it.

    March 25, 2010 at 7:16 am
  • Baron Schwartz Reply

    So, maybe once you’ve logged in you could issue the SQL SET command to make it get called and restore sanity.

    March 25, 2010 at 9:22 am
  • Aurimas Mikalauskas Reply

    Or just run:


    gdb -p $(pidof mysqld) \
    -ex "set max_connections=5000" \
    -ex "call resize_thr_alarm(5030)" -batch

    ?

    March 25, 2010 at 9:35 am
  • Baron Schwartz Reply

    Yeah, but if possible I’d be more comfortable letting things happen through the database’s own code path. Maybe paranoid, but …

    March 25, 2010 at 2:00 pm
  • peter Reply

    Aurimas,

    What I’d do is to change connections to higher value so you can connect and when change it again using MySQL way so it is resized as needed.
    I would be careful calling functions in GDB unless you’re sure it is safe – mutexes etc may cause unwanted side effects.

    March 26, 2010 at 8:53 am
  • domas Reply

    damn, all the dirty tricks get revealed, got to get a new bag now…

    March 27, 2010 at 8:43 am
  • Xupeng Reply

    I just rescued my dying MySQL server with the help of this useful tip, thank you 🙂

    March 31, 2010 at 5:42 am
  • Aurimas Mikalauskas Reply

    Xupeng, now go fix the configuration of your server so you never run into this again 😉 See comments above for tips on how to do that

    April 2, 2010 at 3:38 am
  • Aurimas Mikalauskas Reply

    Domas, if you could pull a trick showing us how to reset wait_timeout for all running threads from your hat, that would be something new!

    April 2, 2010 at 3:40 am
  • caraga Reply

    i was impress this thing additional to this i work an application that uses vb.net and i use a control backgroundworker as my asyncronous mysql connection to database to retrieve data every second after a long running application suddenly ived enctour a too many connection in my mysql server 5+.

    August 22, 2010 at 10:44 pm
  • Romeo Reply

    I have tried this command in our redhat server w/ mysql having “too many connection” problem & it doesn’t work.

    gdb -p $(cat data/mysql_sandbox5087.pid) -ex “set max_connections=5000” -batch

    > gdb -p $17391 -ex “set max_connections=5000” -batch
    GNU gdb Red Hat Linux (6.3.0.0-1.162.el4rh)
    Copyright 2004 Free Software Foundation, Inc.
    GDB is free software, covered by the GNU General Public License, and you are
    welcome to change it and/or distribute copies of it under certain conditions.
    Type “show copying” to see the conditions.
    There is absolutely no warranty for GDB. Type “show warranty” for details.
    This GDB was configured as “i386-redhat-linux-gnu”…set max_connections=5000: No such file or directory.

    /mysql/data/-batch: No such file or directory.
    (gdb)

    I have tried “–batch” and putting a complete command syntax for all still returns the above error.

    We can’t restart the DB or the server. What should I do next?

    November 21, 2011 at 12:52 am
  • Aurimas Mikalauskas Reply

    Romeo, this seems to be run incorrectly. You should not use PID as a variable i.e. $17391, instead try:

    gdb -p 17391 -ex “set max_connections=5000″ -batch

    Aurimas

    November 21, 2011 at 4:09 am
  • Romeo Reply

    I have tried that but it is giving this error:
    > gdb -pid 17391 -ex “set max_connections=5000” -batch
    set max_connections=5000: No such file or directory.
    (no debugging symbols found)
    Using host libthread_db library “/lib/tls/libthread_db.so.1”.
    0x082e674f in ?? ()

    November 21, 2011 at 7:43 am
  • Andrews Reply

    Hello, every one.

    Aurimas excellent article,

    I have read almost all the comments because a few days ago we had the same problem with our new web site, but I have a question about this change and I will share with you to see if you can answer it.

    When doing this kind of change? No need to change some other side of apache? Considering that increasing the number of connections is possible that the workload increase on the web server or not?

    November 23, 2011 at 3:13 pm
  • Aurimas Mikalauskas Reply

    Romeo, could it be the symbols (debug package) is not available for the version of MySQL you are using? Also, this may be gdb dependent, but are you sure you should be using -pid instead of –pid or -p ?

    Andrews – this article is only about changing max_connections when MySQL server is using all of the available slots and you need to log into it to fix it (say kill some mysql queries) without restarting mysql server. Apache httpd settings is entirely different (and actually more important) topic, but there’s no simple answer here i.e. it depends on why you are hitting the max connections limit in the first place.

    Aurimas

    November 24, 2011 at 1:11 am
  • Andrews Reply

    Aurimas, thanks a lot for your reply.

    Could you refer to me some article to check it out about this topic, the idea is make a balance between apache conections and MySQL max_conections.

    Thanks in advance.

    November 24, 2011 at 4:57 am
  • Aurimas Mikalauskas Reply

    I can’t think of anything right now- I’d have to search for them and read to verify they are relevant. I can’t write one either right now 🙂 But I found one article here written by Peter long time ago, which is actually very much related to it:

    http://www.mysqlperformanceblog.com/2007/02/05/why-do-you-need-many-apache-children/

    November 25, 2011 at 7:46 am
  • JoseM Reply

    Hello everyone;

    I have my website develop in PHP with MYSQL, these are MYSQL values:

    datadir=/data/mysql
    socket=/data/mysql/mysql.sock
    max_connections = 600
    wait_timeout = 60
    interactive_timeout=1800
    skip-name-resolve
    skip-bdb
    default-time-zone=America/Caracas
    log-slow-queries=/var/log/mysql-slow-queries.log
    long_query_time=15
    log-queries-not-using-indexes
    query_cache_type=1
    query_cache_size=32M
    thread_cache_size=30
    table_cache=4096
    join_buffer_size=6M
    key_buffer_size=12M
    server-id = 1
    log-bin=/data/mysql/drbd
    expire_logs_days = 5
    max_binlog_size = 100M
    [client]
    socket=/data/mysql/mysql.sock

    When I start the database service that starts smoothly with a low number of connections and so remains for a few minutes, but then so sopresiva connections to the database increases reaching the maximum of connections and I have to re-restart the server.
    I’ve checked the security aspect and not an attack, will have any suggestions or ideas of what might be causing thereby increase the number of connections?
    Thanks a lot

    March 15, 2012 at 10:47 am
  • Ashbeel Reply

    MySQL connect ERROR: Too many connections

    November 13, 2015 at 1:54 pm
    • Yakubpasha Reply

      Fix:
      1.Connect to DB(Open command prompt->mysql –u=root –p= Press enter
      2.run the following command once you connected to mysql
      ->SET GLOBAL max_connections = 10000;

      April 21, 2016 at 5:59 am
  • Aurimas Reply

    @Yakubpasha – this only works if you didn’t make a mistake of giving SUPER privileges to your application user. Because if you did – even the connection reserved for root user will be used. That’s where the gdb trick becomes useful.

    April 21, 2016 at 7:51 am
  • Thanks mate! Reply

    Best trick ever

    January 6, 2017 at 1:12 pm

Leave a Reply