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: '23.227.111.186' (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
