Maria Lock Waits Test

MariaDB enables client sessions to acquire table locks explicitly for the purpose of cooperating with other sessions for access to tables, or to prevent other sessions from modifying tables during periods when a session requires exclusive access to them. A session can acquire or release locks only for itself. One session cannot acquire locks for another session or release locks held by another session.

Locks may be used to emulate transactions or to get more speed when updating tables.

Sometimes, in an attempt to release locks for compatible requests, Maria Database server may end up increasing the lock wait times for certain incompatible requests. Some other times, certain long-running operations can cause the length of the queue to increase along with the waiting time for locks. Long lock wait times can adversely impact application performance. This is why, administrators will have to keep an eye on the locking activity on a Maria Database server, determine whether/not requests are waiting too long for locks. This will enable administrators to quickly diagnose why the requests are taking too long to acquire a lock and resolve the bottleneck. The Maria Lock Waits test helps administrators achieve this.

This test reports the rate at which locks of that type were acquired, the percentage of requests that were waiting for locks and the number of times a table lock could not be acquired by the requests. With this information, administrators can easily figure out if the lock wait time is prolonged and troubleshoot the long wait times.

Target of the test : A Maria Database server

Agent deploying the test : An internal/remote agent

Outputs of the test : One set of results for the target Maria Database server being monitored.

Configurable parameters for the test
Parameter Description

Test Period

How often should the test be executed.

Host

The IP address of the Maria Database server.

Port

The port on which the server is listening.

Database

Specify the name of the database that is to be monitored on the target Maria Database server.

User and 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 Maria Database server. To know how to create such a user, refer to Configuring the eG Agent with Access Privileges section.

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 requests that were waiting to acquire a lock.

Percentage

A consistent increase in the value for this measure could indicate that locks are not being released in a timely manner. A high value of this measure may be attributed to the following reasons:

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

Table Locks Waited

Indicates the number of times a table lock could not be acquired by the requests and had to wait for a lock during the last measurement period.

Number

A high value for this measure may indicate one of the following:

  • Too many transactions happening
  • Locked resources not being released properly
  • Locks are being held by long-running operations

A high value for this measure may cause performance issues to the database server. To reduce the value of this measure, you may try optimizing the queries or split the tables of the database server or use replication process on the database server.