MySQL InnoDb Engine Statistics Test

The buffer pool is an area in main memory where InnoDB caches table and index data as it is accessed. The buffer pool permits frequently used data to be accessed directly from memory, which speeds up processing. On dedicated servers, up to 80% of physical memory is often assigned to the buffer pool. For efficiency of high-volume read operations, the buffer pool is divided into pages that can potentially hold multiple rows. For efficiency of cache management, the buffer pool is implemented as a linked list of pages; data that is rarely used is aged out of the cache using a variation of the least recently used (LRU) algorithm.

If the size of the buffer pool is inadequate or if the buffer pool is poorly sized, then, more often than not, the requests may not be serviced by the buffer pool. Similarly if too many pages in the buffer pool are held for administrative purpose or are being held for read/write operations, then, InnoDB may not be able to cache the tables and data in the buffer pool. If the required data is not available in the buffer pool, requests may have to be serviced directly from the disk Which may cause severe overheads. To avoid such overheads, it is necessary for the administrators to constantly keep a vigil on the sizing of the buffer pool and the memory allocated to the buffer pool. The MySQL InnoDb Memory test helps administrators in this regard!

This test monitors the InnoDB buffer pool and reports the amount of memory allocated to the buffer pool and the amount of memory utilized by the buffer pool. The statistics reported by this test help administrators analyze the usage of the buffer pool, and provides them with useful pointers to fine-tune the configuration of the buffer pool.

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 target database server instance being monitored

Configurable parameters for the test
Parameter Description

Test Period

How often should the test be executed.

Host

The IP address of the MySQL server.

Port

The port at which the specified host listens.

Database

Specify the name of a database on the target MySQL database server being monitored

Username 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 MySQL server. To know how to create such a user, refer to Pre-requisites for Monitoring the MySQL Server topic .

Confirm Password

Confirm the password by retyping it here.

Measurements made by the test
Measurement Description Measurement Unit Interpretation

Total memory allocated

Indicates the total amount of memory allocated to the buffer pool.

MB

 

Free memory

Indicates the amount of memory available for use in the pages allocated to the buffer pool.

MB

A high value is desired for this measure.

Used memory

Indicates the amount of memory that is already utilized in the pages allocated to the buffer pool.

MB

A value close to Total memory allocated measure is an indication that the buffer pool is currently running out of memory.

Used percentage for Maximum size

Indicates the percentage of memory utilized by the pages allocated to the buffer pool.

Percent

A low value is desired for this measure.

A value close to 100 indicates that the buffer pool is running out of memory resources.

The value of this measure is calculated as follows:

(Used memory / Total memory allocated)*100

Additional memory allocated

Indicates the amount of memory that is additionally allocated to the buffer pool.

MB

MySQL server stores details such as metadata accessed by the users, internal information structure etc in the additional memory allocated to the buffer pool.

Dictionary memory allocated

Indicates the amount of memory allocated for the data dictionary of the InnoDB storage engine.

MB

 

Total page size

Indicates the total size of the buffer pool, in pages.

MB

The value of this measure is a sum of Data page size, Dirty page size and Misc page size measures.

Data page size

Indicates the size of the pages containing data in the buffer pool.

MB

 

Dirty page size

Indicates the amount of bytes held in the dirty pages available in the buffer pool.

MB

 

Misc page size

Indicates the size of the pages that have been allocated for administrative overhead, such as row locks or the adaptive hash index.

MB

 

Latched pages

Indicates the number of latched pages in the buffer pool.

Number

These are pages currently being read or written, or that cannot be flushed or removed for some other reason.

Flushed page size

Indicates the size of pages that were flushed from the buffer pool.

MB