kill all pts
for x in $(ps aux | grep pts| awk '{print $2}'); do kill $x; done
Strace all php-fpm process by a single user for debugging
echo; read -p "User Name: " _user_ ;strace -ffTttvs20000 -o ~/strace.$_user_.log $(ps aux | grep php-fpm | grep $_user_ | awk '{print $2}'| sed 's/\([0-9]*\)/\-p \1/g')
Strace for apache
strace -ffTttvs20000 -o ~/strace.apache.log $(ps aux | grep apache |grep -v grep | awk '{print $2}'| sed 's/\([0-9]*\)/\-p \1/g')
What process is using up the most memory
ps aux | awk '{if ($5 != 0 ) print $2,$5,$6,$11}' | sort -k2n
Find the top files in MB for the Current GROUP user
find $PWD -type f -group $(stat -c "%G" $PWD) -exec du --block-size=MB {} \; | sort -rn | uniq | head
Find the larges directories in MB for the current GROUP user
find $PWD -type d -group $(stat -c "%G" $PWD) -exec du --block-size=MB {} \; | sort -rn | uniq | head
Apache Oneliners
Bandwidth per IP/Bot
grep -i google transfer.log | awk '{ sum+=$10} END {size= sum / 1048576; printf "total size %.2f MB\n", size}'
Max_Clients - Check which account is getting the most traffic
for z in $(find /home/*/var/*/logs/transfer.log); do echo $z; echo '----------'; for x in $(seq -w 0 24); do echo -n "$x "; grep -c "$(date +%d/%b/%Y:)$x" $z; done;done;
Check the Number of IP's Connecting over Port 80
netstat -tn |grep EST| grep :80 | awk '{print $5}'| cut -d: -f1 | sort | uniq -c | sort -rn | head
netstat -tn |grep EST| grep :80 | awk '{print $5}'| cut -d: -f4 | sort | uniq -c | sort -rn | head
Check what IP's are getting the most traffic
netstat -tn | grep EST | grep :80 | awk '{print $4}' |cut -d: -f1 | sort |uniq -c | sort -rn | head
netstat -tn |grep EST| grep :80 | awk '{print $4}'| cut -d: -f4 | sort | uniq -c | sort -rn | head
size of logs
ls -lahrS /home/*/var/*/logs/*log
what are the php-cgi processes doing? (sleeps for 5 seconds, command takes a while to run, will open up less when it's done)
sleep 5; for i in $(ps aux |grep php-cgi |grep -v defunct |grep -v grep |awk '{print $2}'); do strace -p $i -o $i.trace ; done ; cat *.trace | less && rm -rf *.trace
top 20 URLs from the last 5000 hits
tail -5000 ./transfer.log | awk '{freq[$7]++} END {for (x in freq) {print freq[x], x}}' | sort -rn | head -20
top 20 URLS excluding POST data from the last 5000 hits
tail -5000 ./transfer.log | awk -F"[ ?]" '{freq[$7]++} END {for (x in freq) {print freq[x], x}}' | sort -rn | head -20
top 20 IPs from the last 5000 hits
tail -5000 ./transfer.log | awk '{freq[$1]++} END {for (x in freq) {print freq[x], x}}' | sort -rn | head -20
top 20 URLs requested from a certain ip from the last 5000 hits
IP=1.2.3.4; tail -5000 ./transfer.log | awk -v ip=$IP ' $1 ~ ip {freq[$7]++} END {for (x in freq) {print freq[x], x}}' | sort -rn | head -20
top 20 URLS requested from a certain ip excluding, excluding POST data, from the last 5000 hits
IP=1.2.3.4; tail -5000 ./transfer.log | awk -F"[ ?]" -v ip=$IP ' $1 ~ ip {freq[$7]++} END {for (x in freq) {print freq[x], x}}' | sort -rn | head -20
top 20 referrers from the last 5000 hits
tail -5000 ./transfer.log | awk '{freq[$11]++} END {for (x in freq) {print freq[x], x}}' | tr -d '"' | sort -rn | head -20
top 20 user agents from the last 5000 hits
tail -5000 ./transfer.log | cut -d\ -f12- | sort | uniq -c | sort -rn | head -20
sum of data (in MB) transferred in the last 5000 hits
tail -5000 ./transfer.log | awk '{sum+=$10} END {print sum/1048576}'
IPs using the most bandwidth (in MB) from the last 5000 hits
tail -5000 ./transfer.log | awk '{tx[$1]+=$10} END {for (x in tx) {print x, "\t", tx[x]/1048576, "M"}}' | sort -k 2n | tail -n 20 | tac
hits per hour
for x in $(seq -w 0 23); do echo -n "$x "; grep -c "$(date +%d/%b/%Y:)$x" ./transfer.log; done;
Mysql Oneliners
Show locked tables
show open tables WHERE In_use > 0;
Check what size the Innodb Buffer Pool should be
SELECT CONCAT(ROUND(KBS/POWER(1024, IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
SUBSTR(' KMG',IF(PowerOf1024<0,0, IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size FROM (SELECT SUM(data_length+index_length) KBS
FROM information_schema.tables WHERE engine='InnoDB') A, (SELECT 3 PowerOf1024) B;
Show all Definers
show procedure status;
Change Procedure Definer
UPDATE `mysql`.`proc` p SET definer = 'use1r@%' WHERE definer='user2t@%'
Check uptime
mysql> \s;
Adjust innodb_lock_wait_timeout, might need a restart but most systems are not read only
m -e'set global innodb_lock_wait_timeout = 150;' && m -e'set session innodb_lock_wait_timeout = 150;'
- note I have setup an alias for m to connect to mysql in my bashrc file
alias m='mysql -u <user> -p<longuglypasswod>'
Check database size for all databases
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;
Check Free space in the Database - Free space in the tablespace not allocated to any segment
SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 /1024 "Data Base Size in MB",sum( data_free )/ 1024 / 1024 "Free Space in MB"FROM information_schema.TABLES GROUP BY table_schema ;
Check when the databases where last updated
use information_schema; SELECT MAX(UPDATE_TIME), MAX(CREATE_TIME), TABLE_SCHEMA FROM TABLES GROUP BY TABLE_SCHEMA ORDER BY 1, 2;
