0

DEDICATED VM FOR MARIADB -

  • CENTOS 7.7 \\ 16VCPU/250G - JBOD DISKS/NO SSD.
  • OS PARTITION AND DATABASE ARE ON DIFFERENT FILESYSTEM.

Span of every two weeks, Kernel OOM happens. Reducing innodb_buffer_pool_size maybe not the only solution.

What else can be done to tune database -

DB SETTING -

datadir = /data1/mysql
socket = /data1/mysql/mysql.sock
lower_case_table_names = 1
query_cache_size = 64M
query_cache_type = 1
innodb_log_file_size = 512M
#innodb_buffer_pool_size = 200733M
innodb_buffer_pool_size = 150G
key_buffer_size = 8388608
max_allowed_packet = 512M
thread_cache_size = 1000
max-connections = 1000
query_cache_limit = 8M
read_buffer_size = 64M
read_rnd_buffer_size = 128M
sort_buffer_size = 64M
expire_logs_days = 7
innodb_strict_mode = 0
interactive_timeout = 3600
wait_timeout = 3600
open_files_limit =  30000
bulk_insert_buffer_size =  512M
query_cache_min_res_unit = 4K
table_open_cache = 2048
tmp_table_size = 128M
max_heap_table_size = 128M
join_buffer_size = 512M
net_write_timeout = 3600
net_read_timeout = 3600
innodb_log_files_in_group = 2

#wait_timeout = 36000

###manual part
innodb_buffer_pool_instances = 16
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

##manual part end

#
# * Galera-related settings
#
[galera]
# Mandatory settings
#wsrep_on=ON
#wsrep_provider=
#wsrep_cluster_address=
#binlog_format=row
#default_storage_engine=InnoDB
#innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
#bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0

# this is only for embedded server
[embedded]

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

# This group is only read by MariaDB-10.3 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.3]

Global Variables

"Variable_name" "Value"
"alter_algorithm"   "DEFAULT"
"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"  "250"
"basedir"   "/usr/"
"big_tables"    "OFF"
"bind_address"  ""
"binlog_annotate_row_events"    "ON"
"binlog_cache_size" "32768"
"binlog_checksum"   "CRC32"
"binlog_commit_wait_count"  "0"
"binlog_commit_wait_usec"   "100000"
"binlog_direct_non_transactional_updates"   "OFF"
"binlog_file_cache_size"    "16384"
"binlog_format" "MIXED"
"binlog_optimize_thread_scheduling" "ON"
"binlog_row_image"  "FULL"
"binlog_stmt_cache_size"    "32768"
"bulk_insert_buffer_size"   "536870912"
"character_set_client"  "latin1"
"character_set_connection"  "latin1"
"character_set_database"    "latin1"
"character_set_filesystem"  "binary"
"character_set_results" "latin1"
"character_set_server"  "latin1"
"character_set_system"  "utf8"
"character_sets_dir"    "/usr/share/mysql/charsets/"
"check_constraint_checks"   "ON"
"collation_connection"  "latin1_swedish_ci"
"collation_database"    "latin1_swedish_ci"
"collation_server"  "latin1_swedish_ci"
"column_compression_threshold"  "100"
"column_compression_zlib_level" "6"
"column_compression_zlib_strategy"  "DEFAULT_STRATEGY"
"column_compression_zlib_wrap"  "OFF"
"completion_type"   "NO_CHAIN"
"concurrent_insert" "AUTO"
"connect_timeout"   "10"
"core_file" "OFF"
"datadir"   "/data1/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_regex_flags"   ""
"default_storage_engine"    "InnoDB"
"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"
"event_scheduler"   "OFF"
"expensive_subquery_limit"  "100"
"expire_logs_days"  "7"
"explicit_defaults_for_timestamp"   "OFF"
"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"  "p0162v.log"
"group_concat_max_len"  "1048576"
"gtid_binlog_pos"   ""
"gtid_binlog_state" ""
"gtid_current_pos"  ""
"gtid_domain_id"    "0"
"gtid_ignore_duplicates"    "OFF"
"gtid_pos_auto_engines" ""
"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"  "YES"
"histogram_size"    "0"
"histogram_type"    "SINGLE_PREC_HB"
"host_cache_size"   "653"
"hostname"  "p0162v"
"idle_readonly_transaction_timeout" "0"
"idle_transaction_timeout"  "0"
"idle_write_transaction_timeout"    "0"
"ignore_builtin_innodb" "OFF"
"ignore_db_dirs"    ""
"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_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"  "16"
"innodb_buffer_pool_load_abort" "OFF"
"innodb_buffer_pool_load_at_startup"    "ON"
"innodb_buffer_pool_load_now"   "OFF"
"innodb_buffer_pool_size"   "161061273600"
"innodb_change_buffer_max_size" "25"
"innodb_change_buffering"   "all"
"innodb_checksum_algorithm" "crc32"
"innodb_checksums"  "ON"
"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_data_file_path" "ibdata1:12M:autoextend"
"innodb_data_home_dir"  ""
"innodb_deadlock_detect"    "ON"
"innodb_default_encryption_key_id"  "1"
"innodb_default_row_format" "dynamic"
"innodb_defragment" "OFF"
"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_encrypt_log"    "OFF"
"innodb_encrypt_tables" "OFF"
"innodb_encryption_rotate_key_age"  "1"
"innodb_encryption_rotation_iops"   "100"
"innodb_encryption_threads" "0"
"innodb_fast_shutdown"  "1"
"innodb_fatal_semaphore_wait_threshold" "600"
"innodb_file_format"    ""
"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_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_io_capacity"    "200"
"innodb_io_capacity_max"    "2000"
"innodb_large_prefix"   ""
"innodb_lock_schedule_algorithm"    "fcfs"
"innodb_lock_wait_timeout"  "50"
"innodb_locks_unsafe_for_binlog"    "OFF"
"innodb_log_buffer_size"    "16777216"
"innodb_log_checksums"  "ON"
"innodb_log_compressed_pages"   "ON"
"innodb_log_file_size"  "536870912"
"innodb_log_files_in_group" "2"
"innodb_log_group_home_dir" "./"
"innodb_log_optimize_ddl"   "ON"
"innodb_log_write_ahead_size"   "8192"
"innodb_lru_scan_depth" "1024"
"innodb_max_dirty_pages_pct"    "75.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_monitor_disable"    ""
"innodb_monitor_enable" ""
"innodb_monitor_reset"  ""
"innodb_monitor_reset_all"  ""
"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"    "4"
"innodb_read_only"  "OFF"
"innodb_replication_delay"  "0"
"innodb_rollback_on_timeout"    "OFF"
"innodb_rollback_segments"  "128"
"innodb_scrub_log"  "OFF"
"innodb_scrub_log_speed"    "256"
"innodb_sort_buffer_size"   "1048576"
"innodb_spin_wait_delay"    "4"
"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"    "OFF"
"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_undo_directory" "./"
"innodb_undo_log_truncate"  "OFF"
"innodb_undo_logs"  "128"
"innodb_undo_tablespaces"   "0"
"innodb_use_atomic_writes"  "ON"
"innodb_use_native_aio" "ON"
"innodb_version"    "10.3.14"
"innodb_write_io_threads"   "4"
"interactive_timeout"   "3600"
"join_buffer_size"  "536870912"
"join_buffer_space_limit"   "2097152"
"join_cache_level"  "2"
"keep_files_on_create"  "OFF"
"key_buffer_size"   "8388608"
"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"
"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"   "OFF"
"log_bin_basename"  ""
"log_bin_compress"  "OFF"
"log_bin_compress_min_len"  "256"
"log_bin_index" ""
"log_bin_trust_function_creators"   "OFF"
"log_disabled_statements"   "sp"
"log_error" "/var/log/mariadb.log"
"log_output"    "FILE"
"log_queries_not_using_indexes" "OFF"
"log_slave_updates" "OFF"
"log_slow_admin_statements" "ON"
"log_slow_disabled_statements"  "sp"
"log_slow_filter"   "admin,filesort,filesort_on_disk,filesort_priority_queue,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"    "1"
"master_verify_checksum"    "OFF"
"max_allowed_packet"    "536870912"
"max_binlog_cache_size" "18446744073709547520"
"max_binlog_size"   "1073741824"
"max_binlog_stmt_cache_size"    "18446744073709547520"
"max_connect_errors"    "100"
"max_connections"   "1000"
"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"    "536870912"
"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"    "BACKUP,QUICK"
"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"  "3600"
"net_retry_count"   "10"
"net_write_timeout" "3600"
"old"   "OFF"
"old_alter_table"   "DEFAULT"
"old_mode"  ""
"old_passwords" "OFF"
"open_files_limit"  "16364"
"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,split_materialized=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"  "160"
"performance_schema_max_statement_classes"  "200"
"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"  "/data1/mysql/p0162v.pid"
"plugin_dir"    "/usr/lib64/mysql/plugin/"
"plugin_maturity"   "gamma"
"port"  "3306"
"preload_buffer_size"   "32768"
"profiling" "OFF"
"profiling_history_size"    "15"
"progress_report_time"  "5"
"protocol_version"  "10"
"proxy_protocol_networks"   ""
"query_alloc_block_size"    "16384"
"query_cache_limit" "8388608"
"query_cache_min_res_unit"  "4096"
"query_cache_size"  "67108864"
"query_cache_strip_comments"    "OFF"
"query_cache_type"  "ON"
"query_cache_wlock_invalidate"  "OFF"
"query_prealloc_size"   "24576"
"range_alloc_block_size"    "4096"
"read_binlog_speed_limit"   "0"
"read_buffer_size"  "67108864"
"read_only" "OFF"
"read_rnd_buffer_size"  "134217728"
"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"
"rpl_semi_sync_master_enabled"  "OFF"
"rpl_semi_sync_master_timeout"  "10000"
"rpl_semi_sync_master_trace_level"  "32"
"rpl_semi_sync_master_wait_no_slave"    "ON"
"rpl_semi_sync_master_wait_point"   "AFTER_COMMIT"
"rpl_semi_sync_slave_delay_master"  "OFF"
"rpl_semi_sync_slave_enabled"   "OFF"
"rpl_semi_sync_slave_kill_conn_timeout" "5"
"rpl_semi_sync_slave_trace_level"   "32"
"secure_auth"   "ON"
"secure_file_priv"  ""
"secure_timestamp"  "NO"
"server_id" "1"
"session_track_schema"  "ON"
"session_track_state_change"    "OFF"
"session_track_system_variables"    "autocommit,character_set_client,character_set_connection,character_set_results,time_zone"
"session_track_transaction_info"    "OFF"
"skip_external_locking" "ON"
"skip_name_resolve" "OFF"
"skip_networking"   "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_transaction_retry_errors"    "1213,1205"
"slave_transaction_retry_interval"  "0"
"slave_type_conversions"    ""
"slow_launch_time"  "2"
"slow_query_log"    "OFF"
"slow_query_log_file"   "p0162v-slow.log"
"socket"    "/data1/mysql/mysql.sock"
"sort_buffer_size"  "67108864"
"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"    "InnoDB"
"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"  "IST"
"system_versioning_alter_history"   "ERROR"
"system_versioning_asof"    "DEFAULT"
"table_definition_cache"    "400"
"table_open_cache"  "2048"
"table_open_cache_instances"    "8"
"tcp_keepalive_interval"    "0"
"tcp_keepalive_probes"  "0"
"tcp_keepalive_time"    "0"
"thread_cache_size" "1000"
"thread_concurrency"    "10"
"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"  "16"
"thread_pool_stall_limit"   "500"
"thread_stack"  "299008"
"time_format"   "%H:%i:%s"
"time_zone" "SYSTEM"
"timed_mutexes" "OFF"
"tmp_disk_table_size"   "18446744073709551615"
"tmp_memory_table_size" "134217728"
"tmp_table_size"    "134217728"
"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.3.14-MariaDB"
"version_comment"   "MariaDB Server"
"version_compile_machine"   "x86_64"
"version_compile_os"    "Linux"
"version_malloc_library"    "system"
"version_source_revision"   "ab7b9cf9122f6eb02744082db8672a920ae6009f"
"version_ssl_library"   "OpenSSL 1.0.2k-fips  26 Jan 2017"
"wait_timeout"  "3600"
"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"   "/data1/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"   "p0162v"
"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"

Engine INNODB STATUS

6
  • Hmmm. Those values seem safe for 250GB of RAM.
    – Rick James
    Dec 9 '20 at 22:21
  • Are you using "huge pages"? Is the OOM caused by lack of swap space? Do you have any RAM-disks?
    – Rick James
    Dec 9 '20 at 22:25
  • @RickJames huge pages disabled. swappiness configured as value 10.
    – Indranil
    Dec 9 '20 at 23:22
  • Seems unlikely that MariaDB is at fault. Consider posting this in an OS-related forum. Focus on asking how to debug a mysterious OOM, not database. Leave this posting here in case you get some clues that send you back here.
    – Rick James
    Dec 9 '20 at 23:33
  • @RickJames this VM is dedicated only for Mariadb. apart from swappiness and thp plus db data folders in different partitions, i dnt get anything else to tune in OS side.
    – Indranil
    Dec 10 '20 at 12:01