MySQL Forums
Forum List  »  InnoDB

Interpreting Innodb_row_lock_xxx status variables
Posted by: JG K
Date: March 07, 2012 09:20PM

I hope this is an appropriate topic for this forum. If not
please point me to the appropriate one. Thanks in advance.

Server version: 5.0.77 Source distribution
mysqladmin Ver 8.41 Distrib 5.0.77, for redhat-linux-gnu on i686

I am somewhat new to using mySQL in a large production environment
and am trying to learn how to monitor and evaluate performance of
a complex DB application. In particular would like to know more
about the precise information content of, as well as the options
for resetting to 0, the following extended-status variables:

| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 1268590426 |
| Innodb_row_lock_time_avg | 247 |
| Innodb_row_lock_time_max | 54913 |
| Innodb_row_lock_waits | 5116639 |

From what I've been able to find in documentation and on-line articles,
the row_lock_time_avg is just computed as row_lock_time/row_lock_waits
(247 in the above example), and row_lock_time is the cumulative wait
time across all the row_lock_waits. The row_lock_time_max is the
highwater mark for any one row_lock_wait.

Is row_lock_current_waits the value for the most recent wait recorded?
I've only ever seen a 0 in this variable.

What I have not been able to determine is the time span for collecting
these stats and how to reset them to start a new collection interval.

I've tried the flush-status mysqladmin command as well as the
'flush status' command from the mysql console and this has
no effect on these values. Is there no way to reset these
counters short of a full blown mysql restart? In particular
I would like to be able to monitor the row_lock_time_max at
varying times of the day so I'd like to be able to reset this
fairly frequently with minimal operational disruption.

For my particular application there are three large databases
with close to 200 tables among them. Are these Innodb_row_lock_
status values cumulative across all the tables in all the databases?
Is there anyway to obtain row_lock stats on a per-table basis?
Maybe there is some kind of profiling I can enable in parts of
the application itself for this? At the conclusion of a query
in the application itself is there any way, using the C-API
say, to access stats that reflect that query's performance
(e.g. total response time, lock wait time, etc.).

Thanks to whomever chooses to provide some guidance.

Options: ReplyQuote


Subject
Views
Written By
Posted
Interpreting Innodb_row_lock_xxx status variables
2663
March 07, 2012 09:20PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.