Linux + MySQL/MariaDB: “Who’s eating CPU/RAM?” — Handy Commands

Below is a compact, copy-pasteable cheat-sheet to find which process, user, or application is consuming the most CPU and memory, plus MySQL/MariaDB commands to pinpoint hot databases and queries.

🧠 Quick System Snapshots

# Top CPU processes (non-interactive)
ps -eo user,pid,ppid,%cpu,%mem,command --sort=-%cpu | head -20

# Top Memory processes (by RSS)
ps -eo user,pid,ppid,rss,%mem,command --sort=-rss | head -20

# Aggregate CPU & MEM by USER (your provided one-liner)
ps -eo user,%cpu,%mem --no-headers | awk '{cpu[$1]+=$2; mem[$1]+=$3} END {printf "%-15s %-10s %-10s\n", "USER", "CPU(%)", "MEM(%)"; for (i in cpu) printf "%-15s %-10.2f %-10.2f\n", i, cpu[i], mem[i]}'

# Aggregate CPU & MEM by COMMAND (binary basename)
ps -eo comm,%cpu,%mem --no-headers | awk '{cpu[$1]+=$2; mem[$1]+=$3} END {printf "%-25s %-10s %-10s\n","COMMAND","CPU(%)","MEM(%)"; for (i in cpu) printf "%-25s %-10.2f %-10.2f\n", i, cpu[i], mem[i]}' | sort -k2nr | head

# “top” in batch mode (snapshot)
top -b -n1 | head -40

# CPU per core + overall every 1s (5 samples)
mpstat -P ALL 1 5

# System run queue, context switches, memory, IO every 1s (5 samples)
vmstat 1 5

# Memory summary
free -h

# Per-PID CPU usage every second (5 samples)
pidstat -u 1 5

# Per-PID memory usage every second (5 samples)
pidstat -r 1 5

# If using systemd cgroups: top talkers by cgroup (services/containers)
systemd-cgtop

# If using Docker: per-container CPU/RAM
docker stats

🧩 Web/PHP-specific (optional but handy)

# PHP-FPM workers sorted by CPU
ps -o pid,etime,pcpu,pmem,cmd -C php-fpm | sort -k3 -nr | head

# See PHP-FPM pools spiking (cpanel path shows pool names)
ps -eo user,pcpu,pmem,command | grep "php-fpm: pool" | sort -k2 -nr | head

# Apache httpd workers by CPU
ps -o pid,etime,pcpu,pmem,cmd -C httpd | sort -k3 -nr | head

🗃️ MySQL/MariaDB — Find the Noisy DB/Queries

Replace -p with -pYOURPASS to inline the password (or omit to be prompted).

1) What’s running right now?

mysql -u root -p -e "SHOW FULL PROCESSLIST\G"

# Active queries by database (ignores Sleep/Daemon)
mysql -u root -p -e "
SELECT db, COUNT(*) AS active_queries
FROM information_schema.PROCESSLIST
WHERE db IS NOT NULL AND COMMAND NOT IN ('Sleep','Daemon')
GROUP BY db
ORDER BY active_queries DESC;"

2) Sample for a minute to catch spikes

# 30 samples every 2s; aggregate active queries per DB
for i in {1..30}; do
  mysql -N -u root -pYOURPASS -e "
    SELECT IFNULL(db,'(none)') db, COUNT(*) c
    FROM information_schema.PROCESSLIST
    WHERE COMMAND NOT IN ('Sleep','Daemon')
    GROUP BY db;" 2>/dev/null
  sleep 2
done | awk '{a[$1]+=$2} END{for (i in a) printf "%-30s %s\n", i, a[i]}' | sort -k2nr

3) Largest databases (size can hint hot spots)

mysql -u root -p -e "
SELECT table_schema AS Database,
       ROUND(SUM(data_length + index_length)/1024/1024, 2) AS Size_MB
FROM information_schema.tables
GROUP BY table_schema
ORDER BY Size_MB DESC;"

4) Slow Query Log (captures heavy queries you might miss live)

Enable (permanent): add to /etc/my.cnf (or /etc/my.cnf.d/server.cnf)

[mysqld]
slow_query_log=1
slow_query_log_file=/var/log/mariadb/slow.log
long_query_time=2
log_slow_verbosity=query_plan

Then:

systemctl restart mariadb

Enable (temporary, no restart):

mysql -u root -p -e "SET GLOBAL slow_query_log=ON; SET GLOBAL long_query_time=2;"

Watch & analyze:

tail -f /var/log/mariadb/slow.log
# If Percona toolkit is installed:
pt-query-digest /var/log/mariadb/slow.log | less
# Quick count of slow queries per schema from the log:
grep -i '^# Schema:' /var/log/mariadb/slow.log | awk '{print $3}' | sort | uniq -c | sort -k1nr

5) Performance Schema (per-DB total time & top query patterns)

# Check if enabled
mysql -u root -p -e "SHOW VARIABLES LIKE 'performance_schema';"

If ON, run:

# Total time spent per database
mysql -u root -p -e "
SELECT schema_name AS db,
       ROUND(SUM(SUM_TIMER_WAIT)/1e12,2) AS total_seconds,
       SUM(COUNT_STAR) AS queries
FROM performance_schema.events_statements_summary_by_digest
GROUP BY schema_name
ORDER BY total_seconds DESC
LIMIT 10;"

# Top query fingerprints by total time
mysql -u root -p -e "
SELECT schema_name AS db,
       ROUND(SUM_TIMER_WAIT/1e12,2) AS seconds,
       COUNT_STAR,
       DIGEST_TEXT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20\G"

If OFF, enable in /etc/my.cnf:

[mysqld]
performance_schema=ON
performance-schema-instrument='statement/%=ON'
performance-schema-consumer-events-statements-current=ON
performance-schema-consumer-events-statements-history=ON
performance-schema-consumer-events-statements-history-long=ON

Then systemctl restart mariadb.

6) MariaDB “userstat” (per-DB/table read/write activity)

Enable in /etc/my.cnf:

[mysqld]
userstat=1

Restart, then:

# Top databases by table activity
mysql -u root -p -e "
SELECT db,
       SUM(rows_read) AS rows_read,
       SUM(rows_changed) AS rows_changed,
       (SUM(rows_read)+SUM(rows_changed)) AS activity
FROM information_schema.TABLE_STATISTICS
GROUP BY db
ORDER BY activity DESC
LIMIT 10;"

7) InnoDB internals & server health

# Locks, purge lag, deadlocks, long trx
mysql -u root -p -e "SHOW ENGINE INNODB STATUS\G" | sed -n '1,200p'

# Useful status deltas (run twice ~30s apart)
mysql -u root -p -e "SHOW GLOBAL STATUS LIKE 'Threads_running';"
mysql -u root -p -e "SHOW GLOBAL STATUS LIKE 'Created_tmp%';"
mysql -u root -p -e "SHOW GLOBAL STATUS LIKE 'Sort%';"

✅ Tips

Run the sampling loop during the CPU spike to get a fair ranking of hot databases.
Always pair PROCESSLIST snapshots with the slow query log for complete coverage.
If PHP-FPM is prominent in ps, check which pool is hot (often maps to a single vhost/site).

If you want, paste outputs from:

the sampling loop summary, or
the top 20 digests from Performance Schema, and I’ll point to the exact DB and queries to fix (indexes, rewrites, or config tweaks).

devops-school/Linux-MySQLMariaDB-CPU-MEMORY.md