MySQL InnoDb Memory 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	Specify Host name of the server for which the test is to be configured in this text box.
Port	Specify the port to which the specified host listens in this text box.
Database(DB)	Specify the name of a database on the target MySQL database server being monitored in the Database text box.
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 in the Confirm Password text box.
Allow Public Key	By default, the Allow Public Key flag is set to No. But, if the specified USER is created with caching_sha2_password as the authentication plugin, then the eG agent can connect to the target database cluster using RSA public key. To this effect, you have to set Allow Public Key flag to Yes.
SSL	By default, the SSL flag is set to No, indicating that the target MySQL database server is not SSL-enabled by default. To enable the test to connect to an SSL-enabled MySQL database server , set the SSL flag to Yes.
Verify CA	If the eG agent is required to establish an encrypted connection with the target MySQL database server by authenticating the server's identity through verifying the server CA certificate, set Verify CA flag to Yes. By default, this flag is set to No.
Truststore Password	This parameter is applicable only if the Verify CA parameter is set to Yes. To verify the target server certificate, provide the password of the truststore file which contains the server CA certificate in the Truststore Password text box. By default, this parameter is set to none.
Confirm Password	Confirm the Password by retyping it in the Confirm Password text box.
Keystore Password	This parameter is applicable only if the Verify CA parameter is set to Yes. To establish a connection with the target MySQL database server , the eG agent needs to have access to the client certificate. For this provide the password of the keystore file which contains the client certificate in the Keystore Password text box. By default, this parameter is set to none.
Confirm Password	Confirm the Password by retyping it in the Confirm Password text box.

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 Total Memory	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 database 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