victorpendleton

SELECT blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_statement,
blocking_activity.query AS current_statement_in_blocking_process
FROM  pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity
         ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks 

victorpendleton

SELECT n.nspname
, c.relname
, ROUND(SUM(pg_total_relation_size(c.oid))/1024/1024, 2) AS MB 
FROM pg_class c INNER 
JOIN  pg_namespace n 
ON c.relnamespace = n.oid 
WHERE relkind = 'r' 
AND n.nspname IN ('')
AND c.relname = ''
GROUP BY 1, 2 

victorpendleton

SELECT n.nspname
, c.relname
, ROUND(SUM(pg_total_relation_size(c.oid))/1024/1024, 2) AS MB 
FROM pg_class c INNER 
JOIN  pg_namespace n 
ON c.relnamespace = n.oid 
WHERE relkind = 'r' 
AND n.nspname IN ('') 
GROUP BY 1, 2 
ORDER BY 3;

victorpendleton

SELECT n.nspname, c.relname
, ROUND(SUM(pg_total_relation_size(c.oid))/1024/1024, 2) AS total_bytes 
FROM pg_class c INNER 
JOIN  pg_namespace n 
ON c.relnamespace = n.oid 
WHERE relkind = 'r' 
AND n.nspname NOT LIKE 'pg%' 
GROUP BY 1, 2
ORDER BY 2;

victorpendleton

# https://www.rabbitmq.com/rabbitmqctl.8.html

# Create virtual host
sudo rabbitmqctl add_vhost host_name

# Create user
sudo rabbitmqctl add_user user password

# Set tags for user
sudo rabbitmqctl set_user_tags user [administrator|management]

victorpendleton

SELECT n.nspname
, ROUND(SUM(pg_total_relation_size(c.oid))/1024/1024, 2) AS MB 
FROM pg_class c INNER 
JOIN  pg_namespace n 
ON c.relnamespace = n.oid 
WHERE relkind = 'r' 
AND n.nspname NOT LIKE 'pg%' 
GROUP BY 1 
ORDER BY 2;

victorpendleton

#!/bin/bash
RUNTIME=$(date +'%F %T'); # Get date/time
NUMOFCPUS=$(grep -c ^processor /proc/cpuinfo); # Grab the number of CPUs
THRESHOLD=$(echo "${NUMOFCPUS}*.5" | bc); # Calculate the threshold
CPU=$(top -bn1 | grep load | awk '{printf "%.2f\t\t\n", $(NF-2)}'); # Grab the current load from top NumberFields minus 2 should be last minute average
EXCEEDED=$(echo "${CPU} > ${THRESHOLD}" | bc); # Determine if current load is higher than threshold
if (( ${EXCEEDED} > 0 ))
then
        echo "${RUNTIME} - Current load: ${CPU}" >> /some/file;
        echo "${RUNTIME} - CPU has execeed threshold of: ${THRESHOLD}. Capturing processlist..." >> /some/file;

victorpendleton

#!/bin/bash

## Pull INSERTs
/usr/local/mysql/bin/mysqlbinlog server-bin.1 --base64-output=DECODE-ROWS
 --start-datetime="YYYY-MM-DD HH:MM:SS" 
--stop-datetime="YYYY-MM-DD HH:MM:SS" 
-vv | grep -iI "INSERT INTO \`database-name" | sort -n | uniq -c | sort -rn

## Pull UPDATEs
/usr/local/mysql/bin/mysqlbinlog server-bin.1 --base64-output=DECODE-ROWS

victorpendleton

SELECT p.user 
, substring(p.host, 1, IF( (locate(':', p.host)-1)<0, LENGTH(p.host), locate(':', p.host)-1 ) ) as host
, Substring(p.info, 1, 56) as snippet
, count(*) 
FROM information_schema.processlist p 
LEFT OUTER JOIN information_schema.innodb_trx t 
ON p.id = t.trx_mysql_thread_id 
WHERE p.command not in ('Binlog Dump GTID', 'Binlog Dump') 
AND p.user NOT IN ('system user') 
AND p.id <> connection_id() 

victorpendleton

SELECT p.id 
, p.user 
, p.host
, p.command 
, p.time 
, p.time/60 as minutes 
, p.state 
, substring(p.info, 1, 80) as snippet 
FROM information_schema.processlist p 
WHERE p.user IN ('system user')