MariaDB cache going cold overnight

We are running a LAMP stack which includes MariaDB 10.5.15 on Centos 7. It's a dedicated server with 4 CPU's and 8GB RAM.

Never had a problem until the last few weeks, where I noticed slow queries taking 8-9 seconds in the slow query log.

These queries are against multiple INNODB databases, but always large tables (i.e. 10,000+ rows). They always occur between the hours of 6am and 8am.

The tables in question have indexes, and the same queries execute in less than a second ordinarily.

I found that by logging into the server at 8am on a Sunday and querying these large tables with simple select statements, they would take 8-9 seconds to execute. Then, for the rest of the day, even beyond midnight, any queries against that table would be fast.

6am - 8am would also be the time when things "come back to life" after a period of inactivity during the early hours.

It seems like there is some kind of cache that is going cold and needs to warm up, but I am not sure why this suddenly started happening after years of trouble free use. There is no huge process running on the server overnight that I know of, and the server is not under load when this happens.

We monitor the CPU load 24/7 and periodically check the number of connections to Apache and MariaDB which remain moderately low throughout the day. Typically there is around 3GB memory free excluding buffers and cache.

Edit

Embarrassingly, I discovered that the slow queries in question actually weren't using indexes and were doing a full table scan. An initial query can take several seconds, then for the rest of the day even the full table scan takes less than a second. I am assuming this is some kind of disk cache that is going cold overnight.

Although it might not seem unusual for a full table scan to have performance issues, it still seems odd that this suddenly became an issue out of the blue.

You seem to have ruled out a nightly dump and some slow process?

More debugging suggestions:

Lower the global setting of long_query_time and keep the slowlog turned on; you might catch the villain.

How often does your query run? What I am fishing for here is narrowing down the time when things get slow.

Check SHOW GLOBAL STATUS LIKE "Uptime" If that is less than 86400, then the MariaDB has restarted less than 24 hours ago. In this case, you should be able to pin the time nearly to the second. (But not identify the cause).

Show us the query, plus SHOW CREATE TABLE and EXPLAIN... We may be able to help you speed it up (eg, by a better index or a reformulation of the query), thereby eliminating the problem (though without solving the cause).