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 specified
database
and table
.
handler-wrlock-start
— triggered
when a write lock is requested on the specified
database
and table
.
handler-unlock-start
— triggered
when an unlock request is made on the specified
database
and table
.
handler-rdlock-done
— triggered
when a read lock request completes. The
status
is 0 if the lock operation
succeeded, or >0
on failure.
handler-wrlock-done
— triggered
when a write lock request completes. The
status
is 0 if the lock operation
succeeded, or >0
on failure.
handler-unlock-done
— triggered
when an unlock request completes. The
status
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
User Comments
Add your own comment.