MySQL dynamic max_connections setting

Put the following script in a .sh file, for example in /root/watch-mysql.sh

 1#!/usr/bin/bash
 2trap "exit" 0
 3
 4MAXCONN=1000 # set this to whatever base max_connections you have defined in your my.conf setting
 5while true; do
 6  #mysql -e "SHOW VARIABLES LIKE 'max_connections';"
 7  THREADS=`mysqladmin status | awk '{print $4}'`
 8  THREADSINT=`echo "$THREADS" | bc`
 9  #echo "Load is: $THREADS"
10  NEWMAX=$MAXCONN
11  if [[ $THREADSINT -ge 1 ]]; then
12    NEWMAX=1000
13  fi
14  if [[ $THREADSINT -ge 600 ]]; then
15    NEWMAX=2000
16  fi
17  if [[ $THREADSINT -ge 1400 ]]; then
18    NEWMAX=3000
19  fi
20  if [[ $THREADSINT -ge 2400 ]]; then
21    NEWMAX=4000
22  fi
23  if [[ $THREADSINT -ge 3400 ]]; then
24    NEWMAX=5000
25  fi
26  if [[ $NEWMAX -ne $MAXCONN ]]; then
27    mysql -e "set global max_connections = $NEWMAX;"
28    #echo "increased: $NEWMAX"
29    MAXCONN=$NEWMAX
30  fi
31  sleep 3
32done

If mysqladmin status gives error “connect to server at ’localhost’ failed”, make sure you have your credentials stored in ~/.my.cnf

I have a separate mysql server, runing PerconaDB inside docker, so in my case my ~/.my.cnf looks like this:

[client]
protocol=tcp
port=3306
user=root
password="secret"

You can run this script on startup by creating a systemd service or by using supervisor.

An easier setup is by runing a cron job every minute. If you do a while loop in a cron job, you will end up with unlimited proceses spawned and we don’t want that. Instead we will replace while with a for loop:

# replace `while true; do` with
for i in {1..20}; do

For loop for 20 iterations because each loop sleeps for 3 seconds. So 20 * 3 = 60 seconds assuming our code does not take too long to execute.

Now run command crontab -e and add a cron job for this:

* * * * * bash /root/watch-mysql.sh

Last modified on 2024-11-18