No obvious explanation in the VARIABLES and STATUS.
Analysis of GLOBAL STATUS and VARIABLES:
Observations:
- Version: 8.0.20
- 60 GB of RAM
- Uptime = 04:49:11; some GLOBAL STATUS values may not be meaningful yet.
- You are running on Windows.
- 4.89 Queries/sec : 3.43 Questions/sec
The More Important Issues:
Almost nothing is going on. I am having trouble imagining that MySQL cased the crash.
Lower max_connections
to 500. (There have not been more than 23 concurrent connections since startup.)
tmp_table_size = 500M -- it is currently dangerously high for the amount of RAM you have.
innodb_doublewrite = ON
Details and other observations:
( innodb_lru_scan_depth * innodb_page_cleaners ) = 1,024 * 4 = 4,096
-- Amount of work for page cleaners every second.
-- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixable by lowering lru_scan_depth: Consider 1000 / innodb_page_cleaners (now 4). Also check for swapping.
( innodb_lru_scan_depth ) = 1,024
-- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixed by lowering lru_scan_depth
( Innodb_buffer_pool_pages_free * 16384 / innodb_buffer_pool_size ) = 2,478,311 * 16384 / 38912M = 99.5%
-- buffer pool free
-- buffer_pool_size is bigger than working set; could decrease it
( innodb_io_capacity ) = 200
-- When flushing, use this many IOPs.
-- Reads could be slugghish or spiky.
( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 2,478,311 / 2490368 = 99.5%
-- Pct of buffer_pool currently not in use
-- innodb_buffer_pool_size (now 40802189312) is bigger than necessary?
( innodb_io_capacity_max / innodb_io_capacity ) = 2,000 / 200 = 10
-- Capacity: max/plain
-- Recommend 2. Max should be about equal to the IOPs your I/O subsystem can handle. (If the drive type is unknown 2000/200 may be a reasonable pair.)
( Innodb_buffer_pool_bytes_data / innodb_buffer_pool_size ) = 196,214,784 / 38912M = 0.48%
-- Percent of buffer pool taken up by data
-- A small percent may indicate that the buffer_pool is unnecessarily big.
( innodb_doublewrite ) = innodb_doublewrite = OFF
-- Extra I/O, but extra safety in crash.
-- OFF is OK for FusionIO, Galera, Replicas, ZFS.
( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 3,944,448 / (17351 / 3600) / 2 / 48M = 0.00813
-- Ratio
-- (see minutes)
( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 17,351 / 60 * 48M / 3944448 = 3,690
-- Minutes between InnoDB log rotations Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf.
-- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size (now 50331648). (Cannot change in AWS.)
( innodb_flush_method ) = innodb_flush_method = unbuffered
-- How InnoDB should ask the OS to write blocks. Suggest O_DIRECT or O_ALL_DIRECT (Percona) to avoid double buffering. (At least for Unix.) See chrischandler for caveat about O_ALL_DIRECT
( innodb_io_capacity ) = 200
-- I/O ops per second capable on disk . 100 for slow drives; 200 for spinning drives; 1000-2000 for SSDs; multiply by RAID factor.
( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = ON
-- Usually should be ON.
-- There are cases where OFF is better. See also innodb_adaptive_hash_index_parts (now 8) (after 5.7.9) and innodb_adaptive_hash_index_partitions (MariaDB and Percona). ON has been implicated in rare crashes (bug 73890). 10.5.0 decided to default OFF.
( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF
-- Whether to log all Deadlocks.
-- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.
( max_connections ) = 2,000
-- Maximum number of connections (threads). Impacts various allocations.
-- If max_connections (now 2000) is too high and various memory settings are high, you could run out of RAM.
( bulk_insert_buffer_size ) = 8 / 61440M = 0.01%
-- Buffer for multi-row INSERTs and LOAD DATA
-- Too big could threaten RAM size. Too small could hinder such operations.
( tmp_table_size ) = 4096M
-- Limit on size of MEMORY temp tables used to support a SELECT
-- Decrease tmp_table_size (now 4294967296) to avoid running out of RAM. Perhaps no more than 64M.
( Select_full_join / Com_select ) = 15,198 / 31082 = 48.9%
-- % of selects that are indexless joins
-- Add suitable index(es) to tables used in JOINs.
( Com_admin_commands / Queries ) = 25,348 / 84808 = 29.9%
-- Percent of queries that are "admin" commands.
-- What's going on?
( long_query_time ) = 10
-- Cutoff (Seconds) for defining a "slow" query.
-- Suggest 2
( log_slow_slave_statements ) = log_slow_slave_statements = OFF
-- (5.6.11, 5.7.1) By default, replicated statements won't show up in the slowlog; this causes them to show.
-- It can be helpful in the slowlog to see writes that could be interfering with Replica reads.
( back_log ) = 80
-- (Autosized as of 5.6.6; based on max_connections)
-- Raising to min(150, max_connections (now 2000)) may help when doing lots of connections.
( Max_used_connections / max_connections ) = 23 / 2000 = 1.1%
-- Peak % of connections
-- Since several memory factors can expand based on max_connections (now 2000), it is good not to have that setting too high.
( Com_change_db / Connections ) = 25,413 / 311 = 81.7
-- Database switches per connection
-- (minor) Consider using "db.table" syntax
( Aborted_connects / Connections ) = 227 / 311 = 73.0%
-- Perhaps a hacker is trying to break in? (Attempts to connect)
Abnormally small:
10 * read_buffer_size = 0.6MB
Com_insert = 4.4 /HR
Handler_read_next = 16 /sec
Innodb_buffer_pool_reads * innodb_page_size / innodb_buffer_pool_size = 0.47%
Innodb_dblwr_pages_written = 0
Innodb_rows_updated = 0.62 /HR
back_log / max_connections = 4.0%
innodb_doublewrite_files = 0
innodb_doublewrite_pages = 0
Abnormally large:
Com_create_db = 0.21 /HR
Com_create_table = 92 /HR
Com_show_charsets = 1.7 /HR
Com_show_plugins = 0.41 /HR
Com_show_storage_engines = 0.41 /HR
Innodb_buffer_pool_pages_free = 2.48e+6
Innodb_system_rows_deleted = 0.1 /sec
Innodb_system_rows_inserted = 0.1 /sec
Innodb_system_rows_updated = 0.32 /sec
Ssl_accepts = 304
Ssl_default_timeout = 7,200
Ssl_finished_accepts = 304
Ssl_session_cache_hits = 290
Ssl_session_cache_timeouts = 5
Ssl_verify_depth = 4.29e+9
Ssl_verify_mode = 5
gtid_executed_compression_period = 0.058 /sec
innodb_thread_concurrency = 21
max_error_count = 1,024
max_length_for_sort_data = 4,096
optimizer_trace_offset = --1
performance_schema_max_cond_classes = 100
performance_schema_max_mutex_classes = 300
performance_schema_max_rwlock_classes = 60
performance_schema_max_stage_classes = 175
performance_schema_max_statement_classes = 218
performance_schema_max_thread_classes = 100
Abnormal strings:
event_scheduler = ON
ft_boolean_syntax = + -><()~*:\"\"&
have_query_cache = NO
innodb_fast_shutdown = 1
innodb_temp_tablespaces_dir = .\\#innodb_temp\\
lower_case_file_system = ON
lower_case_table_names = 1
mysqlx_compression_algorithms = DEFLATE_STREAM,LZ4_MESSAGE,ZSTD_STREAM
optimizer_trace = enabled=off,one_line=off
optimizer_trace_features = greedy_search=on, range_optimizer=on, dynamic_range=on, repeated_subselect=on
protocol_compression_algorithms = zlib,zstd,uncompressed
slave_rows_search_algorithms = INDEX_SCAN,HASH_SCAN