Analysis of GLOBAL STATUS and VARIABLES:
- Version: 10.5.8-MariaDB-log
- 32 GB of RAM
- Uptime = 07:15:44; some GLOBAL STATUS values may not be meaningful yet.
- You are not running on Windows.
- Running 64-bit version
- You appear to be running entirely (or mostly) InnoDB.
The More Important Issues:
Since you are not using MyISAM, you could decrease
key_buffer_size from 256M to 20M.
Table_open_cache_hits = 2347 /sec
Table_open_cache_misses = 35 /sec
That's a reasonable ratio. But can you explain why so many tables are being used so fast? Thise are both "high" numbers.
Was there some reason for having this hige a value?
( innodb_lru_scan_depth ) = 1,536 256 might be better.
The buffer_pool_size is 22G, but the status seems to imply that the dataset, or at least the working set, is much lower than that. If this is the case, then the machine size (RAM size) is unnecessarily large. Of course, if you expect the dataset to grow, you have a lot of room for growth.
If your disk is SSD, there are a couple of settings to change; see below.
lock_wait_timeout = 86,400 -- That may hide issues that should be taken care of.
Some clues that there is heavy use of temp tables in SELECTs (automatically generated by the Optimizer). Let's see some of the common queries; maybe they can be improved.
Details and other observations:
( Key_blocks_used * 1024 / key_buffer_size ) = 0 * 1024 / 256M = 0 -- Percent of key_buffer used. High-water-mark.
-- Lower key_buffer_size (now 268435456) to avoid unnecessary memory usage.
( table_open_cache ) = 10,000 -- Number of table descriptors to cache
-- Several hundred is usually good.
( Table_open_cache_misses ) = 911,982 / 26144 = 35 /sec
-- May need to increase table_open_cache (now 10000)
( innodb_buffer_pool_size / innodb_buffer_pool_instances ) = 22528M / 1 = 22528MB -- Size of each buffer_pool instance.
-- An instance should be at least 1GB. In very large RAM, have 16 instances.
( innodb_lru_scan_depth * innodb_page_cleaners ) = 1,536 * 1 = 1,536 -- 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 1). Also check for swapping.
( innodb_lru_scan_depth ) = 1,536
-- "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 ) = 1,191,526 * 16384 / 22528M = 82.6% -- buffer pool free
-- buffer_pool_size is bigger than working set; could decrease it
( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 1,191,526 / 1419440 = 83.9% -- Pct of buffer_pool currently not in use
-- innodb_buffer_pool_size (now 23622320128) 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 ) = 3,734,142,976 / 22528M = 15.8% -- Percent of buffer pool taken up by data
-- A small percent may indicate that the buffer_pool is unnecessarily big.
( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 26,144 / 60 * 512M / 352526848 = 663 -- 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 536870912). (Cannot change in AWS.)
( innodb_flush_method ) = innodb_flush_method = fsync -- 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
( default_tmp_storage_engine ) = default_tmp_storage_engine =
( innodb_flush_neighbors ) = 1 -- A minor optimization when writing blocks to disk.
-- Use 0 for SSD drives; 1 for HDD.
( 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 = OFF -- 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).
( 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.
( character_set_server ) = character_set_server = utf8
-- Charset problems may be helped by setting character_set_server (now utf8) to utf8mb4. That is the future default.
( local_infile ) = local_infile = ON
-- local_infile (now ON) = ON is a potential security issue
( Created_tmp_tables ) = 2,497,985 / 26144 = 96 /sec -- Frequency of creating "temp" tables as part of complex SELECTs.
( 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.
( Connections ) = 3,289,327 / 26144 = 125 /sec -- Connections
-- Increase wait_timeout (now 28800); use pooling?
( thread_cache_size / Max_used_connections ) = 512 / 297 = 172.4%
-- There is no advantage in having the thread cache bigger than your likely number of connections. Wasting space is the disadvantage.
Acl_database_grants = 0
Acl_users = 3
Aria_pagecache_read_requests = 4.3 /HR
Aria_pagecache_write_requests = 0.55 /HR
Com_show_fields = 0
Com_show_tables = 0
Handler_read_first = 2.5 /HR
Handler_tmp_update = 0
Innodb_buffer_pool_pages_flushed / max(Questions, Queries) = 0.00057
Innodb_buffer_pool_pages_misc = 0
Innodb_buffer_pool_pages_misc * 16384 / innodb_buffer_pool_size = 0
Innodb_ibuf_merged_inserts = 0
Innodb_ibuf_merges = 0
Innodb_master_thread_active_loops = 0
Innodb_max_trx_id = 1.72e+6
Innodb_mem_adaptive_hash = 0
Innodb_rows_updated = 1.7 /HR
Memory_used_initial = 6.06e+7
innodb_adaptive_max_sleep_delay = 0
innodb_background_scrub_data_check_interval = 0
innodb_background_scrub_data_interval = 0
innodb_spin_wait_delay = 4
lock_wait_timeout = 86,400
(Com_select + Qcache_hits) / (Com_insert + Com_update + Com_delete + Com_replace) = 872
10 * read_buffer_size = 160MB
Com_select = 2344 /sec
Feature_check_constraint = 0.14 /HR
Feature_locale = 16 /HR
Handler_commit = 2347 /sec
Handler_tmp_write = 15143 /sec
Innodb_buffer_pool_pages_free = 1.19e+6
Open_streams = 4
Select_range = 147 /sec
Sort_priority_queue_sorts = 96 /sec
Sort_scan = 96 /sec
Table_open_cache_hits = 2347 /sec
Threads_cached = 185
innodb_lru_scan_depth / innodb_io_capacity = 7.68
innodb_open_files = 10,000
max_heap_table_size = 512MB
max_relay_log_size = 1024MB
min(max_heap_table_size, tmp_table_size) = 512MB
performance_schema_max_cond_classes = 90
performance_schema_max_statement_classes = 222
tmp_memory_table_size = 512MB
aria_recover_options = BACKUP,QUICK
binlog_row_metadata = NO_LOG
disconnect_on_expired_password = OFF
histogram_type = DOUBLE_PREC_HB
init_connect = SET NAMES utf8 collate utf8_general_ci
innodb_fast_shutdown = 1
innodb_log_optimize_ddl = OFF
log_slow_admin_statements = ON
myisam_stats_method = NULLS_UNEQUAL
old_alter_table = DEFAULT
optimizer_trace = enabled=off
slave_parallel_mode = optimistic
use_stat_tables = PREFERABLY_FOR_QUERIES
wsrep_debug = NONE
wsrep_load_data_splitting = OFF