0

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.

Improve this question
4
  • Enable slow query log with explain to see if its a query that does this. Maybe periodicly its using a choosing a bad plan and causing troubles for others in the mean time. As a poor mans profiler maybe cron to save show global status every minute and show the difference over a CPU spiked period compared to the period before it. Include the server config by editing your question. – danblack Jan 21 at 2:45
  • Added config. 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. Is it possible, that some selects could lock the table so this happens? – Firze Jan 21 at 7:33
  • +log-slow-verbosity=query_plan,explain. Look for query plans changing. Select are non-locking. – danblack Jan 21 at 8:13
  • At hh:mm:52, do SHOW FULL PROCESSLIST; delete the "Sleep" rows, and show it to us. I'll help you find the villain. And when you find it in the slowlog, I'll explain why it was so hard to spot. – Rick James Jan 30 at 4:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.