Mariadb가 filesort에서 가끔 충돌함

Mariadb가 filesort에서 가끔 충돌함

몇 년 동안 문제 없이 실행된 앱이 있습니다.갑자기, 일반적으로 파일 정렬 기능이 실행되는 것처럼 보이는 다양한 쿼리 중에 Mariadb가 손상되고 복구되는 것을 보기 시작했습니다.우리는 많은 복잡한 쿼리를 수행하고 때때로 계산된 필드에서 정렬해야 합니다.이전에는 문제 없이 작동했기 때문에 쿼리 자체에 문제가 있다고 생각하지 않습니다.

구성 문제인지 아니면 버그 또는 하드웨어 문제인지 잘 모르겠습니다.어떤 도움이든 대단히 감사합니다.

서버에는 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

(죄송합니다, SO에는 너무 깁니다)

관측치:

• 버전: 10.2.25-MariaDB-log
• 16GB RAM
• 가동 시간 = 03:04:23; 몇 시간 후 SHOW Global Status를 다시 실행하십시오.
• 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%을 캐싱하는 데 -- 또는 max_connections binlog의 RAM - binlog_cache_size 및또 / 는 max_connections 감

( innodb_lru_scan_depth * innodb_page_cleaners ) = 512 * 4 = 2,048 -- ": loop taked1000 / 파일에서 수정할 수 있습니다. -- "InnoDB: page_cleaner: 1000ms." lru_scandepth 파일 / inno.

( 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 pct가 필요합니다. 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,186InnoDB 로그 순환 간격(분) 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을 사용하고 HDD에는 1을 사용합니다.

( innodb_io_capacity ) = 200Disk에서 초당 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 = 0NUMA 제

( default_storage_engine ) = default_storage_engine = MyISAM은 더 되지 않으며 보다 덜 .ISAM은 더 이상 사용되지 않으며 대부분 InnoDB보다 덜 바람직합니다.

( local_infile ) = local_infile = ON=인 보안 문제 local_infile = ON입니다.

( query_cache_size ) = 128MQC 크기 -- 너무 작음 = 별로 쓸모가 없음.너무 큼 = 오버헤드가 너무 많습니다.합니다.0 또 50M 하합권다니장를.

( Created_tmp_disk_tables ) = 15,777 / 11063 = 1.4 /sec복잡한 SELECT의 일부로 디스크 "temp" 테이블을 만드는 빈도 - tmp_table_size 및 max_heap_table_size를 늘립니다.MyISAM 대신 MEMORY를 사용하는 경우의 임시 테이블에 대한 규칙을 확인합니다.사소한 스키마 또는 쿼리 변경으로 MyISAM이 차단될 수 있습니다.더 나은 인덱스와 쿼리 재구성이 도움이 될 가능성이 높습니다.

( Created_tmp_disk_tables / Questions ) = 15,777 / 104672 = 15.1%디스크에 tmp 테이블이 필요한 쿼리의 pct. -- 인덱스 개선/블롭 없음/ 등.

( 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"/"/"/". 될 수 .ROW가 선호되며 기본값이 될 수 있습니다.

( expire_logs_days ) = 3으로 제거하는 후) -- 큰0) = 작은( 작은) =충돌에 ( = 관련 없음) binlog는 (으)로 표시됩니다. -- (으)로 표시됩니다. 너무 작은 = 네트워크/머신 충돌에 신속하게 대응해야 합니다. (log_bin = OFF인 경우 관련 없음)

( wsrep_on ) = wsrep_on = OFF파일: -- 이 꺼집니까?복제 켜기 - 복제가 해제된 이유는 무엇입니까?

( innodb_autoinc_lock_mode ) = 1갈레라: 욕망 2 -- 2 = "인터리브"; 1 = "연속적"이 전형적이고, 0 = "수직적"입니다.

( wsrep_log_conflicts ) = wsrep_log_conflicts = OFFCOMMIT 중 교착 상태 충돌이 발생할 경우 이 플래그가 유용할 수 있습니다.

( slow_query_log ) = slow_query_log = OFF쿼리를 (12) 로부터 시작합니다. (5.1.12)

( long_query_time ) = 10 쿼리를 ( 2 - 제안 2

( Subquery_cache_hit / ( Subquery_cache_hit + Subquery_cache_miss ) ) = 10 / ( 10 + 1074 ) = 0.92% -- optimizer_switch=off'를 합니다; 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 = 

Rate Per Second=RPS - my.cnf [mysqld] 섹션에 대해 고려해야 할 제안 사항

thread_cache_size=256  # from 12 for the minimum suggested in REFMAN for 10.nn.nn MariaDB
innodb_io_capacity=1900  # from 200 to enable higher SSD IOPS
innodb_lru_scan_depth=100  # from 512 to reduce CPU cycles used for function
read_buffer_size=512K  # from 128K to reduce handler_read_next RPS of 117,695
read_rnd_buffer_size=192K  # from 256K to reduce handler_read_rnd_nxt RPS of 4,114

고지 사항:저는 제 프로필인 네트워크 프로필에 언급된 웹 사이트의 콘텐츠 작성자이며, 귀하의 성능과 추가 제안을 개선하기 위해 무료 유틸리티 스크립트를 이용할 수 있습니다.

언급URL : https://stackoverflow.com/questions/56872113/mariadb-crashes-occasionally-on-filesort