MariaDB got much faster, and I can't find the cause?

Question

I have a concern about my MariaDB 10.4.12 database query execution time, which is getting much faster without any update to my database schema or data. While a speed-up is always welcome, I am concerned about the root cause of this speed-up, especially since I have not rolled out any changes in the last 24 hours. This specific query has sped up 60x overnight.

I have a NodeJS web application that filters a large dataset into "reporting" pages, which typically take 10-12 seconds to load. My main table has 3.5 million rows and the base query involves many joins, date comparisons, and text comparisons. There is room for fine-tuning the query, but it worked for what it was designed to do and I could live with 10 second load times. I noticed this morning, though, that my queries were executed in less than 1 second, without any recent changes on my part.

The most recent change to the application was pushed out five days ago, which affected the amount of data being pulled into this database. A separate application on the same server reaches out to a data set every 10 minutes and replicates these rows into the same database the "reporting" application communicates with. Up until this update, the query was collecting and inserting ~80,000 rows on average, taking about 8-10 seconds to fully replicate the data into this database. My change five days ago reduced the rows being inserted to ~20,000 on average.

Other clues:

• PHPMyAdmin still takes 10-12 seconds to run the query, while the MySQL command-line tool takes in less than 1 second
• The MariaDB temp directory was changed to a larger partition 7 days ago
• The query was tested to be slow (10-12 seconds) 24 hours ago
• The query is still slow on a pre-production server that runs the same application with an identical MySQL instance running (same schema and data)

My current running theory is that the ~80,000 inserts were not being executed in the time range being reported by NodeJS (8-10 seconds for the inserts), and they were instead waiting in the MariaDB temp directory until they could be fully written to the database. That would suggest that the database was constantly bogged down by these writes, and reducing the number to ~20k allowed the database to insert faster, allowing the select queries to run faster this morning.

Should I be concerned about this speed up? Could MariaDB have found a faster way to index my data? Am I going crazy?

Thank you.

Answer 1

Don't worry. This kind of thing can be caused by contention (multiple database clients using the database concurrently) and all sorts of other things.

(Cherish this moment. Performance usually goes the other direction.)

You can test for correctness to increase your confidence level. Check a few older and a few newer records to see if they still contain good data.

Or a full-table-scan query, something like this

    SELECT COUNT(*), AVG(some_number_column), MIN(some_text_column) FROM mytable

That will take a while but it will hit every row in the table.

You probably don't need to do this, but it's a way to double check (and tell your boss, "I double checked.)