The lock probes are called whenever an external lock is requested by MySQL for a table using the corresponding lock mechanism on the table as defined by the table's engine type. There are three different types of lock, the read lock, write lock, and unlock operations. Using the probes you can determine the duration of the external locking routine (that is, the time taken by the storage engine to implement the lock, including any time waiting for another lock to become free) and the total duration of the lock/unlock process.
handler-rdlock-start(database, table) handler-rdlock-done(status) handler-wrlock-start(database, table) handler-wrlock-done(status) handler-unlock-start(database, table) handler-unlock-done(status)
handler-rdlock-start
: Triggered when a read lock is requested on the specifieddatabase
andtable
.handler-wrlock-start
: Triggered when a write lock is requested on the specifieddatabase
andtable
.handler-unlock-start
: Triggered when an unlock request is made on the specifieddatabase
andtable
.handler-rdlock-done
: Triggered when a read lock request completes. Thestatus
is 0 if the lock operation succeeded, or>0
on failure.handler-wrlock-done
: Triggered when a write lock request completes. Thestatus
is 0 if the lock operation succeeded, or>0
on failure.handler-unlock-done
: Triggered when an unlock request completes. Thestatus
is 0 if the unlock operation succeeded, or>0
on failure.
You can use arrays to monitor the locking and unlocking of individual tables and then calculate the duration of the entire table lock using the following script:
#!/usr/sbin/dtrace -s #pragma D option quiet mysql*:::handler-rdlock-start { self->rdlockstart = timestamp; this->lockref = strjoin(copyinstr(arg0),strjoin("@",copyinstr(arg1))); self->lockmap[this->lockref] = self->rdlockstart; printf("Start: Lock->Read %s.%s\n",copyinstr(arg0),copyinstr(arg1)); } mysql*:::handler-wrlock-start { self->wrlockstart = timestamp; this->lockref = strjoin(copyinstr(arg0),strjoin("@",copyinstr(arg1))); self->lockmap[this->lockref] = self->rdlockstart; printf("Start: Lock->Write %s.%s\n",copyinstr(arg0),copyinstr(arg1)); } mysql*:::handler-unlock-start { self->unlockstart = timestamp; this->lockref = strjoin(copyinstr(arg0),strjoin("@",copyinstr(arg1))); printf("Start: Lock->Unlock %s.%s (%d ms lock duration)\n", copyinstr(arg0),copyinstr(arg1), (timestamp - self->lockmap[this->lockref])/1000000); } mysql*:::handler-rdlock-done { printf("End: Lock->Read %d ms\n", (timestamp - self->rdlockstart)/1000000); } mysql*:::handler-wrlock-done { printf("End: Lock->Write %d ms\n", (timestamp - self->wrlockstart)/1000000); } mysql*:::handler-unlock-done { printf("End: Lock->Unlock %d ms\n", (timestamp - self->unlockstart)/1000000); }
When executed, you should get information both about the duration of the locking process itself, and of the locks on a specific table:
Start: Lock->Read test.t2 End: Lock->Read 0 ms Start: Lock->Unlock test.t2 (25743 ms lock duration) End: Lock->Unlock 0 ms Start: Lock->Read test.t2 End: Lock->Read 0 ms Start: Lock->Unlock test.t2 (1 ms lock duration) End: Lock->Unlock 0 ms Start: Lock->Read test.t2 End: Lock->Read 0 ms Start: Lock->Unlock test.t2 (1 ms lock duration) End: Lock->Unlock 0 ms Start: Lock->Read test.t2 End: Lock->Read 0 ms