I recently created a snapshop monitor for Mysql.
It gives nice snapshots every 15 minutes of Mysql (server) activities.
It is a useful tool for finding problems especially from night run as you can go back in time.
The output consists of:
1 Memory stats of the server
2 Number of SQL queries since last snap
3 Processes running when snap is taken
A snapshot of the snapshop monitor output:
Fri Aug 19 03:31:10 CEST 2022 total used free shared buffers cached Mem: 64383 63785 597 2 0 45306 -/+ buffers/cache: 18478 45905 Swap: 32767 75 32692 SQL queries since last snap 1011240 Id User Host db Command Time State Info 36790 KalleAnka toossedwvetl3-i:52859 TRANSFORM Query 1461 Sending data create temporary table LKLP_Selection (primary key(VBELN, POSNR)) as (\n selec 36798 KalleAnka toossedwvetl3-i:52868 RAW Query 4193 updating DELETE FROM TRANSFORM.Finance_Pricing WHERE Valid_From_Date >= 36848 KalleAnka toossedwvetl3-i:52930 TRANSFORM Query 56 copy to tmp table ALTER TABLE PTD_DTR_OL ADD INDEX (KUNNR, VKORG) 36967 manager %:53112 TRANSFORM Query 124 Creating sort index INSERT INTO TRANSFORM.Logistics_MaterialMovements_RollingQuantity_Periodic(\nSELECT\nA.W 36977 KalleAnka toossedwvetl3-i:53122 TRANSFORM Query 4175 Sending data INSERT INTO TRANSFORM.Sourcing_PurchaseOrders\nSELECT\nA.AFNAM as 36992 KalleAnka toossedwvetl3-i:53137 TRANSFORM Query 4170 Sending data INSERT INTO PPV_INVOICED_Temp (\n\t SELECT \n\t A.BELNR as 38395 KalleAnka toossedwvetl3-i:55708 TRANSFORM Query 2857 Sending data INSERT INTO TRANSFORM.PKBridgeMaterialCost 38554 KalleAnka toossedwvetl3-i:55976 ACTADW Query 2449 executing LOAD DATA LOCAL INFILE 'AFRU.CSV' replace INTO TABLE ZDTSQRYAFRU FIELDS B 38566 KalleAnka toossedwvetl3-i:55981 A… |
The bash code:
#!/bin/bash mv /home/tooljn/mymonlog/0mymon.txt "/home/tooljn/mymonlog/$(date '+%Y%m%d%H%M')-mymon.txt" find /home/tooljn/mymonlog -type f -mtime +32 -delete while true do date >> /home/tooljn/mymonlog/0mymon.txt free -m >> /home/tooljn/mymonlog/0mymon.txt mysql -e "show global status like 'Queries'" | grep Queries >> /home/tooljn/mymonlog/mymonQ.txt topl="$(head -n 1 /home/tooljn/mymonlog/mymonQ.txt | grep -oE '[^[:space:]]+$')" botl="$(tail -n 1 /home/tooljn/mymonlog/mymonQ.txt | grep -oE '[^[:space:]]+$')" queries=$((botl-topl)) echo "SQL queries since last snap $queries" >> /home/tooljn/mymonlog/0mymon.txt (mapfile -n 2 < /home/tooljn/mymonlog/mymonQ.txt; if ((${#MAPFILE[@]}>1)); then echo "This file has more than 1 line." fi) mysql -e "show processlist" | sed '/Sleep/d' | sed '/root/d' | sed -e "s/[[:space:]]\+/ /g" | cut -c -150 >> /home/tooljn/mymonlog/0mymon.txt echo "- - - - - - - - - - - - - - - - - - - - - - \n" >> /home/tooljn/mymonlog/0mymon.txt sleep 15m done |
