One role of a MySQL consultant is to review an existing production system. Sometimes you have sufficient time and access, and other times you don’t. If I am given a limited time here is a general list of things I look at.
- Review Server architecture, OS, Memory, Disks (including raid and partition type), Network etc
- Review server load and identify physical bottleneck
- Look at all running processes
- Look specifically at MySQL processes
- Review MySQL Error Log
- Determine MySQL version
- Look at MySQL configuration (e.g. /etc/my.cnf)
- Look at running MySQL Variables
- Look at running MySQL status (x n times)
- Look at running MySQL INNODB status (x n times) if used
- Get Database and Schema Sizes
- Get Database Schema
- Review Slow Query Log
- Capture query sample via SHOW FULL PROCESSLIST (locked and long running)
- Analyze Binary Log file
- Capture all running SQL
Here are some of the commands I would run.
2. Review server load and identify physical bottleneck
$ vmstat 5 720 > vmstat.`date +%y%m%d.%H%M%S`.txt
4. Look at MySQL processes
$ ps -eopid,fname,rss,vsz,user,command | grep -e "RSS" -e "mysql"
PID COMMAND RSS VSZ USER COMMAND
5463 grep 764 5204 ronald grep -e RSS -e mysql
13894 mysqld_s 596 3936 root /bin/sh /usr/bin/mysqld_safe
13933 mysqld 4787812 5127208 mysql /usr/sbin/mysqld --basedir=/usr --datadir=/vol/mysql/mysqldata --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock
13934 logger 608 3840 root logger -p daemon.err -t mysqld_safe -i -t mysqld
$ ps -eopid,fname,rss,vsz,user,command | grep " mysqld " | grep -v grep | awk '{print $3,$4}'
4787820 5127208
5. Review MySQL Error Log
The error log can be found in various different places based on the operating system and configuration. It is important to find the right log, the SHOW GLOBAL VARIABLES LIKE ‘log_error’ will determine the location.
This is generally overlooked, however this can quickly identify some underlying problems with a MySQL environment.
7. Look at MySQL configuration
$ [ -f /etc/my.cnf ] && cat /etc/my.cnf $ [ -f /etc/mysql/my.cnf ] && cat /etc/mysql/my.cnf $ find / -name "*my*cnf" 2>/dev/null
8. Look at running MySQL Variables
$ mysqladmin -uroot -p variables
9. Look at running MySQL status (x n times)
$ mysqladmin -uroot -p extended-status
It is important to run this several times at regular intervals, say 60 seconds, 60 minutes, or 24 hours.
I also have dedicated scripts that can perform this. Check out Log MySQL Stats.
11. Get Database and Schema Sizes
Check out my scripts on my MySQL DBA page
14. Capture Locked statements
Check out my script for Capturing MySQL sessions.
15. Analyze Binary Log file
Check out my post on using mk-query-digest.
16. Capture all SQL
Check out my post on DML Stats per table
Moving forward
Of course the commands I run exceeds this initial list, and gathering this information is only
