A review of the VARIABLES and GLOBAL STATUS:
Observations:
* Version: 10.3.15-MariaDB
* 16 GB of RAM
* Uptime = 64d 10:48:05
* You are not running on Windows.
* Running 64-bit version
* You appear to be running entirely (or mostly) InnoDB.
The More Important Issues:
Is this a standalone InnoDB database, not clustered, not a Slave, etc?
Find out whether you have HDD or SSD; then see a few items in the Details, below.
You are using the "Query cache", but it is not being very efficient, possibly slowing down the system overall. Recommend either turning it off or using DEMAND
together with carefully picking which SELECTs
to have SQL_CACHE
in.
No COMMITs? Are you using autocommit=ON and ever using BEGIN? Please describe the typical DML queries; we may have suggestions on using transactions differently to decrease I/O.
Consider changing from REPLACE
to INSERT ... ON DUPLICATE KEY UPDATE
.
Your question was about what system to use. Please note that network bandwidth may be a big issue for you (Bytes_sent = 7666357 /sec
); hence addressing number and verbosity of queries may be useful (and system-agnostic).
Why so many SHOW STATUS
calls?
Lots of full-table scans for DELETE. Let's discuss them and possible ways to improve them, especially if they are on big tables. ( http://mysql.rjweb.org/doc.php/deletebig )
Details and other observations:
( Table_open_cache_misses ) = 14,420,381 / 5568485 = 2.6 /sec
-- May need to increase table_open_cache (now 2048)
( 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_page_cleaners / innodb_buffer_pool_instances ) = 4 / 6 = 0.667
-- innodb_page_cleaners
-- Recommend setting innodb_page_cleaners (now 4) to innodb_buffer_pool_instances (now 6)
( innodb_lru_scan_depth ) = 1,024
-- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixed by lowering lru_scan_depth
( innodb_doublewrite ) = innodb_doublewrite = OFF
-- Extra I/O, but extra safety in crash.
-- OFF is OK for FusionIO, Galera, Slaves, ZFS.
( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 182,569,362,432 / (5568485 / 3600) / 2 / 2048M = 0.0275
-- Ratio
-- (see minutes)
( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 5,568,485 / 60 * 2048M / 182569362432 = 1,091
-- 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 2147483648). (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
( Innodb_row_lock_waits ) = 917,931 / 5568485 = 0.16 /sec
-- How often there is a delay in getting a row lock.
-- May be caused by complex queries that could be optimized.
( 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.
( sync_binlog ) = 0
-- 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.
( 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 = latin1
-- Charset problems may be helped by setting character_set_server (now latin1) to utf8mb4. That is the future default.
( local_infile ) = local_infile = ON
-- local_infile (now ON) = ON is a potential security issue
( query_cache_size ) = 128M
-- Size of QC
-- Too small = not of much use. Too large = too much overhead. Recommend either 0 or no more than 50M.
( Qcache_hits / Qcache_inserts ) = 1,259,699,944 / 2684144053 = 0.469
-- Hit to insert ratio -- high is good
-- Consider turning off the query cache.
( Qcache_hits / (Qcache_hits + Com_select) ) = 1,259,699,944 / (1259699944 + 3986160638) = 24.0%
-- Hit ratio -- SELECTs that used QC
-- Consider turning off the query cache.
( Qcache_inserts - Qcache_queries_in_cache ) = (2684144053 - 46843) / 5568485 = 482 /sec
-- Invalidations/sec.
( (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache / query_alloc_block_size ) = (128M - 59914960) / 46843 / 16384 = 0.0968
-- query_alloc_block_size vs formula
-- Adjust query_alloc_block_size (now 16384)
( Select_scan ) = 6,048,081 / 5568485 = 1.1 /sec
-- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)
( Com_stmt_prepare - Com_stmt_close ) = 4,138,804,898 - 4129522738 = 9.28e+6
-- How many prepared statements have not been closed.
-- CLOSE prepared statements
( Com_replace ) = 28,182,079 / 5568485 = 5.1 /sec
-- Consider changing to INSERT ... ON DUPLICATE KEY UPDATE.
( binlog_format ) = binlog_format = MIXED
-- STATEMENT/ROW/MIXED.
-- ROW is preferred by 5.7 (10.3)
( slow_query_log ) = slow_query_log = OFF
-- Whether to log slow queries. (5.1.12)
( long_query_time ) = 10
-- Cutoff (Seconds) for defining a "slow" query.
-- Suggest 2
( max_connect_errors ) = 999,999,999 = 1.0e+9
-- A small protection against hackers.
-- Perhaps no more than 200.
( Connections ) = 206,910,348 / 5568485 = 37 /sec
-- Connections
-- Use pooling?
Abnormally small:
Com_show_tables = 0
Created_tmp_files = 0.12 /HR
Innodb_dblwr_pages_written = 0
Qcache_total_blocks * query_cache_min_res_unit / Qcache_queries_in_cache = 5,166
eq_range_index_dive_limit = 0
innodb_ft_min_token_size = 2
innodb_spin_wait_delay = 4
lock_wait_timeout = 86,400
query_cache_min_res_unit = 2,048
Abnormally large:
Access_denied_errors = 93,135
Acl_table_grants = 10
Bytes_sent = 7666357 /sec
Com_create_trigger = 0.0026 /HR
Com_create_user = 0.0013 /HR
Com_replace_select = 0.086 /HR
Com_reset = 1 /HR
Com_show_open_tables = 0.02 /HR
Com_show_status = 0.18 /sec
Com_stmt_close = 741 /sec
Com_stmt_execute = 743 /sec
Com_stmt_prepare = 743 /sec
Delete_scan = 43 /HR
Executed_triggers = 1.5 /sec
Feature_fulltext = 0.62 /sec
Handler_read_last = 0.83 /sec
Handler_read_next = 357845 /sec
Handler_read_prev = 27369 /sec
Innodb_buffer_pool_pages_misc * 16384 / innodb_buffer_pool_size = 16.2%
Innodb_row_lock_time_max = 61,943
Prepared_stmt_count = 3
Qcache_free_blocks = 24,238
Qcache_hits = 226 /sec
Qcache_inserts = 482 /sec
Qcache_total_blocks = 118,160
Select_range = 53 /sec
Sort_range = 47 /sec
Tc_log_page_size = 4,096
innodb_open_files = 10,000
max_relay_log_size = 1024MB
performance_schema_max_stage_classes = 160
Abnormal strings:
aria_recover_options = BACKUP,QUICK
ft_min_word_len = 2
innodb_fast_shutdown = 1
innodb_use_atomic_writes = ON
log_slow_admin_statements = ON
myisam_stats_method = NULLS_UNEQUAL
old_alter_table = DEFAULT
plugin_maturity = gamma