MySQL dynamic max_connections setting

Put the following script in a .sh file, for example in <code>/root/watch-mysql.sh</code><div class=highlight><pre tabindex=0 style=color:#d0d0d0;background-color:#202020;-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-bash data-lang=bash> 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 </code></pre></div>If <code>mysqladmin status</code> gives error “connect to server at ’localhost’ failed”, make sure you have your credentials stored in <code>~/.my.cnf</code>I have a separate mysql server, runing PerconaDB inside docker, so in my case my <code>~/.my.cnf</code> looks like this:<pre tabindex=0><code>[client] protocol=tcp port=3306 user=root password="secret" </code></pre><hr>You can run this script on startup by creating a <a href=https://linuxconfig.org/how-to-run-script-on-startup-on-ubuntu-20-04-focal-fossa-server-desktop target=_blank rel=noopener>systemd service</a> or by using <a href=https://www.digitalocean.com/community/tutorials/how-to-install-and-manage-supervisor-on-ubuntu-and-debian-vps target=_blank rel=noopener>supervisor</a> .<hr>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:<pre tabindex=0><code># replace `while true; do` with for i in {1..20}; do </code></pre>For loop for 20 iterations because each loop sleeps for 3 seconds. So <code>20 * 3 = 60</code> seconds assuming our code does not take too long to execute.Now run command <code>crontab -e</code> and add a cron job for this:<pre tabindex=0><code>* * * * * bash /root/watch-mysql.sh </code></pre><hr width=100% id=EOF>Last modified on 2024-11-18