1

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
5
  • Does your application include Logoff/Logout for your users to come to an orderly end of their activity? This feature would reduce threads_connected and avoid some of the 'timeout' messages, if your users would Logout when they are finished. Jan 12, 2021 at 17:14
  • Most (if not all) of these warnings are related to a joomla website and the built-in search engine functionality. I don't know if people login before they conduct a search and it's unlikely they're logging out after the search. Is that what you mean?
    – Alex Regan
    Jan 15, 2021 at 0:50
  • Yes, that is correct. Jan 15, 2021 at 12:39
  • Additional information request. # cores, any SSD or NVME devices on MySQL Host server? Post on pastebin.com and share the links. From your SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) STATUS; G) SHOW ENGINE INNODB STATUS; AND Optional helpful information, if available includes - htop OR top for most active apps, ulimit -a for a Linux/Unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions. Jan 15, 2021 at 12:43
  • If your client is connected to your MySQL host with a browser, looking at your data and has any of the following you would see this type message. Power failure at workstation, Terminating browser with top right X, CRTL+ALT+DELETE to restart workstation, unplug pc from power source, was inactive at workstation longer than 'wait_timeout' seconds and more. Jan 25, 2021 at 15:48

1 Answer 1

8

The reason for that messages is the client's inactivity. If no actions from the client for wait_timeout seconds then connection will be closed with a message.

That is the normal behaviour and that message was hidden by default log_warnings = 1 before mariadb 10.2.24. All newer releases has log_warnings = 2 and logfile now is filled by "(Got timeout reading communication packets)".

Just change an option to log_warnings = 1 to rid of annoying messages.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.