Well, I did not find much of significance in these values. The slowlog may provide more info.
Hmmm... 12 app servers, each connecting up to 14 times. Max_used_connections = 164. That sounds like you came close to hitting 14 on each server at least once in the last 4 months. Alas, that does not give any useful clues in what to do next in that area.
And 916 QPS is rather high.
While you could increase max_connections and the 14 per app server, I worry that the server will bog down.
If you expect to double the traffic in the near future, let's talk about having multiple MariaDB servers -- either a Primary plus at least two Replicas, or a Galera cluster.
If the slowlog finds something to speed up, that will cut down on the 'need' to increase max_connections. If long_query_time=2 does not give much info, it can be lowered. (The number is a float.)
But, I find that even with long_query_time set "too high", it is quite useful at providing useful info on a severe spike. The thundering herd leads to all queries taking much longer than they should.
On the other hand If you "never" have high CPU, then it should be safe to increase the 14.
Analysis of GLOBAL STATUS and VARIABLES:
Observations:
- Version: 10.3
- 64 GB of RAM
- Uptime = 116d 05:46:57
- 916 QPS
The More Important Issues:
Lower these to under 1% of RAM: tmp_table_size, max_heap_table_size.
Check to see if the code is issuing COMMIT sometimes when it is not necessary.
Details and other observations:
( table_open_cache ) = 8,192 -- Number of table descriptors to cache
-- Several hundred is usually good.( innodb_buffer_pool_size ) = 30,720 / 65536M = 46.9% -- % of RAM used for InnoDB buffer_pool
-- Set to about 70% of available RAM. (To low is less efficient; too high risks swapping.)( innodb_buffer_pool_instances ) = 30 -- For large RAM, consider using 1-16 buffer pool instances, not allowing less than 1GB each. Also, not more than, say, twice the number of CPU cores.
-- Recommend no more than 16. (Beginning to go away in 10.5)( innodb_lru_scan_depth * innodb_buffer_pool_instances ) = 1,024 * 30 = 30,720 -- A metric of CPU usage.
-- Lower either number.( 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_lru_scan_depth is a very poorly named variable. A better name would be innodb_free_page_target_per_buffer_pool. It is a number of pages InnoDB tries to keep free in each buffer pool instance to speed up read and page creation operations.
-- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixed by lowering lru_scan_depth( innodb_io_capacity ) = 200 -- When flushing, use this many IOPs.
-- Reads could be slugghish or spiky. Use 2000 if using SSD drive.( innodb_doublewrite ) = innodb_doublewrite = OFF -- ON leads to extra I/O, but extra safety in crash.
-- OFF is OK for FusionIO, Galera, Replicas, ZFS.( innodb_log_buffer_size ) = 128M -- Suggest 2MB-64MB, and at least as big as biggest blob set in transactions.
-- Adjust innodb_log_buffer_size (now 134217728).( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 10,043,217 / 60 * 6144M / 1591819351040 = 677 -- Minutes between InnoDB log rotations Beginning with 5.6.8, innodb_log_file_size can be changed dynamically; I don't know about MariaDB. Be sure to also change my.cnf
-- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size (now 6442450944). (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( Handler_rollback ) = 189,403,449 / 10043217 = 19 /sec
-- Why so many rollbacks?( Innodb_row_lock_time_avg ) = 8,946 -- Avg time to lock a row (millisec)
-- Possibly conflicting queries; possibly table scans.( Innodb_row_lock_time_max ) = 472,943 -- Max time to lock a row (millisec)
-- Possibly conflicting queries; possibly table scans.( innodb_lock_wait_timeout ) = 1,200 -- Two battling InnoDB transactions, but not a deadlock -- one will wait this long (seconds) in hopes of getting the desired locks.
-- Fix the cause of timeouts rather than increasing this value.( 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. Limits write IO requests per second (IOPS).
-- For starters: HDD: 200; SSD: 2000.( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = ON -- Whether to use the adapative hash (AHI).
-- ON for mostly readonly; OFF for DDL-heavy( innodb_flush_log_at_trx_commit ) = 1 -- 1 = secure; 2 = faster
-- (You decide) Use 1, along with sync_binlog (now 5)=1 for the greatest level of fault tolerance. 0 is best for speed. 2 is a compromise between 0 and 1.( sync_binlog ) = 5 -- 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. 0 is OK for Galera.( 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.( min( tmp_table_size, max_heap_table_size ) ) = (min( 1024M, 1024M )) / 65536M = 1.6% -- Percent of RAM to allocate when needing MEMORY table (per table), or temp table inside a SELECT (per temp table per some SELECTs). Too high may lead to swapping.
-- Decrease tmp_table_size (now 1073741824) and max_heap_table_size (now 1073741824) to, say, 1% of ram.( local_infile ) = local_infile = ON
-- local_infile (now ON) = ON is a potential security issue( bulk_insert_buffer_size ) = 8M / 65536M = 0.01% -- Buffer for multi-row INSERTs and LOAD DATA
-- Too small could hinder such operations.( Qcache_not_cached ) = 2,027,192,644 / 10043217 = 201 /sec -- SQL_CACHE attempted, but ignored
-- Rethink caching; tune qcache( Qcache_free_blocks / Qcache_total_blocks ) = 1,997 / 6500 = 30.7% -- Fragmentation in Query Cache.
-- Various things.( Qcache_inserts - Qcache_queries_in_cache ) = (1063262492 - 2234) / 10043217 = 105 /sec -- Invalidations/sec.( (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache / query_alloc_block_size ) = (16M - 13111704) / 2234 / 16384 = 0.1 -- query_alloc_block_size vs formula
-- Adjust query_alloc_block_size (now 16384)( tmp_table_size ) = 1024M -- Limit on size of MEMORY temp tables used to support a SELECT
-- Decrease tmp_table_size (now 1073741824) to avoid running out of RAM. Perhaps no more than 64M.( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (201391833 + 120793648 + 101525982 + 0) / 833693705 = 0.508 -- Statements per Commit (assuming all InnoDB)
-- Low: Might help to group queries together in transactions; High: long transactions strain various things.( binlog_format ) = binlog_format = MIXED -- STATEMENT/ROW/MIXED.
-- ROW is preferred by 5.7 (10.3)( expire_logs_days ) = 90 -- 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)( Syncs ) = 13,032,779 / 10043217 = 1.3 /sec -- Sync to disk for binlog and perhaps .frm, not InnoDB.( thread_pool_max_threads ) = 65,536 -- One of many settings for MariaDB's thread pooling
-- Lower the value.
Abnormally small:
Innodb_dblwr_pages_written = 0
Sort_priority_queue_sorts = 0.02 /HR
Table_locks_immediate = 0.77 /HR
eq_range_index_dive_limit = 0
innodb_page_cleaners / innodb_buffer_pool_instances = 0.133
interactive_timeout = 600
Abnormally large:
Binlog_bytes_written = 167602 /sec
Binlog_snapshot_position = 3.25e+8
Busy_time = 2.76e+7
Com_commit = 83 /sec
Com_drop_index = 0.00036 /HR
Com_purge_before_date = 0.0025 /HR
Com_show_generic = 0.0014 /HR
Com_show_status = 0.13 /sec
Cpu_time = 2.94e+6
Handler_discover = 0.075 /HR
Handler_read_rnd_next / Handler_read_rnd = 13,901
Innodb_buffer_pool_pages_dirty = 136,388
Innodb_buffer_pool_pages_misc = 1.84e+19
Performance_schema_file_instances_lost = 47,805
Qcache_hits = 338 /sec
Qcache_inserts = 105 /sec
Slave_connections = 34
gtid_domain_id = 4.0e+6
innodb_open_files = 16,384
max_heap_table_size = 1024MB
max_user_connections = 260
min(max_heap_table_size, tmp_table_size) = 1024MB
performance_schema_events_stages_history_size = 20
performance_schema_events_statements_history_size = 20
performance_schema_events_waits_history_size = 20
query_cache_limit = 1.68e+7
table_open_cache / max_connections = 31.3
tmp_memory_table_size = 1024MB
Abnormal strings:
Innodb_have_snappy = ON
Slave_heartbeat_period = 0
Slave_received_heartbeats = 0
aria_recover_options = BACKUP,QUICK
init_connect = SET NAMES utf8mb4
innodb_fast_shutdown = 1
innodb_use_native_aio = OFF
myisam_stats_method = NULLS_UNEQUAL
old_alter_table = DEFAULT
sql_slave_skip_counter = 0
userstat = ON
wsrep_data_home_dir = /mysql/data/
Query Cache
Oh, I promised to make a recommendation about the Query cache. The above list is the important highlights; in looking at the details, I get inconclusive results. Some details:
- query_cache_type = ON -- you could switch to DEMAND and change all SELECTs to include either SQL_CACHE or SQL_NO CACHE as appropriate. This would make the cache more efficient.
- Since you have lots of RAM, increasing 16MB some (say 32MB) might help. (There is really no information to conclusively say whether this or other changes will help or hurt.)
- Qcache_lowmem_prunes = 9.4/second -- This is 'moderate', but is supports my previous note.
- Qcache_free_memory / query_cache_size = 78% -- This is puzzling. Perhaps the cache had been freshly purged.
- Qcache_queries_in_cache = 2234
- Qcache_inserts = 105/sec (contrast: 660 SELECTs/sec)
SHOW GLOBAL STATUS LIKE 'Max_used_connections';-- to see if it is actually hitting 175.