Skip to content

Instantly share code, notes, and snippets.

@fideloper
Created December 19, 2018 19:48
Show Gist options
  • Save fideloper/a58a6aecbd4f35caa6d5487bd1128f29 to your computer and use it in GitHub Desktop.
Save fideloper/a58a6aecbd4f35caa6d5487bd1128f29 to your computer and use it in GitHub Desktop.
Quick and dirty monitoring of mysql connections
#!/usr/bin/env bash
# 5 minutes between alerts
SECONDS_BETWEEN_ALERTS=300
# Track when we sent the last alert
LAST_ALERT=0
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 https://hooks.slack.com/services/xxx/xxx/xxx --data-urlencode "payload={\"username\": \"RDS Police\", \"text\": \"\nTOTAL CONNECTIONS: $NUMBER_CONNECTIONS\n$CONNECTIONS_PER_USER\n\", \"icon_emoji\": \":alert:\"}"
LAST_ALERT="$RIGHTNOW"
fi
sleep 5
done
description "Monitoring Database Connections"
start on filesystem or runlevel [2345]
stop on runevel [!2345]
respawn
respawn limit 5 2
script
/opt/dbmon.sh
end script
[Unit]
Description=Monitoring Database Connections
[Service]
Restart=on-failure
ExecStart=/opt/dbmon.sh
[Install]
WantedBy=multi-user.target
@fideloper
Copy link
Author

fideloper commented Dec 19, 2018

Monitoring your DB connections:

Quick and dirty

connections.sh (referred to as dbmon.sh 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:

Init

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

Reference: https://serversforhackers.com/c/process-monitoring-with-upstart

Systemd

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

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

Reference: https://serversforhackers.com/c/process-monitoring-with-systemd

@matthewsuan
Copy link

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