I have been trying to figure out, what could possibly cause very strange spiky load on a DB server. I am running Ubuntu 20.04LTS and mariaDB 10.5.7. 8CPUs, 16Gb RAM. There are 2 separate APIs accessing the DB server though private network.
This is a statistics server with just a few tables. Most of the queries are to 2 tables of roughly 1 million rows each right as of now. All the fields used for queries are indexed (3 int, 2 timestamps). InnoDB buffer pool size is 4Gb of which roughly 200Mb used.
This behavior seems to happen no matter what level of load I have. Currently having roughly 25 selects / second and 6 writes / second. Load averages moving around 30-40. Periods of different load seems to be split into 2 phases. 40 seconds of basically 2-3% CPU load. SHOW FULL PROCESSLIST shows basically nothing. Then suddenly CPU usage spikes to 70-80% for 20 seconds and lots of queries are being ran. Checking SHOW FULL PROCESSLIST show roughly 20+ operations being done.
No application behavior explains this, as the load should be pretty much random as there are no timed actions related to selects or inserts.
I feel like this has to be some buffer issue. Queries are possibly being buffered somewhere, I just have no idea where. Tried figuring out if it could be innodb_change_buffer_max_size, but I don't think it could be that as we are sitting at 200Mb/4Gb of buffer pool used.
My config changes. Rest are default.
[mysqld] query_cache_size=0 query_cache_type=0 innodb_buffer_pool_size=4G innodb_log_file_size=1G max_connections=550 thread_cache_size=250 [mariadb] log_error=/var/log/mysql/mariadb-errors.log slow_query_log slow_query_log_file=/var/log/mysql/mariadb-slow.log long_query_time=1.0
I have slow queries logged. There is nothing special. Even very simple queries get listed there during the "slowdown". Queries, that usually run in few ms, take over 1 second. All the queries logged in slow query log are also timestamped hh:mm:50-55. Which seems to be pretty much the same 1 minute cycle I measured manually just watching top.