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
Ultima modificare la 2024-11-18