Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
Quick and dirty monitoring of mysql connections
#!/usr/bin/env bash
# 5 minutes between alerts
# Track when we sent the last alert
while true; do
NUMBER_CONNECTIONS=$(mysql --defaults-extra-file=/data/.prod.cnf -sNe "select count(*) as connection_count from INFORMATION_SCHEMA.PROCESSLIST;")
RIGHTNOW=$(date +"%s")
if [ "$NUMBER_CONNECTIONS" -ge "750" ] && [ "`expr $RIGHTNOW - $LAST_ALERT`" -ge "$SECONDS_BETWEEN_ALERTS" ]; then
CONNECTIONS_PER_USER=$(mysql --defaults-extra-file=/data/.prod.cnf -e "select USER, HOST, count(*) as connection_count from INFORMATION_SCHEMA.PROCESSLIST group by USER order by connection_count desc;")
curl -X POST --data-urlencode "payload={\"username\": \"RDS Police\", \"text\": \"\nTOTAL CONNECTIONS: $NUMBER_CONNECTIONS\n$CONNECTIONS_PER_USER\n\", \"icon_emoji\": \":alert:\"}"
sleep 5
description "Monitoring Database Connections"
start on filesystem or runlevel [2345]
stop on runevel [!2345]
respawn limit 5 2
end script
Description=Monitoring Database Connections

This comment has been minimized.

Copy link
Owner Author

@fideloper fideloper commented Dec 19, 2018

Monitoring your DB connections:

Quick and dirty (referred to as in the systemd / init configs):

  1. pings a database every 5 seconds asking for total # of connections.
  2. Tracks the last time an alert was sent
  3. If total connections is over 750 and an alert was sent 5 minutes or more ago, then send slack an alert
  4. The alert content shows the number of connections per user (and host). This is convenient when you use one user per host against your database.

Keep Script Running

You can use init or, more likely, systemd to keep the bash script alive and running:


Add file dbmon.conf to /etc/init/db.conf and then:

# Test syntax
init-checkconf /etc/init/dbmon.conf

# Check status and start
sudo service dbmon status
sudo service dbmon start



Add file dbmon.service to /lib/systemd/system/dbmon.service and then:

sudo systemctl enable dbmon
sudo systemctl status dbmon
sudo systemctl start dbmon



This comment has been minimized.

Copy link

@matthewsuan matthewsuan commented Dec 20, 2018

this checks db every 5 secs, right?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment