Instance memory statistics

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to monitor and record the memory usages statistics of a MariaDB instance. It focuses on various memory components like static, thread, heap, and temporary tables across different time intervals.
• Why: This job is essential to ensure efficient memory management and to optimize database performance. Monitoring memory usage helps in preempting potential system outages or slowdowns and facilitates proactive tuning of the database configuration.
• Manual Checking: To manually check memory statistics directly from the database, you can use the following SQL commands:

SELECT 'name', 'value' FROM dbw_instance_memory_statistics_last UNION SELECT 'variable_name', 'variable_value' FROM dbw_instance_memory_statistics_mode;

Tables and Procedures

• Tables:
  • dbw_instance_memory_statistics_histr: Tracks historical data of total memory and its breakdown.
  • dbw_instance_memory_statistics_last: Stores the latest snapshot of various buffer sizes and limits.
  • dbw_instance_memory_statistics_mode: Contains mode settings related to memory configurations.

• Procedures:
  • dbw_instance_memory_statistics: A comprehensive procedure that calculates and logs the memory usage statistics; initiated based on a trigger or schedule.

Dependencies

• This task has self dependencies as well as dependencies on the tables it creates for storing data. On failure, it ensures a clean database state by dropping all associated tables and procedures.

Memory Components Monitored

• Static Memory: Includes buffers like key-buffer, binlog-cache, and Innodb-buffer pool which are essential for the operation of MariaDB.
• Thread Memory: Memory assigned per connection or thread; includes buffers used during query operations like sort and read operations.
• Heap Memory: Used by in-memory tables and can affect performance of temporary table operations.
• Temporary Table Memory: Specifically tracks memory used by temporary tables.

Implementation Details

This job is implemented through SQL scripts that create necessary tables and procedures for capturing and storing memory usage statistics. The script captures both static and dynamic configurations and updates them periodically as per the default schedule.

Reporting

• The report generated by dbWatch Control Center for this job will provide insights into memory usage over the previous days, with specific details on the size of various buffers and caches.
• Visual presentations in the report will help in identifying trends and spikes in memory usage.

Conclusion

The dbWatch job for monitoring instance memory statistics in a MariaDB setup is crucial for database health and performance optimization. By continuously tracking key memory metrics, system administrators can take informed decisions to tweak system settings, preventing potential crashes or performance bottlenecks.