Search code examples
mysqlmariadbmariadb-10.4

MariaDB 10.4 random performance degradation

I have a server with such parameters:

  • OS: Ubuntu 18.04.4 LTS x86_64
  • Host: X11DPi-N(T)
  • Kernel: 4.15.0-112-generic
  • CPU: Intel Xeon Silver 4214 (48) @ 2.201GHz
  • GPU: ASPEED Technology, Inc. ASPEED Graphics Family
  • Memory: 18552MiB / 96336MiB
  • SSD SAMSUNG MZQLB960HAJR-00007 894.3G x 2

With installed 5.5.5-10.4.12-MariaDB-1:10.4.12+maria~bionic. On this screenshot shown a standard DB load:

enter image description here

So I have around 400-500 selects per second (mostly from not so large table with 500k records), 100-190 updates per second, and around 50-150 simultaneous connections.

My problem is: sometimes, for no apparent reason, the server has 2000-3000 open connections/processes. According to SHOW FULL PROCESSLIST they are standard SQL requests but with 'Sending data' state and with 400-500 seconds of runtime. Of course, at this time the server freezes and cannot function normally. I said "for no apparent reason" because at this time I do not see any increase in users number or an increase in activity on the website. In addition, restarting the MariaDB service or a complete reboot of the server helps to get out of this situation, but not always: sometimes even after rebooting I almost instantly get the same 2000-3000 frozen processes.

Has anyone encountered similar database behavior? I would be grateful for any ideas.

UPD:

  1. All of my SELECTs calls only one table (~500k records, without JOIN and/or subqueries), and mostly of them have LIMIT 1, so not so large amount of data.

  2. Error log shown a lot of records like this: 2020-08-26 22:12:35 787380 [Warning] Aborted connection 787380 to db: ... (Got timeout reading communication packets)

  3. innodb_lock_wait_timeout is 50 (default)

  4. Slow query log does not shown something unusual

  5. My optimizer_switch settings: 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=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on

Solution

  • The solution turned out to be quite simple: after studying the MariaDB documentation (especially this article https://mariadb.com/kb/en/thread-pool-in-mariadb/) I added the following to my.cnf and the problem is gone

    thread_handling=pool-of-threads
thread_pool_size=48 
#48 is a number of CPUs