Useful MySQL Commands, Configuration and Diagnostics

After having gone through a Percona training presentation I’ve put some notes together noting some of the more crucial and useful points for myself.

Some are trivial points that I’ve just noted for the sake of reference, others are interesting tricks and snags we should all be aware of. Ultimately, these are all must know facts for any and all MySQL DBAs regardless of the MySQL flavour.

Useful command line monitoring one liners:

top -bn1 # non interactive mode for collection
vmstat 5 # classic vmstat collection
mpstat -P ALL 5 ~ explicit cpu/vm stats

free # free memory

netstat -antp | awk ‘{print $6}’ | sort | uniq -c | sort -rn # count states
netstat -antp | awk ‘{print $5}’ | cut -d: -f1 | sort | uniq -c | sort -rn # count peers

iostat -dx 5 # extended iostats
iostat -kx 5 # with cpu stats

>> Tribute to poor man’s profiler:

perf record -p # collect samples for perf report
perf report # generate perf report

Percona Monitoring Tools:

• pt-deadlock-logger
– Extract and log MySQL deadlock information. • pt-fk-error-logger
– Extract and log MySQL foreign key errors. • pt-mext
– Look at samples of SHOW GLOBAL STATUS side-by- side.


# Command for executing a delayed slave
pt-slave-delay –delay 1m –interval 15s –run-time 10m slavehost

Xtrabackup Tips:

– Create segmented backups per database (with the –databases option) or per tables using a filelist (–tables-file)

INNODB Log Writing:
•  You should increase innodb_log_file_size. Allows InnoDB to “smooth out” background IO.
•  You should put logs on a separate spindle by specifying innodb_log_group_home_dir.
•  Be aware that your total log space usage is: – innodb_log_file_size * innodb_log_files_in_group
•  You can change innodb_log_files_in_group, but there is no significant benefit from this.
• innodb_flush_log_at_trx_commit = 1
• Change to 0 or 2 to reduce the durability of this write.
– Requires less flushing—particularly helpful on systems without writeback caches
• innodb_log_buffer_size may also help buffer changes longer before writing to the logs.
– Very workload dependent—tends to be more helpful for writing big TEXT/BLOB changes.


ibdata file contains the central tablespace includes global information:
•  A tablespace header
•  The InnoDB data dictionary
•  The Rollback segment(s)
•  The Doublewrite buffer
•  The Change buffer

Configuring fixed and dynamic ibdata files in my.cnf:


Remember: innodb_files_per_table=ON [ibdata + seperate .ibd files per table]

Adaptive Hash Concerns & Locking

• Look in SHOW ENGINE INNODB STATUS for locks on btr0sea.c.
Thread 140054029002496 has waited at btr0sea.c line 631 for 1.00
seconds the semaphore: X-lock (wait_ex) on RW-latch at 0x78733f8
created in file btr0sea.c line 182 a writer (thread id 140054029002496)
has reserved it in mode wait exclusive number of readers 1, waiters
flag 1, lock_word: ffffffffffffffff Last time read locked in file
btr0sea.c line 879 Last time write locked in file btr0cur.c line 1896

Purging old rows & indexes:

If many updates happen concurrently it is possible that this thread may not be able to keep.
• You can use innodb_max_purge_lag (default: 0) to throttle updates when purging is behind.
• It is possible to configure the purge operation to work in it own separate thread:
• MySQL 5.1 (only XtraDB): innodb_use_purge_thread=1
• MySQL 5.5: innodb_purge_threads=1

Internal threads:

[Default] InnoDB has one background thread. This is referred to as “the main thread.” or srv_master_thread.
• In MySQL 5.5 and in XtraDB, it’s also possible to purge undo in its own thread(s).
• Deadlock detection is also a thread—but we have very little visibility to its operation.

• When innodb_status_file=1, InnoDB outputs SHOW ENGINE INNODB STATUS to a file called innodb_status. every 15 seconds.

Monitoring Tables:
• Exist as a way of asking InnoDB to print information to your error log.
CREATE TABLE innodb_monitor (a INT) ENGINE=InnoDB;
CREATE TABLE innodb_lock_monitor (a INT) ENGINE=InnoDB;
CREATE TABLE innodb_table_monitor (a INT) ENGINE=InnoDB;
CREATE TABLE innodb_tablespace_monitor (a INT) ENGINE=InnoDB;


Can show if default spin lock was used, or OS wait:
e.g. “Mutex spin waits 5672442, rounds 3899888, OS waits 4719”

– Spin lock burns CPU
– OS Wait requires expensive context switching back in.
– Increase innodb_sync_spin_loops to context switch less but wait longer on mutexes.
If an OS wait was required, it should show where in the source this occurred:

“btr0sea” are adaptive hash waits.
“trx0rseg” is rollback segment.
“buf0buf” is the main buffer pool mutex.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s