MySQL Locks Test

The locking activity of a database server must be monitored carefully because an application holding a specific lock for a long time could cause a number of other transactions relying on the same lock to fail. The MySQL Locks test monitors the locking activity on a database server instance.

Target of the test : A MySQL server

Agent deploying the test : An internal/remote agent

Outputs of the test : One set of results for the database being monitored

Configurable parameters for the test
  1. TEST PERIOD – How often should the test be executed
  2. Host – The IP address of the MySQL server
  3. Port – The port on which the server is listening
  4. db – the name of a database on the server
  5. Usernameand password - The eG agent has to be configured with the credentials of a user who has server-wide process and select privileges on the monitored MySQL server. To know how to create such a user, refer to Pre-requisites for Monitoring the MySQL Server topic.
  6. Confirm password – Confirm the password by retyping it here.
Measurements made by the test
Measurement Description Measurement Unit Interpretation

Lock waits:

Indicates the percentage of lock requests that could not be satisfied immediately and hence, required the caller to wait before being granted the lock.

Percent

A high value of waits can have an adverse impact on application performance. Possible reasons for this behavior could be:

  • inadequate number of locks available in the database,
  • unusually high locking behavior of applications accessing the database,
  • improper database application design, etc.

 

Table lock waits:

Indicates the number of times in the last measurement period a table lock could not be acquired immediately and a wait was needed.

Number

If the number of waits is high, application performance could suffer. You should first optimize your queries, and then either split your table(s) or use replication.