mysql - Mariadb有时在Filesort上崩溃

Question

拥有已运行两年且没有问题的应用程序。突然，我们开始看到Mariadb崩溃并在各种查询中恢复，这些查询似乎通常在其上运行filesort函数。我们执行许多复杂的查询，有时会被迫对计算字段进行排序。这些以前没有问题，所以我不认为查询本身有问题。

我不确定这是配置问题还是错误或硬件问题。非常感谢您的协助。

服务器有16GB的ram和6个CPU。

日志文件如下:

Version: '10.2.24-MariaDB-log'  socket: '/mnt/volume-01-part1/mysql/mysql.sock'  port: 3306  MariaDB Server
2019-06-27 12:52:23 139999433680640 [ERROR] InnoDB: Table `mysql`.`innodb_table_stats` not found.
190627 12:53:18 [ERROR] mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.

To report this bug, see https://mariadb.com/kb/en/reporting-bugs

We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed, 
something is definitely wrong and this may fail.

Server version: 10.2.24-MariaDB-log
key_buffer_size=10485760
read_buffer_size=2097152
max_used_connections=9
max_threads=102
thread_count=16
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 430185 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7f53b80009a8
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x7f54287d1d30 thread_stack 0x30000
/usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x56288037858e]
/usr/sbin/mysqld(handle_fatal_signal+0x30d)[0x56287fe0421d]
sigaction.c:0(__restore_rt)[0x7f576a7ca5d0]
/usr/sbin/mysqld(_Z8filesortP3THDP5TABLEP8FilesortP16Filesort_trackerP4JOINy+0x2b4)[0x56287fe02684]
/usr/sbin/mysqld(_Z17create_sort_indexP3THDP4JOINP13st_join_tableP8Filesort+0xd7)[0x56287fcb1b57]
/usr/sbin/mysqld(_ZN17Window_funcs_sort4execEP4JOINb+0x4a)[0x56287fd8ee2a]
/usr/sbin/mysqld(_ZN24Window_funcs_computation4execEP4JOINb+0x2a)[0x56287fd8f53a]
/usr/sbin/mysqld(_ZN7AGGR_OP8end_sendEv+0xe7)[0x56287fcc20d7]
/usr/sbin/mysqld(_Z24sub_select_postjoin_aggrP4JOINP13st_join_tableb+0x31)[0x56287fcc22a1]
/usr/sbin/mysqld(_ZN4JOIN10exec_innerEv+0x903)[0x56287fcc2bf3]
/usr/sbin/mysqld(_ZN4JOIN4execEv+0x33)[0x56287fcc2f43]
/usr/sbin/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x11a)[0x56287fcc309a]
/usr/sbin/mysqld(_Z18mysql_derived_fillP3THDP3LEXP10TABLE_LIST+0x152)[0x56287fc4ae62]
/usr/sbin/mysqld(_Z27mysql_handle_single_derivedP3LEXP10TABLE_LISTj+0xe4)[0x56287fc4abd4]
/usr/sbin/mysqld(_ZN13st_join_table12preread_initEv+0xdf)[0x56287fca2fbf]
/usr/sbin/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x2f8)[0x56287fca32c8]
/usr/sbin/mysqld(_ZN4JOIN10exec_innerEv+0xa3f)[0x56287fcc2d2f]
/usr/sbin/mysqld(_ZN4JOIN4execEv+0x33)[0x56287fcc2f43]
/usr/sbin/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x11a)[0x56287fcc309a]
/usr/sbin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x254)[0x56287fcc3bf4]
/usr/sbin/mysqld(+0x41c4ef)[0x56287fba74ef]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x334d)[0x56287fc6e99d]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_statebb+0x2de)[0x56287fc748ee]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcjbb+0x21f5)[0x56287fc77ac5]
/usr/sbin/mysqld(_Z10do_commandP3THD+0x165)[0x56287fc786e5]
/usr/sbin/mysqld(_Z24do_handle_one_connectionP7CONNECT+0x1aa)[0x56287fd41e3a]
/usr/sbin/mysqld(handle_one_connection+0x3d)[0x56287fd41f5d]
pthread_create.c:0(start_thread)[0x7f576a7c2dd5]
/lib64/libc.so.6(clone+0x6d)[0x7f5768b64ead]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0x7f53b800f130): [REMOVED]
Connection ID (thread ID): 31
Status: NOT_KILLED

Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
Writing a core file...
Working directory at /mnt/volume-01-part1/mysql
Resource Limits:
Limit                     Soft Limit           Hard Limit           Units     
Max cpu time              unlimited            unlimited            seconds   
Max file size             unlimited            unlimited            bytes     
Max data size             unlimited            unlimited            bytes     
Max stack size            8388608              unlimited            bytes     
Max core file size        0                    unlimited            bytes     
Max resident set          unlimited            unlimited            bytes     
Max processes             63458                63458                processes 
Max open files            16364                16364                files     
Max locked memory         65536                65536                bytes     
Max address space         unlimited            unlimited            bytes     
Max file locks            unlimited            unlimited            locks     
Max pending signals       63458                63458                signals   
Max msgqueue size         819200               819200               bytes     
Max nice priority         0                    0                    
Max realtime priority     0                    0                    
Max realtime timeout      unlimited            unlimited            us        
Core pattern: core

服务器变量是:

'aria_block_size', '8192'
'aria_checkpoint_interval', '30'
'aria_checkpoint_log_activity', '1048576'
'aria_encrypt_tables', 'OFF'
'aria_force_start_after_recovery_failures', '0'
'aria_group_commit', 'none'
'aria_group_commit_interval', '0'
'aria_log_file_size', '1073741824'
'aria_log_purge_type', 'immediate'
'aria_max_sort_file_size', '9223372036853727232'
'aria_page_checksum', 'ON'
'aria_pagecache_age_threshold', '300'
'aria_pagecache_buffer_size', '134217728'
'aria_pagecache_division_limit', '100'
'aria_pagecache_file_hash_size', '512'
'aria_recover_options', 'BACKUP,QUICK'
'aria_repair_threads', '1'
'aria_sort_buffer_size', '268434432'
'aria_stats_method', 'nulls_unequal'
'aria_sync_log_dir', 'NEWFILE'
'aria_used_for_temp_tables', 'ON'
'auto_increment_increment', '1'
'auto_increment_offset', '1'
'autocommit', 'ON'
'automatic_sp_privileges', 'ON'
'back_log', '50'
'basedir', '/usr/'
'big_tables', 'OFF'
'binlog_annotate_row_events', 'ON'
'binlog_cache_size', '31457280'
'binlog_checksum', 'CRC32'
'binlog_commit_wait_count', '0'
'binlog_commit_wait_usec', '100000'
'binlog_direct_non_transactional_updates', 'OFF'
'binlog_format', 'MIXED'
'binlog_optimize_thread_scheduling', 'ON'
'binlog_row_image', 'FULL'
'binlog_stmt_cache_size', '32768'
'bulk_insert_buffer_size', '8388608'
'character_set_client', 'utf8'
'character_set_connection', 'utf8'
'character_set_database', 'utf8'
'character_set_filesystem', 'binary'
'character_set_results', 'utf8'
'character_set_server', 'latin1'
'character_set_system', 'utf8'
'character_sets_dir', '/usr/share/mysql/charsets/'
'check_constraint_checks', 'ON'
'collation_connection', 'utf8_general_ci'
'collation_database', 'utf8_general_ci'
'collation_server', 'latin1_swedish_ci'
'completion_type', 'NO_CHAIN'
'concurrent_insert', 'AUTO'
'connect_timeout', '10'
'core_file', 'OFF'
'datadir', '/mnt/volume-01-part1/mysql/'
'date_format', '%Y-%m-%d'
'datetime_format', '%Y-%m-%d %H:%i:%s'
'deadlock_search_depth_long', '15'
'deadlock_search_depth_short', '4'
'deadlock_timeout_long', '50000000'
'deadlock_timeout_short', '10000'
'debug_no_thread_alarm', 'OFF'
'default_master_connection', ''
'default_regex_flags', ''
'default_storage_engine', 'MyISAM'
'default_tmp_storage_engine', ''
'default_week_format', '0'
'delay_key_write', 'ON'
'delayed_insert_limit', '100'
'delayed_insert_timeout', '300'
'delayed_queue_size', '1000'
'div_precision_increment', '4'
'encrypt_binlog', 'OFF'
'encrypt_tmp_disk_tables', 'OFF'
'encrypt_tmp_files', 'OFF'
'enforce_storage_engine', ''
'eq_range_index_dive_limit', '0'
'error_count', '0'
'event_scheduler', 'OFF'
'expensive_subquery_limit', '100'
'expire_logs_days', '3'
'explicit_defaults_for_timestamp', 'OFF'
'external_user', ''
'extra_max_connections', '1'
'extra_port', '0'
'flush', 'OFF'
'flush_time', '0'
'foreign_key_checks', 'ON'
'ft_boolean_syntax', '+ -><()~*:\"\"&|'
'ft_max_word_len', '84'
'ft_min_word_len', '4'
'ft_query_expansion_limit', '20'
'ft_stopword_file', '(built-in)'
'general_log', 'OFF'
'general_log_file', 'dtd-db-01.log'
'group_concat_max_len', '1048576'
'gtid_binlog_pos', '0-1-11007595'
'gtid_binlog_state', '0-1-11007595'
'gtid_current_pos', '0-1-11007595'
'gtid_domain_id', '0'
'gtid_ignore_duplicates', 'OFF'
'gtid_seq_no', '0'
'gtid_slave_pos', ''
'gtid_strict_mode', 'OFF'
'have_compress', 'YES'
'have_crypt', 'YES'
'have_dynamic_loading', 'YES'
'have_geometry', 'YES'
'have_openssl', 'YES'
'have_profiling', 'YES'
'have_query_cache', 'YES'
'have_rtree_keys', 'YES'
'have_ssl', 'DISABLED'
'have_symlink', 'DISABLED'
'histogram_size', '0'
'histogram_type', 'SINGLE_PREC_HB'
'host_cache_size', '228'
'hostname', 'dtd-db-01'
'identity', '0'
'ignore_builtin_innodb', 'OFF'
'ignore_db_dirs', ''
'in_transaction', '0'
'init_connect', ''
'init_file', ''
'init_slave', ''
'innodb_adaptive_flushing', 'ON'
'innodb_adaptive_flushing_lwm', '10.000000'
'innodb_adaptive_hash_index', 'ON'
'innodb_adaptive_hash_index_partitions', '8'
'innodb_adaptive_hash_index_parts', '8'
'innodb_adaptive_max_sleep_delay', '150000'
'innodb_autoextend_increment', '64'
'innodb_autoinc_lock_mode', '1'
'innodb_background_scrub_data_check_interval', '3600'
'innodb_background_scrub_data_compressed', 'OFF'
'innodb_background_scrub_data_interval', '604800'
'innodb_background_scrub_data_uncompressed', 'OFF'
'innodb_buf_dump_status_frequency', '0'
'innodb_buffer_pool_chunk_size', '134217728'
'innodb_buffer_pool_dump_at_shutdown', 'ON'
'innodb_buffer_pool_dump_now', 'OFF'
'innodb_buffer_pool_dump_pct', '25'
'innodb_buffer_pool_filename', 'ib_buffer_pool'
'innodb_buffer_pool_instances', '6'
'innodb_buffer_pool_load_abort', 'OFF'
'innodb_buffer_pool_load_at_startup', 'ON'
'innodb_buffer_pool_load_now', 'OFF'
'innodb_buffer_pool_populate', 'OFF'
'innodb_buffer_pool_size', '12079595520'
'innodb_change_buffer_max_size', '25'
'innodb_change_buffering', 'all'
'innodb_checksum_algorithm', 'crc32'
'innodb_checksums', 'ON'
'innodb_cleaner_lsn_age_factor', 'DEPRECATED'
'innodb_cmp_per_index_enabled', 'OFF'
'innodb_commit_concurrency', '0'
'innodb_compression_algorithm', 'zlib'
'innodb_compression_default', 'OFF'
'innodb_compression_failure_threshold_pct', '5'
'innodb_compression_level', '6'
'innodb_compression_pad_pct_max', '50'
'innodb_concurrency_tickets', '5000'
'innodb_corrupt_table_action', 'deprecated'
'innodb_data_file_path', 'ibdata1:10M:autoextend'
'innodb_data_home_dir', ''
'innodb_deadlock_detect', 'ON'
'innodb_default_encryption_key_id', '1'
'innodb_default_row_format', 'dynamic'
'innodb_defragment', 'ON'
'innodb_defragment_fill_factor', '0.900000'
'innodb_defragment_fill_factor_n_recs', '20'
'innodb_defragment_frequency', '40'
'innodb_defragment_n_pages', '7'
'innodb_defragment_stats_accuracy', '0'
'innodb_disable_sort_file_cache', 'OFF'
'innodb_disallow_writes', 'OFF'
'innodb_doublewrite', 'ON'
'innodb_empty_free_list_algorithm', 'DEPRECATED'
'innodb_encrypt_log', 'OFF'
'innodb_encrypt_tables', 'OFF'
'innodb_encryption_rotate_key_age', '1'
'innodb_encryption_rotation_iops', '100'
'innodb_encryption_threads', '0'
'innodb_fake_changes', 'OFF'
'innodb_fast_shutdown', '1'
'innodb_fatal_semaphore_wait_threshold', '600'
'innodb_file_format', 'Barracuda'
'innodb_file_format_check', 'ON'
'innodb_file_format_max', 'Barracuda'
'innodb_file_per_table', 'ON'
'innodb_fill_factor', '100'
'innodb_flush_log_at_timeout', '1'
'innodb_flush_log_at_trx_commit', '2'
'innodb_flush_method', 'O_DIRECT'
'innodb_flush_neighbors', '1'
'innodb_flush_sync', 'ON'
'innodb_flushing_avg_loops', '30'
'innodb_force_load_corrupted', 'OFF'
'innodb_force_primary_key', 'OFF'
'innodb_force_recovery', '0'
'innodb_foreground_preflush', 'DEPRECATED'
'innodb_ft_aux_table', ''
'innodb_ft_cache_size', '8000000'
'innodb_ft_enable_diag_print', 'OFF'
'innodb_ft_enable_stopword', 'ON'
'innodb_ft_max_token_size', '84'
'innodb_ft_min_token_size', '3'
'innodb_ft_num_word_optimize', '2000'
'innodb_ft_result_cache_limit', '2000000000'
'innodb_ft_server_stopword_table', ''
'innodb_ft_sort_pll_degree', '2'
'innodb_ft_total_cache_size', '640000000'
'innodb_ft_user_stopword_table', ''
'innodb_idle_flush_pct', '100'
'innodb_immediate_scrub_data_uncompressed', 'OFF'
'innodb_instrument_semaphores', 'OFF'
'innodb_io_capacity', '200'
'innodb_io_capacity_max', '2000'
'innodb_kill_idle_transaction', '0'
'innodb_large_prefix', 'ON'
'innodb_lock_schedule_algorithm', 'fcfs'
'innodb_lock_wait_timeout', '120'
'innodb_locking_fake_changes', 'OFF'
'innodb_locks_unsafe_for_binlog', 'OFF'
'innodb_log_arch_dir', ''
'innodb_log_arch_expire_sec', '0'
'innodb_log_archive', 'OFF'
'innodb_log_block_size', '0'
'innodb_log_buffer_size', '33554432'
'innodb_log_checksum_algorithm', 'DEPRECATED'
'innodb_log_checksums', 'ON'
'innodb_log_compressed_pages', 'ON'
'innodb_log_file_size', '3221225472'
'innodb_log_files_in_group', '3'
'innodb_log_group_home_dir', './'
'innodb_log_optimize_ddl', 'ON'
'innodb_log_write_ahead_size', '8192'
'innodb_lru_scan_depth', '512'
'innodb_max_bitmap_file_size', '0'
'innodb_max_changed_pages', '0'
'innodb_max_dirty_pages_pct', '90.000000'
'innodb_max_dirty_pages_pct_lwm', '0.000000'
'innodb_max_purge_lag', '0'
'innodb_max_purge_lag_delay', '0'
'innodb_max_undo_log_size', '10485760'
'innodb_mirrored_log_groups', '0'
'innodb_monitor_disable', ''
'innodb_monitor_enable', ''
'innodb_monitor_reset', ''
'innodb_monitor_reset_all', ''
'innodb_mtflush_threads', '8'
'innodb_old_blocks_pct', '37'
'innodb_old_blocks_time', '1000'
'innodb_online_alter_log_max_size', '134217728'
'innodb_open_files', '2048'
'innodb_optimize_fulltext_only', 'OFF'
'innodb_page_cleaners', '4'
'innodb_page_size', '16384'
'innodb_prefix_index_cluster_optimization', 'OFF'
'innodb_print_all_deadlocks', 'OFF'
'innodb_purge_batch_size', '300'
'innodb_purge_rseg_truncate_frequency', '128'
'innodb_purge_threads', '4'
'innodb_random_read_ahead', 'OFF'
'innodb_read_ahead_threshold', '56'
'innodb_read_io_threads', '8'
'innodb_read_only', 'OFF'
'innodb_replication_delay', '0'
'innodb_rollback_on_timeout', 'OFF'
'innodb_rollback_segments', '128'
'innodb_safe_truncate', 'ON'
'innodb_sched_priority_cleaner', '0'
'innodb_scrub_log', 'OFF'
'innodb_scrub_log_speed', '256'
'innodb_show_locks_held', '0'
'innodb_show_verbose_locks', '0'
'innodb_sort_buffer_size', '1048576'
'innodb_spin_wait_delay', '6'
'innodb_stats_auto_recalc', 'ON'
'innodb_stats_include_delete_marked', 'OFF'
'innodb_stats_method', 'nulls_equal'
'innodb_stats_modified_counter', '0'
'innodb_stats_on_metadata', 'OFF'
'innodb_stats_persistent', 'ON'
'innodb_stats_persistent_sample_pages', '20'
'innodb_stats_sample_pages', '8'
'innodb_stats_traditional', 'ON'
'innodb_stats_transient_sample_pages', '8'
'innodb_status_output', 'OFF'
'innodb_status_output_locks', 'OFF'
'innodb_strict_mode', 'ON'
'innodb_support_xa', 'ON'
'innodb_sync_array_size', '1'
'innodb_sync_spin_loops', '30'
'innodb_table_locks', 'ON'
'innodb_temp_data_file_path', 'ibtmp1:12M:autoextend'
'innodb_thread_concurrency', '0'
'innodb_thread_sleep_delay', '10000'
'innodb_tmpdir', ''
'innodb_track_changed_pages', 'OFF'
'innodb_track_redo_log_now', 'OFF'
'innodb_undo_directory', './'
'innodb_undo_log_truncate', 'OFF'
'innodb_undo_logs', '128'
'innodb_undo_tablespaces', '0'
'innodb_use_atomic_writes', 'ON'
'innodb_use_fallocate', 'OFF'
'innodb_use_global_flush_log_at_trx_commit', 'OFF'
'innodb_use_mtflush', 'OFF'
'innodb_use_native_aio', 'ON'
'innodb_use_stacktrace', 'OFF'
'innodb_use_trim', 'ON'
'innodb_version', '5.7.26'
'innodb_write_io_threads', '8'
'insert_id', '0'
'interactive_timeout', '28800'
'join_buffer_size', '262144'
'join_buffer_space_limit', '2097152'
'join_cache_level', '2'
'keep_files_on_create', 'OFF'
'key_buffer_size', '20971520'
'key_cache_age_threshold', '300'
'key_cache_block_size', '1024'
'key_cache_division_limit', '100'
'key_cache_file_hash_size', '512'
'key_cache_segments', '0'
'large_files_support', 'ON'
'large_page_size', '0'
'large_pages', 'OFF'
'last_gtid', ''
'last_insert_id', '0'
'lc_messages', 'en_US'
'lc_messages_dir', ''
'lc_time_names', 'en_US'
'license', 'GPL'
'local_infile', 'ON'
'lock_wait_timeout', '86400'
'locked_in_memory', 'OFF'
'log_bin', 'ON'
'log_bin_basename', '/mnt/volume-01-part1/mysql/mysql-bin'
'log_bin_compress', 'OFF'
'log_bin_compress_min_len', '256'
'log_bin_index', '/mnt/volume-01-part1/mysql/mysql-bin.index'
'log_bin_trust_function_creators', 'OFF'
'log_error', '/var/log/mariadb/mariadb.log'
'log_output', 'FILE'
'log_queries_not_using_indexes', 'OFF'
'log_slave_updates', 'OFF'
'log_slow_admin_statements', 'ON'
'log_slow_filter', 'admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk'
'log_slow_rate_limit', '1'
'log_slow_slave_statements', 'ON'
'log_slow_verbosity', ''
'log_tc_size', '24576'
'log_warnings', '2'
'long_query_time', '10.000000'
'low_priority_updates', 'OFF'
'lower_case_file_system', 'OFF'
'lower_case_table_names', '0'
'master_verify_checksum', 'OFF'
'max_allowed_packet', '16777216'
'max_binlog_cache_size', '18446744073709547520'
'max_binlog_size', '1073741824'
'max_binlog_stmt_cache_size', '18446744073709547520'
'max_connect_errors', '10'
'max_connections', '100'
'max_delayed_threads', '20'
'max_digest_length', '1024'
'max_error_count', '64'
'max_heap_table_size', '134217728'
'max_insert_delayed_threads', '20'
'max_join_size', '18446744073709551615'
'max_length_for_sort_data', '1024'
'max_long_data_size', '16777216'
'max_prepared_stmt_count', '16382'
'max_recursive_iterations', '4294967295'
'max_relay_log_size', '1073741824'
'max_seeks_for_key', '4294967295'
'max_session_mem_used', '9223372036854775807'
'max_sort_length', '1024'
'max_sp_recursion_depth', '0'
'max_statement_time', '0.000000'
'max_tmp_tables', '32'
'max_user_connections', '0'
'max_write_lock_count', '4294967295'
'metadata_locks_cache_size', '1024'
'metadata_locks_hash_instances', '8'
'min_examined_row_limit', '0'
'mrr_buffer_size', '262144'
'multi_range_count', '256'
'myisam_block_size', '1024'
'myisam_data_pointer_size', '6'
'myisam_max_sort_file_size', '9223372036853727232'
'myisam_mmap_size', '18446744073709551615'
'myisam_recover_options', 'DEFAULT'
'myisam_repair_threads', '1'
'myisam_sort_buffer_size', '134216704'
'myisam_stats_method', 'NULLS_UNEQUAL'
'myisam_use_mmap', 'OFF'
'mysql56_temporal_format', 'ON'
'net_buffer_length', '16384'
'net_read_timeout', '30'
'net_retry_count', '10'
'net_write_timeout', '60'
'old', 'OFF'
'old_alter_table', 'OFF'
'old_mode', ''
'old_passwords', 'OFF'
'open_files_limit', '4233'
'optimizer_prune_level', '1'
'optimizer_search_depth', '62'
'optimizer_selectivity_sampling_limit', '100'
'optimizer_switch', 'index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on'
'optimizer_use_condition_selectivity', '1'
'performance_schema', 'OFF'
'performance_schema_accounts_size', '-1'
'performance_schema_digests_size', '-1'
'performance_schema_events_stages_history_long_size', '-1'
'performance_schema_events_stages_history_size', '-1'
'performance_schema_events_statements_history_long_size', '-1'
'performance_schema_events_statements_history_size', '-1'
'performance_schema_events_waits_history_long_size', '-1'
'performance_schema_events_waits_history_size', '-1'
'performance_schema_hosts_size', '-1'
'performance_schema_max_cond_classes', '80'
'performance_schema_max_cond_instances', '-1'
'performance_schema_max_digest_length', '1024'
'performance_schema_max_file_classes', '50'
'performance_schema_max_file_handles', '32768'
'performance_schema_max_file_instances', '-1'
'performance_schema_max_mutex_classes', '200'
'performance_schema_max_mutex_instances', '-1'
'performance_schema_max_rwlock_classes', '40'
'performance_schema_max_rwlock_instances', '-1'
'performance_schema_max_socket_classes', '10'
'performance_schema_max_socket_instances', '-1'
'performance_schema_max_stage_classes', '150'
'performance_schema_max_statement_classes', '188'
'performance_schema_max_table_handles', '-1'
'performance_schema_max_table_instances', '-1'
'performance_schema_max_thread_classes', '50'
'performance_schema_max_thread_instances', '-1'
'performance_schema_session_connect_attrs_size', '-1'
'performance_schema_setup_actors_size', '100'
'performance_schema_setup_objects_size', '100'
'performance_schema_users_size', '-1'
'pid_file', '/mnt/volume-01-part1/mysql/dtd-db-01.pid'
'plugin_dir', '/usr/lib64/mysql/plugin/'
'plugin_maturity', 'unknown'
'port', '3306'
'preload_buffer_size', '32768'
'profiling', 'OFF'
'profiling_history_size', '15'
'progress_report_time', '5'
'protocol_version', '10'
'proxy_user', ''
'pseudo_slave_mode', 'OFF'
'pseudo_thread_id', '4712'
'query_alloc_block_size', '16384'
'query_cache_limit', '2097152'
'query_cache_min_res_unit', '4096'
'query_cache_size', '134217728'
'query_cache_strip_comments', 'OFF'
'query_cache_type', 'ON'
'query_cache_wlock_invalidate', 'OFF'
'query_prealloc_size', '24576'
'rand_seed1', '289429692'
'rand_seed2', '778761261'
'range_alloc_block_size', '4096'
'read_binlog_speed_limit', '0'
'read_buffer_size', '131072'
'read_only', 'OFF'
'read_rnd_buffer_size', '262144'
'relay_log', ''
'relay_log_basename', ''
'relay_log_index', ''
'relay_log_info_file', 'relay-log.info'
'relay_log_purge', 'ON'
'relay_log_recovery', 'OFF'
'relay_log_space_limit', '0'
'replicate_annotate_row_events', 'ON'
'replicate_do_db', ''
'replicate_do_table', ''
'replicate_events_marked_for_skip', 'REPLICATE'
'replicate_ignore_db', ''
'replicate_ignore_table', ''
'replicate_wild_do_table', ''
'replicate_wild_ignore_table', ''
'report_host', ''
'report_password', ''
'report_port', '3306'
'report_user', ''
'rowid_merge_buff_size', '8388608'
'secure_auth', 'ON'
'secure_file_priv', ''
'server_id', '1'
'session_track_schema', 'ON'
'session_track_state_change', 'OFF'
'session_track_system_variables', ''
'session_track_transaction_info', 'OFF'
'skip_external_locking', 'ON'
'skip_name_resolve', 'ON'
'skip_networking', 'OFF'
'skip_parallel_replication', 'OFF'
'skip_replication', 'OFF'
'skip_show_database', 'OFF'
'slave_compressed_protocol', 'OFF'
'slave_ddl_exec_mode', 'IDEMPOTENT'
'slave_domain_parallel_threads', '0'
'slave_exec_mode', 'STRICT'
'slave_load_tmpdir', '/tmp'
'slave_max_allowed_packet', '1073741824'
'slave_net_timeout', '60'
'slave_parallel_max_queued', '131072'
'slave_parallel_mode', 'conservative'
'slave_parallel_threads', '0'
'slave_parallel_workers', '0'
'slave_run_triggers_for_rbr', 'NO'
'slave_skip_errors', 'OFF'
'slave_sql_verify_checksum', 'ON'
'slave_transaction_retries', '10'
'slave_type_conversions', ''
'slow_launch_time', '2'
'slow_query_log', 'OFF'
'slow_query_log_file', 'dtd-db-01-slow.log'
'socket', '/mnt/volume-01-part1/mysql/mysql.sock'
'sort_buffer_size', '2097152'
'sql_auto_is_null', 'OFF'
'sql_big_selects', 'ON'
'sql_buffer_result', 'OFF'
'sql_log_bin', 'ON'
'sql_log_off', 'OFF'
'sql_mode', 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
'sql_notes', 'ON'
'sql_quote_show_create', 'ON'
'sql_safe_updates', 'OFF'
'sql_select_limit', '18446744073709551615'
'sql_slave_skip_counter', '0'
'sql_warnings', 'OFF'
'ssl_ca', ''
'ssl_capath', ''
'ssl_cert', ''
'ssl_cipher', ''
'ssl_crl', ''
'ssl_crlpath', ''
'ssl_key', ''
'standard_compliant_cte', 'ON'
'storage_engine', 'MyISAM'
'stored_program_cache', '256'
'strict_password_validation', 'ON'
'sync_binlog', '0'
'sync_frm', 'ON'
'sync_master_info', '10000'
'sync_relay_log', '10000'
'sync_relay_log_info', '10000'
'system_time_zone', 'EDT'
'table_definition_cache', '400'
'table_open_cache', '2048'
'table_open_cache_instances', '8'
'thread_cache_size', '12'
'thread_concurrency', '12'
'thread_handling', 'one-thread-per-connection'
'thread_pool_idle_timeout', '60'
'thread_pool_max_threads', '65536'
'thread_pool_oversubscribe', '3'
'thread_pool_prio_kickup_timer', '1000'
'thread_pool_priority', 'auto'
'thread_pool_size', '6'
'thread_pool_stall_limit', '500'
'thread_stack', '196608'
'time_format', '%H:%i:%s'
'time_zone', 'SYSTEM'
'timed_mutexes', 'OFF'
'timestamp', '1562162811.946458'
'tmp_disk_table_size', '18446744073709551615'
'tmp_memory_table_size', '67108864'
'tmp_table_size', '67108864'
'tmpdir', '/tmp'
'transaction_alloc_block_size', '8192'
'transaction_prealloc_size', '4096'
'tx_isolation', 'REPEATABLE-READ'
'tx_read_only', 'OFF'
'unique_checks', 'ON'
'updatable_views_with_limit', 'YES'
'use_stat_tables', 'NEVER'
'userstat', 'OFF'
'version', '10.2.25-MariaDB-log'
'version_comment', 'MariaDB Server'
'version_compile_machine', 'x86_64'
'version_compile_os', 'Linux'
'version_malloc_library', 'system'
'version_ssl_library', 'OpenSSL 1.0.2k-fips  26 Jan 2017'
'wait_timeout', '28800'
'warning_count', '0'
'wsrep_osu_method', 'TOI'
'wsrep_auto_increment_control', 'ON'
'wsrep_causal_reads', 'OFF'
'wsrep_certification_rules', 'strict'
'wsrep_certify_nonpk', 'ON'
'wsrep_cluster_address', ''
'wsrep_cluster_name', 'my_wsrep_cluster'
'wsrep_convert_lock_to_trx', 'OFF'
'wsrep_data_home_dir', '/mnt/volume-01-part1/mysql/'
'wsrep_dbug_option', ''
'wsrep_debug', 'OFF'
'wsrep_desync', 'OFF'
'wsrep_dirty_reads', 'OFF'
'wsrep_drupal_282555_workaround', 'OFF'
'wsrep_forced_binlog_format', 'NONE'
'wsrep_gtid_domain_id', '0'
'wsrep_gtid_mode', 'OFF'
'wsrep_load_data_splitting', 'ON'
'wsrep_log_conflicts', 'OFF'
'wsrep_max_ws_rows', '0'
'wsrep_max_ws_size', '2147483647'
'wsrep_mysql_replication_bundle', '0'
'wsrep_node_address', ''
'wsrep_node_incoming_address', 'AUTO'
'wsrep_node_name', 'dtd-db-01'
'wsrep_notify_cmd', ''
'wsrep_on', 'OFF'
'wsrep_patch_version', 'wsrep_25.24'
'wsrep_provider', 'none'
'wsrep_provider_options', ''
'wsrep_recover', 'OFF'
'wsrep_reject_queries', 'NONE'
'wsrep_replicate_myisam', 'OFF'
'wsrep_restart_slave', 'OFF'
'wsrep_retry_autocommit', '1'
'wsrep_slave_fk_checks', 'ON'
'wsrep_slave_uk_checks', 'OFF'
'wsrep_slave_threads', '1'
'wsrep_sst_auth', ''
'wsrep_sst_donor', ''
'wsrep_sst_donor_rejects_queries', 'OFF'
'wsrep_sst_method', 'rsync'
'wsrep_sst_receive_address', 'AUTO'
'wsrep_start_position', '00000000-0000-0000-0000-000000000000:-1'
'wsrep_sync_wait', '0'

编辑:
显示全局状态；
https://pastebin.com/3iXGGZ6N

(对不起，太长了)

Answer 1

观察值:

版本:10.2.25-MariaDB-log

16 GB的RAM

正常运行时间= 03:04:23;请在几个小时后重新运行“显示全局状态”。

您不在Windows上运行。

运行64位版本

您似乎正在完全(或主要)在运行InnoDB。

更重要的问题:

binlog_cache_size太高了-从30M变为1M

建议使用慢日志；见 http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog

详细信息和其他观察结果:
( binlog_cache_size * max_connections / _ram ) = 30M * 100 / 16384M = 18.3%-用于在Binlog途中缓存事务的RAM。
-减少binlog_cache_size和/或max_connections
( innodb_lru_scan_depth * innodb_page_cleaners ) = 512 * 4 = 2,048-每秒页面清洁器的工作量。
-通过降低lru_scan_depth，可以修复“InnoDB:page_cleaner:花费1000毫秒的预期循环时间……”:考虑1000 / innodb_page_cleaners
( innodb_page_cleaners / innodb_buffer_pool_instances ) = 4 / 6 = 0.667-innodb_page_cleaners
-建议将innodb_page_cleaners设置为innodb_buffer_pool_instances
( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 181,655 / 737235 = 24.6%-当前未使用的buffer_pool的百分比
-innodb_buffer_pool_size是否大于所需的大小？
( Innodb_pages_written / Innodb_buffer_pool_write_requests ) = 33,499 / 70858 = 47.3%-写必须打到磁盘的请求
-检查innodb_buffer_pool_size
( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 11,063 / 60 * 3072M / 24557056 = 24,186-InnoDB日志轮换之间的分钟数，从5.6.8开始，可以动态更改。一定还要更改my.cnf。
-(建议每两次轮换60分钟有点随意。)调整innodb_log_file_size。 (无法在AWS中更改。)
( default_tmp_storage_engine ) = default_tmp_storage_engine = ( innodb_flush_neighbors ) = 1-将块写入磁盘时的次要优化。
-SSD驱动器使用0; 1个用于HDD。
( innodb_io_capacity ) = 200-磁盘上每秒支持的I / O操作数。对于慢速驱动器为100； 200用于旋转驱动器；适用于SSD的1000-2000；乘以RAID因子。
( sync_binlog ) = 0-使用1可以增强安全性，但I / O = 1可能会导致很多“查询结束”； = 0可能会导致“binlog在不可能的位置”并在崩溃时丢失事务，但是速度更快。
( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF-是否记录所有死锁。
-如果您遇到死锁困扰，请将其打开。警告:如果您有很多死锁，这可能会在磁盘上写入很多内容。
( innodb_buffer_pool_populate ) = OFF = 0-NUMA控件
( default_storage_engine ) = default_storage_engine = MyISAM-MyISAM已被弃用，并且大多数情况不如InnoDB理想
( local_infile ) = local_infile = ON-local_infile = ON是潜在的安全问题
( query_cache_size ) = 128M-质量控制的大小
-太小=没什么用。太大=过多的开销。推荐0或不超过50M。
( Created_tmp_disk_tables ) = 15,777 / 11063 = 1.4 /sec-创建磁盘“临时”表作为复杂SELECT的一部分的频率
-增加tmp_table_size和max_heap_table_size。
在使用MEMORY代替MyISAM时检查临时表的规则。较小的架构或查询更改也许可以避免使用MyISAM。
更好的索引和重新编制查询更有可能会有所帮助。
( Created_tmp_disk_tables / Questions ) = 15,777 / 104672 = 15.1%-需要磁盘tmp表的查询的百分比
-更好的索引/无斑点/等
( Created_tmp_disk_tables / Created_tmp_tables ) = 15,777 / 33066 = 47.7%-溢出到磁盘的临时表的百分比
-也许增加tmp_table_size和max_heap_table_size;改善指标；避免斑点等
( Select_full_join / Com_select ) = 6,864 / 55356 = 12.4%-无索引连接的选择的百分比
-在JOIN中使用的表中添加合适的索引。
( Select_scan ) = 17,137 / 11063 = 1.5 /sec-全表扫描
-添加索引/优化查询(除非它们是很小的表)
( Select_scan / Com_select ) = 17,137 / 55356 = 31.0%-执行全表扫描的选择的百分比。 (可能会被存储例程欺骗。)
-添加索引/优化查询
( binlog_format ) = binlog_format = MIXED-STATEMENT / ROW / MIXED。 ROW是首选；它可能成为默认值。
( expire_logs_days ) = 3-自动清除二进制日志的时间(在这几天之后)
-太大(或为零)=占用磁盘空间；太小=需要快速响应网络/计算机崩溃。
(如果log_bin = OFF不相关)
( wsrep_on ) = wsrep_on = OFF-Galera:打开复制
-为什么关闭？
( innodb_autoinc_lock_mode ) = 1-Galera:需求2-2 =“interleaved”; 1 =“连续”是典型的； 0 =“传统”。
( wsrep_log_conflicts ) = wsrep_log_conflicts = OFF-如果在COMMIT期间遇到死锁冲突，则此标志可能会有所帮助。
( slow_query_log ) = slow_query_log = OFF-是否记录慢速查询。 (5.1.12)
( long_query_time ) = 10-定义“慢”查询的截止时间(秒)。
-建议2
( Subquery_cache_hit / ( Subquery_cache_hit + Subquery_cache_miss ) ) = 10 / ( 10 + 1074 ) = 0.92%-子查询缓存命中率
-考虑设置SET Optimizer_switch ='subquery_cache = off';

异​​常小:

Acl_users = 5
Com_show_fields = 0
Com_show_tables = 0
Handler_read_rnd_next / Handler_read_rnd = 6.13
Innodb_buffer_pool_write_requests / Innodb_buffer_pool_pages_flushed = 2.12
Key_blocks_used = 0
Key_read_requests = 0
Table_locks_immediate = 6.5 /HR
eq_range_index_dive_limit = 0   (always use index dives)
innodb_log_block_size = 0  (Deprecated as of MariaDB 10.2.6)
innodb_max_bitmap_file_size = 0
innodb_max_changed_pages = 0
innodb_mirrored_log_groups = 0
innodb_sched_priority_cleaner = 0
innodb_show_locks_held = 0
lock_wait_timeout = 86,400

异​​常大:

((query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache) / query_cache_min_res_unit = 6.67
(query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache = 27,332
Aria_pagecache_writes = 11 /sec
Com_create_temporary_table = 0.65 /HR
Com_load = 0.65 /HR
Innodb_buffer_pool_bytes_data = 816674 /sec
Qcache_total_blocks * query_cache_min_res_unit / Qcache_queries_in_cache = 157,586
binlog_cache_size = 3.15e+7
group_concat_max_len = 1.05e+6
innodb_adaptive_hash_index_partitions = 8
innodb_background_scrub_data_check_interval = 0.33 /sec
innodb_background_scrub_data_interval = 55 /sec
max_relay_log_size = 1024MB
thread_concurrency = 12

字符串异常:

binlog_annotate_row_events = ON
ft_boolean_syntax = + -><()~*:\"\"&
innodb_corrupt_table_action = deprecated
innodb_defragment = ON
innodb_fast_shutdown = 1
innodb_locking_fake_changes = OFF
innodb_use_atomic_writes = ON
innodb_use_global_flush_log_at_trx_commit = OFF
innodb_use_trim = ON
log_slow_admin_statements = ON
log_slow_slave_statements = ON
myisam_stats_method = NULLS_UNEQUAL
opt_s__engine_condition_pushdown = off
opt_s__orderby_uses_equalities = on
replicate_annotate_row_events = ON
session_track_system_variables =