Performance
mariadb：中止連接..讀取通信數據包超時
此類警告的典型原因是什麼？它們定期出現，有時每天多次出現，然後一天左右不出現。
2021-01-08 13:20:46 203939
$$ Warning $$Aborted connection 203939 to db: ’lsv’ user: ‘finder’ host: ‘23.227.111.186’ （讀取通信數據包超時） 這個數據庫伺服器只被少數幾台主機查詢，而且似乎發生在所有主機和主機上的所有數據庫上。該伺服器通過 1gbit 連結連接到 Internet 以及 10gbit 本地連結到 Web 伺服器。
這是 fedora33 上的 mariadb-10.4.17 伺服器，具有 5.9.16 核心和 128GB 記憶體。這是這個盒子的唯一功能。它已經發生了相當長的一段時間。這似乎無關緊要如何解決這個問題？這可能是網路問題嗎？
我會很感激你可能有的任何想法。這是my.cnf的內容。
# cat my.cnf |grep -Ev '^$|^#' [client] port = 3306 socket = /var/lib/mysql/mysql.sock default-character-set = utf8mb4 [mysqld] character-set-client-handshake = FALSE character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci max_connections=600 replicate_do_db='txrepdb' replicate_do_db='sqlgrey' replicate_do_db='sbclient' port = 3306 socket = /var/lib/mysql/mysql.sock skip-external-locking key_buffer_size = 256M max_allowed_packet = 512M join_buffer_size = 2M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 64M query_cache_size = 0 query_cache_type = 0 relay_log_space_limit = 500M relay_log_purge = 1 log-slave-updates = 1 local_infile = OFF binlog_format = ROW max_heap_table_size = 1024M tmp_table_size = 1024M performance_schema=ON performance-schema-instrument='stage/%=ON' performance-schema-consumer-events-stages-current=ON performance-schema-consumer-events-stages-history=ON performance-schema-consumer-events-stages-history-long=ON relay-log=havoc-relay-bin log_bin = /var/log/mariadb/mysql-bin.log expire_logs_days = 2 max_binlog_size = 500M plugin_load=server_audit=server_audit.so plugin_load_add = query_response_time server_audit_events=connect,query server_audit_file_path = /var/log/mariadb/server_audit.log server_audit_file_rotate_size = 1G server_audit_file_rotations = 1 slow-query-log = 1 slow-query-log-file = /var/log/mariadb/mariadb-slow.log long_query_time = 1 log_error = /var/log/mariadb/mariadb-error.log binlog_format=mixed server-id = 590 report-host=havoc.example.com innodb_data_home_dir = /var/lib/mysql innodb_defragment=1 innodb_file_per_table innodb_data_file_path = ibdata1:10M:autoextend:max:500M innodb_buffer_pool_size=60G innodb_log_file_size = 1G innodb_flush_log_at_trx_commit = 2 innodb_flush_method=O_DIRECT innodb_lock_wait_timeout = 50 innodb_buffer_pool_instances = 40 open_files_limit=30000 # from 1222 for ~ 50% of planned ulimit -a Open Files of 65536 innodb_open_files=10000 # from 512 to match table_open_cache innodb_log_buffer_size=64M # from 8M for ~ 30 minutes log buffered in RAM innodb_page_cleaners=15 # from 4 to expedite page cleaning innodb_purge_threads=15 # from 4 to expedite purge processing innodb_write_io_threads=64 # from 4 to expedite multi core write processing SE5666 Rolando innodb_read_io_threads=64 # from 4 to expedite multi core read processing SE5666 9/12/11 read_rnd_buffer_size=262144 # from 4M to reduce handler_read_rnd_next of 124,386 RPS innodb_io_capacity=2100 # from 1100 to allow higher SSD iops innodb_lru_scan_depth=100 # from 1024 to conserve CPU cycles every SECOND max_connect_errors=10 table_open_cache=10000 # from 512 to reduce opened_tables RPS of 1 read_buffer_size=1572864 # from 1M to reduce handler_read_next of 32,317 RPS table_definition_cache=10000 # from 400 to reduce opened table_definitions RPS of 1 log_slow_verbosity=explain # from nothing or ADD ,explain to enhance SLOW QUERY log query_prealloc_size=32768 # from 24K to reduce CPU malloc frequency query_alloc_block_size=32768 # from 16K to reduce CPU malloc frequency transaction_prealloc_size=32768 # from 4K to reduce CPU malloc frequency transaction_alloc_block_size=32768 # from 8K to reduce CPU malloc frequency innodb_fast_shutdown=0 aria_pagecache_division_limit=50 # from 100 for WARM blocks percentage aria_pagecache_age_threshold=900 innodb_adaptive_max_sleep_delay=20000 # from 150000 ms (15 sec to 2 sec) delay when busy innodb_flushing_avg_loops=5 # from 30 to minimize innodb_buffer_pool_pages_dirty count max_seeks_for_key=64 # from ~ 4 Billion to conserve CPU max_write_lock_count=16 # from ~ 4 Billion to allow RD after nn lck requests optimizer_search_depth=0 # from 62 to allow OPTIMIZER autocalc of reasonable limit innodb_print_all_deadlocks=ON # from OFF to log event in error log for DAILY awareness wait_timeout=7200 innodb_flush_neighbors=0 # from ON to conserve CPU cycles when you have SSD/NVME interactive_timeout=7200 innodb_buffer_pool_dump_pct=90 # from 25 to minimize WARM time on STOP / START or RESTART innodb_fill_factor=93 innodb_read_ahead_threshold=8 # from 56 to reduce delays by ReaDing next EXTENT earlier sort_buffer_size=1572864 # from 1M to reduce sort_merge_passes RPS of 1 innodb_stats_sample_pages=32 # from 8 for optimizer to use more accurate cardinality min_examined_row_limit=1 # from 0 to reduce clutter in slow query log query_cache_limit=0 # from 2M to conserve RAM because your QC is OFF, as it should be. query_cache_min_res_unit=512 # from 4096 to increase QC capacity, if EVER used [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash default-character-set = utf8mb4 [myisamchk] key_buffer_size = 128M sort_buffer_size = 128M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout
該消息的原因是客戶端的不活動。如果客戶端
wait_timeout幾秒鐘內沒有任何操作，則連接將關閉並顯示一條消息。這是正常行為，並且該消息
log_warnings = 1在 mariadb 10.2.24 之前預設隱藏。所有較新的版本都有log_warnings = 2並且日誌文件現在由“（讀取通信數據包超時）”填充。只需更改一個選項即可
log_warnings = 1消除煩人的消息。