Instance memory statistics

Job details

Parameters

Job Summary

• Purpose:
  The purpose of this job is to collect instance memory statistics of a MySQL database using the dbWatch Control Center. It helps to review the memory usage against parameters such as static, thread, heap, repair thread, and temporary tables memory.

• Why:
  Monitoring the memory usage on a MySQL server can help troubleshoot performance issues. If there are sudden spikes in memory usage, there may be a problem with the server or the database which needs to be addressed.

• Manual checking:
  You can check this manually in the database by querying the tables dbw_instance_memory_statistics_histr, dbw_instance_memory_statistics_last, and dbw_instance_memory_statistics_mode after they have been created and populated by the running task.

SELECT * FROM dbw_instance_memory_statistics_histr

SELECT * FROM dbw_instance_memory_statistics_last

SELECT * FROM dbw_instance_memory_statistics_mode

Tables Summary

The following tables are created and manipulated by the task:

• dbw_instance_memory_statistics_histr: This table contains a history of total memory, static memory, thread memory, heap memory, repair thread memory, and temporary table memory along with the timestamp it was recorded.
• dbw_instance_memory_statistics_last: This table contains values for various parameters that are used to calculate memory.
• dbw_instance_memory_statistics_mode: This table contains the name and value of variable parameters.

Dependency Summary

The task has dependencies on the following objects:

• dbw_instance_memory_statistics: Has dependency to itself.
• dbw_instance_memory_statistics_histr, dbw_instance_memory_statistics_last, and dbw_instance_memory_statistics_mode: These are dependency tables. If the task fails, these tables will be dropped as part of cleanup.

Reports

A report is generated based on the data collected by the task. The report includes information about the memory setup of the MySQL server, static memory, thread memory, in-memory tables, and memory usage history. The report can be used to monitor and optimize memory utilization.

For example, the ‘Thread memory’ section of the report indicates the memory allocated per connection to the MySQL server, which can be useful for performance tuning. The ‘In-memory tables’ section describes the use and interaction of ‘max_heap_table_size’ and ‘tmp_table_size’ in creating in-memory tables.

The ‘Memory usage history’ provides a tabulated and graphical representation of the total, static, thread, heap, and tmp tables memory over time which can be beneficial in spotting patterns and trends. The memory is measured in MB and the times are represented in hourly intervals.