Databases Reference
In-Depth Information
| InnoDB | &dict_operation_lock | os_waits=13 |
| InnoDB | &log_sys->checkpoint_lock | os_waits=66 |
| InnoDB | combined &block->lock | os_waits=2 |
+--------+------------------------------+-------------+
You can use the output to help determine which parts of InnoDB are bottlenecks, based
on the number of waits. Anywhere there's a mutex, there's a potential for contention.
You might need to write a script to aggregate the output, which can be very large.
There are three main strategies for easing mutex-related bottlenecks: try to avoid
InnoDB's weak points, try to limit concurrency, or try to balance between CPU-inten-
sive spin waits and resource-intensive operating system waits. We discussed this earlier
in this appendix, and in
Chapter 8
.
Replication Status
MySQL has several commands for monitoring replication. On a master server,
SHOW
MASTER STATUS
shows the master's replication status and configuration:
mysql>
SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: mysql-bin.000079
Position: 13847
Binlog_Do_DB:
Binlog_Ignore_DB:
The output includes the master's current binary log position. You can get a list of binary
logs with
SHOW BINARY LOGS
:
mysql>
SHOW BINARY LOGS
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000044 | 13677 |
...
| mysql-bin.000079 | 13847 |
+------------------+-----------+
36 rows in set (0.18 sec)
To view the events in the binary logs, use
SHOW BINLOG EVENTS
. In MySQL 5.5, you can
also use
SHOW RELAYLOG EVENTS
.
On a replica server, you can view the replica's status and configuration with
SHOW SLAVE
STATUS
. We won't include the output here, because it's a bit verbose, but we will note
a few things about it. First, you can see the status of both the replica I/O and replica
SQL threads, including any errors. You can also see how far behind the replica is in
replication. Finally, for the purposes of backups and cloning replicas, there are three
sets of binary log coordinates in the output:
Master_Log_File/Read_Master_Log_Pos
The position at which the I/O thread is reading in the master's binary logs.
