Skip to content

Instantly share code, notes, and snippets.

victorpendleton

Block or report user

Report or block victorpendleton

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
View GitHub Profile
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')
@victorpendleton
victorpendleton / skip_gtid.sql
Last active Oct 4, 2017
How to skip MySQL replication errors using GTIDs
View skip_gtid.sql
SHOW SLAVE STATUS\G
-- Grab Primary Host from `Retrieved_Gtid_Set`
-- Retrieved_Gtid_Set: PRIMARY_GUID:1234567
-- Find what value has been executed from primary from Executed_Gtid_Set
-- Executed_Gtid_Set: PRIMARY_GUID:123455
STOP SLAVE;
-- Set the gtid to next value
SET GTID_NEXT ='PRIMARY_GUID:123456';
begin; commit;
set gtid_next = 'AUTOMATIC';
You can’t perform that action at this time.