What is the typical cause of warnings such as this? They appear periodically, sometimes multiple times per day then not for a day or so.
2021-01-08 13:20:46 203939 (Warning) Aborted connection 203939 to db: ‘lsv’ user: ‘finder’ host: ‘126.96.36.199’ (Got timeout reading communication packets)
This database server is only queried by a few hosts, and it seems to happen with all hosts and all databases on the host. This server is connected by a 1gbit link to the Internet as well as a 10gbit local link to a web server.
This is a mariadb-10.4.17 server on fedora33 with a 5.9.16 kernel and 128GB of RAM. It’s the only function of this box. It’s been happening for quite some time. It doesn’t seem to matter How do I troubleshoot this? Could this be a networking problem?
I would appreciate any ideas you might have. Here is the contents of the 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