Storage

Top 10 largest files and folders

du -a . | sort -n -r | head -n 10

Top 30 largest files on this FS in MB

find . -mount -type f -ls|sort -rnk7 |head -30|awk '{printf "%10d MB\t%s\n",($7/1024)/1024,$NF}'

Top 30 largest files and folders on this FS, limited to 4 decent folders, in MB

du -x --max-depth=4 .|sort -rn|head -n30|awk '{printf "%10d MB\t%s\n",$1/1024,$2}'

Processes

Top 30 processes using SWAP

find /proc -maxdepth 1 -type d -name "[0-9]*" -exec grep -H VmSwap {}/status \; | sort -n -r -k2 | head -30
find /proc -maxdepth 2 -path "/proc/[0-9]*/status" -readable -exec awk -v FS=":" '{process[$1]=$2;sub(/^[ \t]+/,"",process[$1]);} END {if(process["VmSwap"] && process["VmSwap"] != "0 kB") printf "%10s %-30s %20s\n",process["Pid"],process["Name"],process["VmSwap"]}' '{}' \; | awk '{print $(NF-1),$0}' | sort -h | cut -d " " -f2-

Networking

Watching for new tcp connections

tcpdump "tcp[tcpflags] & (tcp-syn) != 0"

Checking for multiple MAC addresses on network

arping -I bond0 10.0.1.0

reviewing multicast addresses on host

ip maddress

MySQL

select processes not sleeping

SELECT * FROM information_schema.processlist WHERE command != 'Sleep' ORDER BY id;
SELECT user, time, state, info FROM information_schema.processlist WHERE command != 'Sleep' AND time >= 2 ORDER BY time DESC, id \G

Purge binary logs older than 10 days minimum ideal retention.

/bin/mysql -e "PURGE BINARY LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 10 DAY);"

Move Binlogs to free up space (dont copy large numbers limit to 10 oldest at a time)

mkdir -p /root/temp_binlogs
head -10  mysql-bin.index
ionice -c 3 mv mysql-bin.{000001..000010} /root/temp_binlogs

Export to csv

# locate writable folder, ensure sufficient storage.
SELECT @@GLOBAL.secure_file_priv;
# export select as csv 
SELECT * FROM MY_TABLE INTO OUTFILE '/data/mysql/export_my_table.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Database size in MB

SELECT table_schema "DB Name",
        ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" 
FROM information_schema.tables 
GROUP BY table_schema; 

PostgreSQL

Standby replication lag

select now()-pg_last_xact_replay_timestamp() as replication_lag;

Blocking processes.

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 
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
 
    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;

Blocked and blocking queries.

SELECT
    activity.pid,
    activity.usename,
    activity.query,
    blocking.pid AS blocking_id,
    blocking.query AS blocking_query
FROM pg_stat_activity AS activity
JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));

Blocking queries runtimes over 1min.

SELECT blocking.pid AS blocking_id
FROM pg_stat_activity AS activity
JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid))
where extract( MINUTES FROM now() - blocking.query_start) > 1;

Show count of queries that are causing blocking.

SELECT count(blocking.pid) AS blocking_count
FROM pg_stat_activity AS activity
JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid))
where extract( MINUTES FROM now() - blocking.query_start) > 10;

Stop a blocking query.

SELECT pg_cancel_backend(PID_HERE);

Stop a blocking query that will not cancel.

SELECT pg_terminate_backend(PID_HERE);