Instance memory statistics

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to monitor and collect statistics related to memory usage on MariaDB instances, specifically targeting instance memory configurations and usage to optimize performance and prevent issues relating to insufficient memory allocation.
• Why: This job is important as it ensures the database performs optimally by tweaking memory settings based on actual usage and provides a safeguard against performance degradation that might result from poorly configured memory settings. The gathered data aids in proactive database management, ensuring high availability and reliability.
• Manual checking: You can check memory statistics manually in the MariaDB database by using SQL commands to query system tables for buffer sizes, thread stack, and other memory-related variables.

SELECT variable_name, variable_value FROM information_schema.GLOBAL_VARIABLES WHERE variable_name LIKE '%size%' OR variable_name = 'THREAD_STACK';

Key Features

• The monitoring job:
  • Collects key memory buffer sizes and configurations.
  • Provides reports on static and dynamic memory usage.
  • Generates memory usage statistics with a breakdown by total, static, thread, heap, and temporary table memory.
  • Schedules reports to run at regular intervals automatically.

• The reports include visual presentations and data on various memory configurations, such as:
  • Buffer sizes for keys, caches, and logs.
  • Memory allocated per server thread.
  • Memory pool sizes for optimizations.

Implementation Overview

The complexity of monitoring and managing a MariaDB instance’s memory involves:

• Creating necessary database tables to store historic and current memory statistics.
• Implementing procedures to periodically capture memory usage data.
• Managing dependencies and ensuring data integrity and cleanup on job failure.

• “Express install” and “force install” settings are set to true, indicating automatic and mandatory application of this monitoring setup upon deployment.

Dependencies and Cleanup

The job has dependencies upon several internal objects and procedures:

On failure, the job is designed to clean itself up, removing the tables and procedures it uses to prevent clutter or partial data collection scenarios.

Data Collection and Reporting Mechanism

• Memory statistics are sorted by several categories and reported through a sophisticated querying system that sizes various buffers and memory pools.
• The data is represented both in tabular format and graphically, illustrating trends over time to assist in detecting spikes or drops in memory usage.

• Historical data tracking helps in understanding patterns and can provide insights for capacity planning or scaling decisions.

Scheduling

• The default schedule for the main memory statistics collection is every hour, ensuring timely updates on the status and configuration of memory usage.
• Detailed statistics on specific memory parameters are collected at specific times (10, 30, 50 minutes past each hour), enabling finer resolution and timely alerts.

Additional Notes

• This job is tailored for MariaDB instances where performance schema and engine status are enabled, targeting scenarios specific to setups optimized for performance monitoring.
• Such detailed monitoring is crucial in environments where MariaDB handles large volumes of transactions or vast datasets, necessitating fine-tuned memory management for efficiency.