Skip to content

Instantly share code, notes, and snippets.

Avatar

victorpendleton

View GitHub Profile
@victorpendleton
victorpendleton / blocked_postgres_connections.sql
Last active Mar 16, 2020
View blocking Postgres connections
View blocked_postgres_connections.sql
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
View gist:c6d26fda13bd0c09bd64dbbaf7ca405e
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
View gist:6ce495aec12776741f038690133965b7
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;
View gist:e5d2da33433dcfeb58d2cd0c799fd954
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;
View Rabbitmq commands
# 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
victorpendleton / size_of_postgres_schemas.sql
Last active Nov 8, 2019
Git size of postgres schemas
View size_of_postgres_schemas.sql
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
victorpendleton / check_load.sh
Created Nov 27, 2017
Script used to fire off action when load crosses threshold
View check_load.sh
#!/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
victorpendleton / getdml.sh
Last active Oct 11, 2017
Pull DML from MySQL binary logs (Optional time range)
View getdml.sh
#!/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
View group_active_transactions.sql
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()
View identify_blocked_replication.sql
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')
You can’t perform that action at this time.