Analysis of GLOBAL STATUS and VARIABLES:
Observations:
- Version: 5.7.40-log
- 32 GB of RAM
- Uptime = 1d 03:53:33
- 43.6 QPS
The More Important Issues:
Well, nothing urgent. Here are the somewhat important ones:
innodb_lru_scan_depth = 256
If using SSD, not HDD:
innodb_io_capacity = 500
innodb_io_capacity_max = 4000
max_allowed_packet = 50M
max_connections = 50
Details and other observations:
( table_open_cache ) = 10,000 -- Number of table descriptors to cache
-- Several hundred is usually good.
( binlog_cache_size * max_connections ) = (8M * 300) / 32768M = 7.3% -- RAM used for caching transactions on the way to the binlog.
-- Decrease binlog_cache_size (now 8388608) and/or max_connections (now 300)
( innodb_buffer_pool_size ) = 6,144M / 32768M = 18.8% -- % of RAM used for InnoDB buffer_pool
-- Set to about 70% of available RAM. (Too low is less efficient; too high risks swapping.)
( 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 the 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_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_read_ahead_evicted / Innodb_buffer_pool_read_ahead ) = 34,621 / 50483 = 68.6% -- Utility of read_ahead.
-- Turn off innodb_random_read_ahead (now OFF).
( innodb_doublewrite ) = innodb_doublewrite = OFF -- ON leads to extra I/O, but extra safety in crash.
-- OFF is OK for FusionIO, Galera, Replicas, ZFS, EXT4.
( 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_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_flush_log_at_trx_commit ) = 1 -- 1 = secure; 2 = faster
-- (You decide) Use 1, along with sync_binlog (now 1)=1 for the greatest level of fault tolerance. 0 is best for speed. 2 is a compromise between 0 and 1.
( 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.
( innodb_purge_threads ) = 1 -- Number of threads to clean up history list.
-- If you have a lot of writes, recommend 4 in versions 5.6 and 10.0 or later.
( max_allowed_packet ) = 1G / 32G = 3.1%
-- If you do not have large blobs (etc) to load, then decrease the value. Else decrease innodb_buffer_pool_size (now 6442450944) to make room. Swapping is terrible for performance.
( character_set_client ) = character_set_client = latin1 --
-- If you will be using text from places other than Western Europe, consider switching to utf8mb4. (Beyond the scope of this discussion.)
( character_set_connection ) = character_set_connection = latin1 --
( character_set_results ) = character_set_results = latin1 --
( local_infile ) = local_infile = ON
-- local_infile (now ON) = ON is a potential security issue
( Com_delete / Com_insert ) = 275,515 / 10556 = 2610.0% -- Deletes / Inserts (as a pct). (Ignores LOAD, REPLACE, etc.)
( Com__biggest ) = Com__biggest = Com_update -- Which of the "Com_" metrics is biggest.
-- Normally it is Com_select (now 440739). If something else, then it may be a sloppy platform, or may be something else.
( relay_log_space_limit ) = 1024M -- The max total size for relay logs on a Replica. (0=unlimited)
-- Let's discuss the rationale for having a limit.
( 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
( 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.
( Max_used_connections / max_connections ) = 7 / 300 = 2.3% -- Peak % of connections
-- Since several memory factors can expand based on max_connections (now 300), it is good not to have that setting too high.
( thread_cache_size / Max_used_connections ) = 11 / 7 = 157.1%
-- There is no advantage in having the thread cache bigger than your likely number of connections. Wasting space is the disadvantage.
Abnormally small:
(Com_select + Qcache_hits) / (Com_insert + Com_update + Com_delete + Com_replace) = 0.115
Com_show_fields = 0
Innodb_buffer_pool_pages_misc = 0
Innodb_buffer_pool_pages_misc * 16384 / innodb_buffer_pool_size = 0
Innodb_dblwr_pages_written = 0
net_buffer_length / max_allowed_packet = 0.00%
Abnormally large:
Com_change_repl_filter = 0.036 /HR
Com_lock_tables_for_backup = 0.072 /HR
Com_purge_before_date = 12 /HR
Com_show_master_status = 0.033 /sec
Com_show_open_tables = 0.036 /HR
Com_show_plugins = 18 /HR
Com_show_slave_status = 0.033 /sec
Com_show_status = 0.24 /sec
Com_show_warnings = 18 /HR
Com_slave_stop = 0.036 /HR
Handler_commit/Questions = 118.6%
Performance_schema_file_handles_lost = 248
Ssl_accepts = 14,343
Ssl_default_timeout = 7,200
Ssl_finished_accepts = 14,343
Ssl_session_cache_misses = 13,168
Ssl_session_cache_overflows = 211
Ssl_used_session_cache_entries = 49
Ssl_verify_depth = 1.84e+19
Ssl_verify_mode = 5
back_log / max_connections = 943.7%
binlog_cache_size = 8.39e+6
binlog_transaction_dependency_history_size = 32,000
net_read_timeout = 120
net_write_timeout = 240
slave_parallel_workers = 32
table_definition_cache = 10,000
table_open_cache / max_connections = 33.3
Abnormal strings:
auto_generate_certs = OFF
binlog_row_image = MINIMAL
binlog_transaction_dependency_tracking = WRITESET
core_file = ON
delay_key_write = OFF
disabled_storage_engines = MyISAM,MRG_MyISAM,BLACKHOLE,FEDEATED,ARCHIVE
innodb_fast_shutdown = 1
innodb_tmpdir = /mnt/temp
log_bin_trust_function_creators = ON
log_output = NONE
log_syslog = ON
lower_case_file_system = ON
lower_case_table_names = 1
opt_tf__dynamic_range =
optimizer_trace = enabled=off,one_line=off
optimizer_trace_features = greedy_search=on,range_optimizer=on,dynamic_range=
relay_log_recovery = ON
require_secure_transport = ON
sha256_password_auto_generate_rsa_keys = OFF
slave_parallel_type = LOGICAL_CLOCK
slave_preserve_commit_order = ON
slave_rows_search_algorithms = TABLE_SCAN,INDEX_SCAN
sql_slave_skip_counter = 0
time_zone = +00:00
COMMITthem? Let's see some of the queries.