Instance memory statistics

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to monitor the memory statistics and dependencies of a MySQL database instance and generate a detailed report, to ensure that the instance is optimally using memory resources and potentially help developers understand where bottlenecks or inefficiencies may be occurring.
• Why: Analyzing memory utilization helps in identifying the areas that could lead to memory bottlenecks. With this information, database managers can adjust database settings to optimize memory usage and improve overall database performance. This job helps in maintaining the health of the MySQL server by detecting if the defined thresholds are reached.
• Manual checking: This monitoring job extracts detailed memory statistics and dependencies from your MySQL database instance. The equivalent SQL commands can be found within the implementation code block.

Reporting

A report titled “Memory statistics” will be generated providing information about the memory setup of the MySQL server. The report consists of 4 main sections:

1. Server memory statistics: Provides statistics about various elements including key buffer size, binary log cache size, InnoDB buffer pool size, InnoDB log buffer size and InnoDB additional memory pool size. It also contains elaborate descriptions about what each of these statistics represent.
2. Thread memory: Gives statistics for each connection to the server. Metrics provided include bulk insert buffer size, read buffer size, read binary index buffer size, and sort buffer size.
3. In-memory tables: This section provides statistics about maximum heap table size and temporary table size.
4. Memory usage history: Provides a history of total memory usage, static memory, thread memory, heap memory, repair thread memory, and temporary table memory.

Each of these sections provides an overview of memory allocation to various components in the database by presenting the information in tables and graphs.

Dependencies

The job has several dependencies like `dbw_instance_memory_statistics_histr`, `dbw_instance_memory_statistics_last`, `dbw_instance_memory_statistics_mode`, and also `dbw_instance_memory_statistics` itself.

Cleanup on Failure

The cleanup process in case of job failure involves dropping certain tables like `dbw_instance_memory_statistics_histr`, `dbw_instance_memory_statistics_last`, and `dbw_instance_memory_statistics_mode` and dropping `dbw_instance_memory_statistics` procedure.

Installation and Execution

The job allows express and forced installation. It is designed to be compatible with instances that are of type ‘instance’, are MySQL branch and have an engine, with the use_global_variables_performance_schema set as ‘1′.