Health Monitor samples disk space information, system memory information, and process memory information every sixty seconds, and stores a day's worth of data in tables in the Performance Schema of the MySQL Server. If the available disk space or available memory falls below certain preset thresholds, Health Monitor issues warnings and logs it to the performance_schema.error_log table.

Health Monitor uses MiB/GiB/TiB storage units where 100 GB = 93 GiB.

The total storage available to a DB system contains the following:

• Available storage: The disk space after the storage reserve is excluded from the total storage.
• Storage reserve: The disk space that is lesser of the disk_low_space_level and disk_low_space_percent configuration variables. The default value of disk_low_space_level is 5,000 MiB, and the default value of disk_low_space_percent is 4% of the total storage.
• Critical storage: The disk space in the storage reserve below the critical threshold, which is defined by the disk_low_space_critical_level configuration variable. This is the minimum viable disk space for the system to avoid disk exhaustion. The default critical storage space is 2,000 MiB.

Disk Space Warnings

When the disk space falls below certain percentage of the available storage, Health Monitor issues the following warnings and logs it to the performance_schema.error_log table:

• WARNING_DISK_USAGE_LEVEL_1: When the disk space falls below 20% of the available storage.
• WARNING_DISK_USAGE_LEVEL_2: When the disk space falls below 10% of the available storage.
• WARNING_DISK_USAGE_LEVEL_3: When the disk space falls below 5% of the available storage.

For example, if the total storage is 100,000 MiB, and the storage reserve is lesser of the following two configuration variables:

• disk_low_space_level : The default value is 5,000 MiB.
• disk_low_space_percent: It is 4% of the total storage, that is, 4% of 100,000 MiB = 4,000 MiB.

The storage reserve is 4,000 MiB, which is lesser of the two configuration variables.

You can find the available storage by excluding the storage reserve from the total storage:

100,000 - 4,000 = 96,000 MiB

Health Monitor issues the following warnings:

• WARNING_DISK_USAGE_LEVEL_1: When the disk space falls below 20% of the available storage, which is 20/100*96,000 = 19,200 MiB.
• WARNING_DISK_USAGE_LEVEL_2: When the disk space falls below 10% of the available storage, which is 10/100*96,000 = 9,600 MiB.
• WARNING_DISK_USAGE_LEVEL_3: When the disk space falls below 5% of the available storage, which is 5/100*96,000 = 4,800 MiB.

Significant or Sustained Shortage of Disk Space

Health Monitor defines significant or sustained shortage of disk space when the following conditions are met:

• Significant shortage: The disk space drops below the critical threshold defined by disk_low_space_critical_level.
• Sustained shortage: The disk space drops below the storage reserve, and then remains in the storage reserve for the duration defined by the disk_low_space_duration configuration variable.

When there is a significant or sustained shortage of disk space, Health Monitor does the following:

• SUPER_READ_ONLY=ON: Sets the system variables, SUPER_READ_ONLY, to ON in the MySQL Server. The MySQL Server rejects all new incoming SQL write statements (UPDATE, INSERT, DELETE, and DDL), regardless of users and privileges. Running transactions are allowed to complete, but new writes are prohibited until the disk space is available again. You cannot load data into the HeatWave cluster when the server is in SUPER_READ_ONLY mode.

  In some cases, global read locks, ongoing commits, or metadata locks blocks the Health Monitor from setting the SUPER_READ_ONLY system variable. In such cases, if you set the health_monitor.disk_fallback_force variable, the Health Monitor identifies and terminates active queries holding global locks to set the SUPER_READ_ONLY system variable.

• OFFLINE_MODE=ON: Sets the system variable, OFFLINE_MODE, to ON in the MySQL Server. The MySQL Server disconnects client users who do not have the CONNECTION_ADMIN privilege, terminates running statements and releases locks, and blocks new connections with an appropriate error.
• super_read_only_disk_full=ON: Sets the status variable, super_read_only_disk_full, to ON indicating that read-only mode was triggered due to low disk space. You can see the value of super_read_only_disk_full status variable with either of the following command:
  • SHOW GLOBAL STATUS WHERE variable_name = 'super_read_only_disk_full';
  • SELECT * FROM performance_schema.global_status WHERE variable_name = 'super_read_only_disk_full';

System variables change behavior of the MySQL Server while status variables are static indicators of the MySQL Server state.

Recovery to Normal Operation

Once the SUPER_READ_ONLY and OFFLINE_MODE are set, Health Monitor does the following:

• Check the disk space every minute .
• Set OFFLINE_MODE=OFF if the available disk space recovers and remains above disk_recovery_level for disk_recovery_time_1 seconds or more.
• Set SUPER_READ_ONLY=OFF if the available disk space recovers and remains above disk_recovery_level for an additional disk_recovery_time_2 seconds or more.

Normal operations are resumed once the disk space remains above disk_recovery_level for disk_recovery_time_1 + disk_recovery_time_2 seconds.

Once the disk space recovers, Health Monitor clears the system variables, SUPER_READ_ONLY, and OFFLINE_MODE, and the status variable, super_read_only_disk_full. You do not have to restart the MySQL instance to clear the variables.

If there is a significant or sustained shortage of disk space again, then the Health Monitor again sets the system variables, SUPER_READ_ONLY and OFFLINE_MODE, and the status variable, super_read_only_disk_full, to ON in the MySQL Server.

Before returning a primary instance of a high availability DB system to normal operating mode, the Health Monitor verifies that the server is online with the group majority. The Health Monitor never sets SUPER_READ_ONLY or OFFLINE_MODE on the secondary instances.

Recovery Increment, Recovery Max Cycle, and Recovery Window

Each time the MySQL Server recovers from a significant or sustained shortage of disk space, the Health Monitor increases the disk recovery time by disk_recovery_increment seconds.

For example, if disk_recovery_time_1 = 300 seconds, and disk_recovery_time_2 = 600 seconds, and disk_recovery_increment = 400 seconds, then the disk recovery time for each cycle is as following:

• Cycle 1: disk_recovery_time_1 = 300 seconds, disk_recovery_time_2 = 600 seconds
• Cycle 2: disk_recovery_time_1 = (300 + 400) seconds, disk_recovery_time_2 = (600+400) seconds
• Cycle 3: disk_recovery_time_1 = (300+400+400) seconds, disk_recovery_time_2 = (600+400+400) seconds

The Health Monitor restores to the normal operating mode a maximum of disk_recovery_max_cycles times within a fixed window of disk_recovery_window seconds, from the last recovery cycle. For example, if disk_recovery_max_cycles is set to 5, and disk_recovery_window is set to 86,400 seconds (1 day), then the Health Monitor can restore to the normal operating mode five times in a window of 86,400 seconds. If a significant or sustained shortage happens the sixth time in the same window, the system variables, SUPER_READ_ONLY and OFFLINE_MODE are set to ON.

The recovery count is reset after disk_recovery_window seconds from the last recovery.

Memory Warnings

When the available memory falls below certain thresholds, Health Monitor issues the following warnings:

• WARNING_MEMORY_USAGE_LEVEL_1: When the available memory falls below 1024 MiB.
• WARNING_MEMORY_USAGE_LEVEL_2: When the available memory falls below 500 MiB.
• WARNING_MEMORY_USAGE_LEVEL_3: When the available memory falls below 100 MiB.

Health Monitor logs the information regarding MySQL version, global memory usage, and various configuration settings in the error log. The information is logged as comma separated values for easy transfer into a spreadsheet for further analysis.

Health Monitor tables store monitoring data and statistics in MySQL Server's Performance Schema.

The health tables contain disk storage information, system memory information, and process memory information.

The statistics tables contain statistics for system CPU usage, disk usage, memory usage, and the MySQL server process (mysqld). Each statistics table has two versions, an on-demand version, which is populated with current readings when you query it, and a history version for which Health Monitor takes samples once a minute, and retains a day's worth of data. Both versions of the table have the same columns.

• Using a Command-Line Client
• Health Monitor Tables

The Health Monitor variables are configuration settings for the monitoring activity in the DB system. You cannot change the values of these variables.

• Using a Command-Line Client
• Health Monitor Variables

The Health Monitor errors, warnings, and status updates are logged in performance_schema.error_log. Health Monitor abbreviates TiB as T, GiB as G and MiB as M in the error log. For example, 306.6G = 306.6 GiB = 329.2 GB.

[Note] [MY-013694] [Health] Disk Collector: Started: running=ON, monitored='/db', 
sample rate=30s, retention=86400s, max samples=2880, status interval=1, low level=100 MiB, 
low percent=4%, critical level=10 MiB, warning levels=20/10/5%, fallback (enable=ON, force=ON), 
low space duration=300s, recovery (enable=ON, level=5%, time 1=300s, time 2=300s, max cycles=3,
increment=10, window=86400)

[Warning] [MY-013695] [Health] Disk: Warning Level 2 (62M): monitored='/db', 
available=59M, total=466M, used=87.2%, low limit=18M, critical=10M, recovery=40M, 
warnings=107M/62M/40M

[Warning] [MY-013695] [Health] Disk: Low Level (18M): Fallback Timer Running: 300 seconds 
until SUPER_READ_ONLY: monitored='/db', available=15M, total=466M, used=96.6%, low limit=18M, 
critical=10M, recovery=40M, warnings=107M/62M/40M

[Warning] [MY-013695] [Health] Disk: Low Level (18M): Fallback Timer Running: 30 seconds until 
SUPER_READ_ONLY: monitored='/db', available=15M, total=466M, used=96.6%, low limit=18M, critical=10M, 
recovery=40M, warnings=107M/62M/40M
[Warning] [MY-013695] [Health] Disk: Low Level (18M): Fallback Timer Expired
[Warning] [MY-013695] [Health] Disk: Low Level (18M): OFFLINE_MODE = ON
[Warning] [MY-013695] [Health] Disk: Low Level (18M): SUPER_READ_ONLY = ON

[Warning] [MY-013695] [Health] Disk Collector: CRITICAL LEVEL (2G): 
OFFLINE_MODE=ON: mount point='/db', available=1.9G, total=1024G, 
used=99.1%, low limit=5.0G, critical=2.0G, warnings=204G/102G/51G 
[Warning] [MY-013695] [Health] Disk Collector: CRITICAL LEVEL (2G): 
SUPER_READ_ONLY=ON: mount point='/db', available=1.9G, total=1024G, 
used=99.1%, low limit=5.0G, critical=2.0G, warnings=204G/102G/51G

[Warning] [MY-013695] [Health] Disk: Low Level (18M): Fallback Timer Canceled
[Warning] [MY-013695] [Health] Disk: Warning Level 1 (107M): monitored='/db', available=79M, 
total=466M, used=82.9%, low limit=18M, critical=10M, recovery=40M, warnings=107M/62M/40M

[Warning] [MY-013695] [Health] MEMORY: WARNING LEVEL 1 (1.0G): available=950M, total=7.5G, used=87.6%, 
mysqld=5.9G, warnings=1.0G/500M/100M
[Warning] [MY-013695] [Health]  
[Warning] [MY-013695] [Health] ===== MEMORY DIAGNOSTICS BEGIN (csv) =====

---------------------------------------
TOTAL ALLOCATED MEMORY AND BUFFER SIZES
---------------------------------------
keyword,value
mysql_version,8.0.33
total_allocated,15121493496
innodb_buffer_pool_size,13958643712
join_buffer_size,262144
----------------------------------------
MEMORY AGGREGATION PER LOGICAL COMPONENT
----------------------------------------
component,current_count,current_alloc,max_alloc,min_alloc
...<csv data>...

-----------------------
MEMORY USAGE PER THREAD
-----------------------
thread_id,user,current_count_used,current_allocated,current_avg_alloc,current_max_alloc,total_allocated
...<csv data>...

-----------------------
MEMORY USAGE PER USER
-----------------------
user,current_count_used,current_allocated,current_avg_alloc,current_max_alloc,total_allocated
...<csv data>...

-------------------
PROCESSLIST DETAILS
-------------------
thd_id,conn_id,user,db,command,state,time,current_statement,execution_engine,statement_latency,progress,
lock_latency,cpu_latency,rows_examined,rows_sent,rows_affected,tmp_tables,tmp_disk_tables,full_scan,
last_statement,last_statement_latency,current_memory,last_wait,last_wait_latency,source,trx_latency,
trx_state,trx_autocommit,pid,program_name
...<csv data>...

---------------
SESSION DETAILS
---------------
thd_id,conn_id,user,db,command,state,time,current_statement,execution_engine,statement_latency,progress,
lock_latency,cpu_latency,rows_examined,rows_sent,rows_affected,tmp_tables,tmp_disk_tables,full_scan,
last_statement,last_statement_latency,current_memory,last_wait,last_wait_latency,source,trx_latency,
trx_state,trx_autocommit,pid,program_name
...<csv data>...

--------------------------------------------------------
CURRENT STATEMENT PER THREAD ORDERED BY STATEMENT MEMORY
--------------------------------------------------------
THREAD_ID,EVENT_ID,END_EVENT_ID,EVENT_NAME,SOURCE,TIMER_START,TIMER_END,TIMER_WAIT,LOCK_TIME,SQL_TEXT,
DIGEST,DIGEST_TEXT,CURRENT_SCHEMA,OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,OBJECT_INSTANCE_BEGIN,MYSQL_ERRNO,
RETURNED_SQLSTATE,MESSAGE_TEXT,ERRORS,WARNINGS,ROWS_AFFECTED,ROWS_SENT,ROWS_EXAMINED,CREATED_TMP_DISK_TABLES,
CREATED_TMP_TABLES,SELECT_FULL_JOIN,SELECT_FULL_RANGE_JOIN,SELECT_RANGE,SELECT_RANGE_CHECK,SELECT_SCAN,
SORT_MERGE_PASSES,SORT_RANGE,SORT_ROWS,SORT_SCAN,NO_INDEX_USED,NO_GOOD_INDEX_USED,NESTING_EVENT_ID,
NESTING_EVENT_TYPE,NESTING_EVENT_LEVEL,STATEMENT_ID,CPU_TIME,MAX_CONTROLLED_MEMORY,MAX_TOTAL_MEMORY,
EXECUTION_ENGINE
...<csv data>...

----------------------------------------------------------
CURRENT STATEMENT PER THREAD FOR THREADS USING MOST MEMORY
----------------------------------------------------------
THREAD_ID,EVENT_ID,END_EVENT_ID,EVENT_NAME,SOURCE,TIMER_START,TIMER_END,TIMER_WAIT,LOCK_TIME,SQL_TEXT,
DIGEST,DIGEST_TEXT,CURRENT_SCHEMA,OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,OBJECT_INSTANCE_BEGIN,MYSQL_ERRNO,
RETURNED_SQLSTATE,MESSAGE_TEXT,ERRORS,WARNINGS,ROWS_AFFECTED,ROWS_SENT,ROWS_EXAMINED,CREATED_TMP_DISK_TABLES,
CREATED_TMP_TABLES,SELECT_FULL_JOIN,SELECT_FULL_RANGE_JOIN,SELECT_RANGE,SELECT_RANGE_CHECK,SELECT_SCAN,
SORT_MERGE_PASSES,SORT_RANGE,SORT_ROWS,SORT_SCAN,NO_INDEX_USED,NO_GOOD_INDEX_USED,NESTING_EVENT_ID,
NESTING_EVENT_TYPE,NESTING_EVENT_LEVEL,STATEMENT_ID,CPU_TIME,MAX_CONTROLLED_MEMORY,MAX_TOTAL_MEMORY,
EXECUTION_ENGINE,thread_id
...<csv data>...

----------------------------------------------------------
STATEMENT HISTORY PER THREAD FOR THREADS USING MOST MEMORY
----------------------------------------------------------
THREAD_ID,EVENT_ID,END_EVENT_ID,EVENT_NAME,SOURCE,TIMER_START,TIMER_END,TIMER_WAIT,LOCK_TIME,SQL_TEXT,
DIGEST,DIGEST_TEXT,CURRENT_SCHEMA,OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,OBJECT_INSTANCE_BEGIN,MYSQL_ERRNO,
RETURNED_SQLSTATE,MESSAGE_TEXT,ERRORS,WARNINGS,ROWS_AFFECTED,ROWS_SENT,ROWS_EXAMINED,CREATED_TMP_DISK_TABLES,
CREATED_TMP_TABLES,SELECT_FULL_JOIN,SELECT_FULL_RANGE_JOIN,SELECT_RANGE,SELECT_RANGE_CHECK,SELECT_SCAN,
SORT_MERGE_PASSES,SORT_RANGE,SORT_ROWS,SORT_SCAN,NO_INDEX_USED,NO_GOOD_INDEX_USED,NESTING_EVENT_ID,
NESTING_EVENT_TYPE,NESTING_EVENT_LEVEL,STATEMENT_ID,CPU_TIME,MAX_CONTROLLED_MEMORY,MAX_TOTAL_MEMORY,
EXECUTION_ENGINE,thread_id
...<csv data>...

-----------------------------
EVENTS ALLOCATING MOST MEMORY
-----------------------------
processlist_id,processlist_user,thread_id,event_name,count_alloc,count_free,current_count_used,
current_number_of_bytes_used
...<csv data>...

----------------------
BUFFER POOL STATISTICS
----------------------
POOL_ID,POOL_SIZE,FREE_BUFFERS,DATABASE_PAGES,OLD_DATABASE_PAGES,MODIFIED_DATABASE_PAGES,PENDING_DECOMPRESS,
PENDING_READS,PENDING_FLUSH_LRU,PENDING_FLUSH_LIST,PAGES_MADE_YOUNG,PAGES_NOT_MADE_YOUNG,PAGES_MADE_YOUNG_RATE,
PAGES_MADE_NOT_YOUNG_RATE,NUMBER_PAGES_READ,NUMBER_PAGES_CREATED,NUMBER_PAGES_WRITTEN,PAGES_READ_RATE,
PAGES_CREATE_RATE,PAGES_WRITTEN_RATE,NUMBER_PAGES_GET,HIT_RATE,YOUNG_MAKE_PER_THOUSAND_GETS,
NOT_YOUNG_MAKE_PER_THOUSAND_GETS,NUMBER_PAGES_READ_AHEAD,NUMBER_READ_AHEAD_EVICTED,READ_AHEAD_RATE,
READ_AHEAD_EVICTED_RATE,LRU_IO_TOTAL,LRU_IO_CURRENT,UNCOMPRESS_TOTAL,UNCOMPRESS_CURRENT
...<csv data>...