Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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

This comment has been minimized.

Copy link
Owner Author

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

This comment has been minimized.

Copy link

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
You can’t perform that action at this time.