Analysis of STATUS and VARIABLES:

Observations:

• Version: 8.0.22
• 1500 GB of RAM
• Uptime = 07:48:45; some GLOBAL STATUS values may not be meaningful yet.
• You are not running on Windows.
• Running 64-bit version
• It appears that you are running both MyISAM and InnoDB.

The More Important Issues:

Migrate tables from MyISAM to InnoDB. (Note: MySQL 8.0 removes MyISAM.)

Unless there are more distinct tables to open, the aggressive raising of table_open_cache was a waste. Open_tables is only 3089.

Increase binlog_stmt_cache_size to, say, 1M. Do you have a lot of "big" transactions?

innodb_page_cleaners = 64 (to match "instances")

innodb_buffer_pool_size is much larger than necessary. How much InnoDB data do you have? It sounds like less than 100MB, not 10000MB. (Shrinking the buffer_pool, plus associated settings, won't make any difference. This is more point out that 1.5TB is overkill.)

innodb_max_dirty_pages_pct = 1 is rather agressive. Did he help? Hurt?

See if some groups of statements can be artificially combined into transactions -- to avoid some of the I/O overhead.

With NVMe drives, I would expect innodb_flush_neighbors = 0 to be slightly better than 2.

Aborted_connects / Connections = 73% -- very high

thread_cache_size = 1800 is overkill considering that Max_used_connections = 6. And that says that no more than about 6 of your 144 cores were being used simultaneously.

DROPping 12 tables per second? Also, some other DDL statements are unusually frequently used.

transaction_isolation = READ-UNCOMMITTED is unusual; was it deliberately chosen?

Details and other observations:

Conversion from MyISAM to InnoDB ( (key_buffer_size - 1.2 * Key_blocks_used * 1024) ) = ((65536M - 1.2 * 941173 * 1024)) / 1536000M = 4.2% -- Percent of RAM wasted in key_buffer. -- Decrease key_buffer_size (now 68719476736).

( Key_reads + Key_writes + Innodb_pages_read + Innodb_pages_written + Innodb_dblwr_writes + Innodb_buffer_pool_pages_flushed ) = (825047 + 14132741 + 519455 + 3947009 + 594784 + 3885938) / 28125 = 849 /sec -- IOPs? -- If the hardware can handle it, set innodb_io_capacity (now 6000) to about this value.

( Opened_tables ) = 133,638 / 28125 = 4.8 /sec -- Frequency of opening Tables -- increase table_open_cache (now 100000)

( Opened_table_definitions ) = 260,203 / 28125 = 9.3 /sec -- Frequency of opening .frm files -- Increase table_definition_cache (now 8000) and/or table_open_cache (now 100000).

( table_open_cache ) = 100,000 -- Number of table descriptors to cache -- Several hundred is usually good.

( Open_tables / table_open_cache ) = 3,089 / 100000 = 3.1% -- Cache usage (open tables + tmp tables) -- Optionally lower table_open_cache (now 100000)

( Binlog_stmt_cache_disk_use ) = 31,755 / 28125 = 1.1 /sec -- Freq of non-transactional binlog cache spilling to disk during transactions -- Increase binlog_stmt_cache_size (now 32768).

( innodb_page_cleaners / innodb_buffer_pool_instances ) = 4 / 64 = 0.0625 -- innodb_page_cleaners -- Recommend setting innodb_page_cleaners (now 4) to innodb_buffer_pool_instances (now 64)

( Innodb_buffer_pool_pages_free * 16384 / innodb_buffer_pool_size ) = 68,530,659 * 16384 / 1179648M = 90.8% -- buffer pool free -- buffer_pool_size is bigger than working set; could decrease it

( innodb_max_dirty_pages_pct ) = 1 -- When buffer_pool starts flushing to disk -- Are you experimenting?

( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 68,530,659 / 72M = 90.8% -- Pct of buffer_pool currently not in use -- innodb_buffer_pool_size (now 1236950581248) is bigger than necessary?

( innodb_io_capacity_max / innodb_io_capacity ) = 40,000 / 6000 = 6.67 -- 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_reads + Innodb_buffer_pool_pages_flushed) ) = ((375278 + 3885938) ) / 28125 = 151 /sec -- InnoDB I/O ( Innodb_buffer_pool_pages_flushed ) = 3,885,938 / 28125 = 138 /sec -- Writes (flushes)

( innodb_change_buffer_max_size ) = 50 -- Percent of buffer_pool that is used for "change buffer" -- a write cache for index changes.

( Innodb_buffer_pool_bytes_data / innodb_buffer_pool_size ) = 113,997,725,696 / 1179648M = 9.2% -- Percent of buffer pool taken up by data -- A small percent may indicate that the buffer_pool is unnecessarily big.

( Innodb_pages_written/Innodb_data_writes ) = 3,947,009/204503857 = 1.9% -- Seems like these values should be equal?

( Innodb_os_log_written ) = 264,829,232,640 / 28125 = 9416150 /sec -- This is an indicator of how busy InnoDB is. -- Very busy InnoDB.

( innodb_log_buffer_size ) = 1024M -- Suggest 2MB-64MB, and at least as big as biggest blob set in transactions.

( Innodb_log_writes ) = 199,642,879 / 28125 = 7098 /sec -- Very high

( Innodb_dblwr_writes ) = 594,784 / 28125 = 21 /sec -- "Doublewrite buffer" writes to disk. "Doublewrites" are a reliability feature. Some newer versions / configurations don't need them. -- (Symptom of other issues)

( innodb_flush_neighbors ) = 2 -- A minor optimization when writing blocks to disk. -- Use 0 for SSD drives; 1 for HDD.

( sync_binlog ) = 60 -- Use 1 for added security, at some cost of I/O =1 may lead to lots of "query end"; =0 may lead to "binlog at impossible position" and lose transactions in a crash, but is faster.

( max_connections ) = 6,000 -- Maximum number of connections (threads). Impacts various allocations. -- If max_connections (now 6000) is too high and various memory settings are high, you could run out of RAM.

( join_buffer_size ) = 262,144 / 1536000M = 0.00% -- 0-N per thread. May speed up JOINs (better to fix queries/indexes) (all engines) Used for index scan, range index scan, full table scan, each full JOIN, etc. -- If large, decrease join_buffer_size (now 262144) to avoid memory pressure. Suggest less than 1% of RAM. If small, increase to 0.01% of RAM to improve some queries.

( character_set_server ) = character_set_server = latin1 -- Charset problems may be helped by setting character_set_server (now latin1) to utf8mb4. That is the future default.

( net_buffer_length / max_allowed_packet ) = 16,384 / 1024M = 0.00%

( bulk_insert_buffer_size ) = 8 / 1536000M = 0.00% -- Buffer for multi-row INSERTs and LOAD DATA -- Too big could threaten RAM size. Too small could hinder such operations.

( Key_blocks_used * 1024 / key_buffer_size ) = 941,173 * 1024 / 65536M = 1.4% -- Percent of key_buffer used . High-water-mark. -- Lower key_buffer_size (now 68719476736) to avoid unnecessary memory usage.

( Key_writes / Key_write_requests ) = 14,132,741 / 28999183 = 48.7% -- key_buffer effectiveness for writes -- If you have enough RAM, it would be worthwhile to increase key_buffer_size (now 68719476736).

( Key_reads ) = 825,047 / 28125 = 29 /sec -- MyISAM index read (from disk) rate -- If you have enough RAM, it would be worthwhile to increase key_buffer_size (now 68719476736).

( Key_writes ) = 14,132,741 / 28125 = 502 /sec -- MyISAM index write (to disk) rate -- If you have enough RAM, it would be worthwhile to increase key_buffer_size (now 68719476736).

( Key_reads + Key_writes ) = (825047 + 14132741) / 28125 = 531 /sec -- MyISAM index I/O rate -- If you have enough RAM, it would be worthwhile to increase key_buffer_size (now 68719476736).

( (Queries-Questions)/Queries ) = (18774846-167787)/18774846 = 99.1% -- Fraction of queries that are inside Stored Routines. -- (Not bad if high; but it impacts the validity of some other conclusions.)

( tmp_table_size ) = 512M -- Limit on size of MEMORY temp tables used to support a SELECT -- Decrease tmp_table_size (now 536870912) to avoid running out of RAM. Perhaps no more than 64M.

( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (2249849 + 3957040 + 228264 + 161) / 6345757 = 1.01 -- Statements per Commit (assuming all InnoDB) -- Low: Might help to group queries together in transactions; High: long transactions strain various things.

( Select_scan ) = 190,626 / 28125 = 6.8 /sec -- full table scans -- Add indexes / optimize queries (unless they are tiny tables)

( Select_scan / Com_select ) = 190,626 / 166472 = 114.5% -- % of selects doing full table scan. (May be fooled by Stored Routines.) -- Add indexes / optimize queries

( Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi ) = (2249849 + 228264 + 36 + 161 + 3957040 + 94) / 28125 = 228 /sec -- writes/sec -- 50 writes/sec + log flushes will probably max out I/O write capacity of normal drives

( Com_alter_table ) = 1,892 / 28125 = 0.067 /sec -- Why so many ALTERs?

( Com__biggest ) = Com__biggest = Com_begin -- Which of the "Com_" metrics is biggest. -- Normally it is Com_select (now 166472). If something else, then it may be a sloppy platform, or may be something else.

( binlog_format ) = binlog_format = MIXED -- STATEMENT/ROW/MIXED. -- ROW is preferred by 5.7 (10.3)

( expire_logs_days ) = 0 -- How soon to automatically purge binlog (after this many days). Being replaced by binlog_expire_logs_seconds. -- Too large (or zero) = consumes disk space; too small = need to respond quickly to network/machine crash. (Not relevant if log_bin (now ON) = OFF)

( 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.

( Aborted_connects / Connections ) = 11,684 / 16001 = 73.0% -- Perhaps a hacker is trying to break in? (Attempts to connect)

( thread_cache_size / Max_used_connections ) = 1,800 / 6 = 30000.0% -- There is no advantage in having the thread cache bigger than your likely number of connections. Wasting space is the disadvantage.

Abnormally small:

Com_show_tables = 0
Handler_read_next / Handler_read_key = 0.296
Handler_read_rnd_next / Handler_read_rnd = 1.58
Innodb_buffer_pool_reads * innodb_page_size / innodb_buffer_pool_size = 0.50%
binlog_expire_logs_seconds = 86,400
innodb_lru_scan_depth / innodb_io_capacity = 0.0167
optimizer_search_depth = 0
range_optimizer_max_mem_size = 0

Abnormally large:

1.2 * Key_blocks_used * 1024 = 1,102.9MB
Binlog_cache_use = 119 /sec
Binlog_stmt_cache_use = 107 /sec
Com_alter_table + Com_flush = 0.067 /sec
Com_begin = 225 /sec
Com_commit = 225 /sec
Com_commit + Com_rollback = 225 /sec
Com_create_db = 0.13 /HR
Com_create_event = 0.38 /HR
Com_create_function = 0.38 /HR
Com_create_table = 5.1 /sec
Com_dealloc_sql = 4.6 /HR
Com_drop_event = 0.38 /HR
Com_drop_function = 0.38 /HR
Com_drop_index = 1.5 /HR
Com_drop_table = 12 /sec
Com_execute_sql = 4.6 /HR
Com_prepare_sql = 4.6 /HR
Com_rename_table = 0.22 /sec
Com_show_binlogs = 60 /HR
Com_show_slave_status = 0.23 /sec
Com_show_status = 1.1 /sec
Com_slave_start = 0.26 /HR
Com_slave_stop = 0.26 /HR
Com_truncate = 60 /HR
Com_update = 140 /sec
Handler_commit/Questions = 14421.6%
Handler_delete = 1943 /sec
Handler_prepare = 481 /sec
Handler_read_rnd = 7152 /sec
Handler_update = 5494 /sec
Handler_write = 30580 /sec
Innodb_buffer_pool_bytes_data = 4053252 /sec
Innodb_buffer_pool_pages_data = 6.96e+6
Innodb_buffer_pool_pages_dirty = 163,480
Innodb_buffer_pool_pages_free = 6.85e+7
Innodb_buffer_pool_pages_total = 7.55e+7
Innodb_buffer_pool_write_requests = 138416 /sec
Innodb_buffer_pool_write_requests / Innodb_buffer_pool_pages_flushed = 1,001
Innodb_data_pending_fsyncs = 1,828
Innodb_data_writes = 7271 /sec
Innodb_data_writes - Innodb_log_writes - Innodb_dblwr_writes = 151 /sec
Innodb_data_written = 13547354 /sec
Innodb_dblwr_pages_written = 138 /sec
Innodb_log_write_requests = 116170 /sec
Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group = 16,163.9MB
Innodb_pages_created = 231 /sec
Innodb_pages_written = 140 /sec
Innodb_rows_deleted = 1913 /sec
Innodb_rows_deleted + Innodb_rows_inserted = 30712 /sec
Innodb_rows_inserted = 28798 /sec
Innodb_rows_updated = 5267 /sec
Innodb_system_rows_deleted = 11 /sec
Innodb_system_rows_inserted = 11 /sec
Innodb_system_rows_read = 4.83e+6
Innodb_system_rows_updated = 128 /sec
Key_blocks_unused = 5.4e+7
Key_blocks_used = 941,173
Key_write_requests = 1031 /sec
Open_files = 2,392
Slave_open_temp_tables = 57
Ssl_accepts = 930
Ssl_finished_accepts = 930
Ssl_session_cache_overflows = 451
Ssl_used_session_cache_entries = 128
back_log / max_connections = 100.0%
binlog_group_commit_sync_delay = 10,000
eq_range_index_dive_limit = 5,000
host_cache_size = 903
innodb_buffer_pool_chunk_size = 1024MB
innodb_flush_log_at_timeout = 30
innodb_flushing_avg_loops = 60
innodb_io_capacity_max = 40,000
innodb_open_files = 100,000
innodb_read_io_threads = 64
innodb_write_io_threads = 64
max_error_count = 1,024
max_heap_table_size = 512MB
max_length_for_sort_data = 4,096
min(max_heap_table_size, tmp_table_size) = 512MB
net_read_timeout = 3,600
net_write_timeout = 3,600
optimizer_trace_offset = --1
performance_schema_error_size = 4,808
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
schema_definition_cache = 100,000
slave_parallel_workers = 6
slave_pending_jobs_size_max = 4096MB
table_definition_cache = 8,000
tablespace_definition_cache = 15,000

Abnormal strings:

binlog_row_image = MINIMAL
event_scheduler = ON
have_query_cache = NO
init_file = /var/lib/mysql/initMysqlScripts.sql
innodb_fast_shutdown = 1
innodb_log_compressed_pages = OFF
innodb_tmpdir = /localbackup/mysql/tmp/inno/
log_bin_trust_function_creators = ON
optimizer_trace = enabled=off,one_line=off
optimizer_trace_features = greedy_search=on, range_optimizer=on, dynamic_range=on, repeated_subselect=on
slave_compressed_protocol = ON
slave_rows_search_algorithms = INDEX_SCAN,HASH_SCAN
slave_sql_verify_checksum = OFF
transaction_isolation = READ-UNCOMMITTED