Due to a recent infrastructure redo, I had to upgrade a stack that I did not update for around two years. I had MariaDB 10.3.4 and decided to try to upgrade it directly to 10.7.1.
The overall process was relatively painless (data volume wasn’t that big), but a few things caught me by surprise.
somehow, I lacked index on a pretty big table - I have no idea how it worked in the older version, but suddenly, after deploying the whole stack to new servers, all hell broke loose. Rails started throwing ActiveRecord connection pool-related exceptions. The entire service was down - after inspecting CPU usage on database container and taking a look into process list I realized what was going on - I have a ridiculous amount of running queries hanging there
so as you guessed from the above - MariaDB doesn’t kill long-running queries by default - you can adjust that by setting max_statement_time - it might be a good practice to set a reasonable timeout on that
Update 2021.12.19: I forgot to mention setting that setting max_statement_time affects your db backups - as mysqldump respects that value (which is not that obvious) - that might lead to incomplete sql dumps! See this stackexchange thread for references. You might want to create a dedicated user for mysqldump or workaround the problem by setting the global value of max_statement_time during your backup (and restoring it after it’s done) - both options are less than ideal. This feature request from 2019 lists some reasonable suggestions (that will be implemented one day hopefully).
when running MariaDB in docker, you can configure it on the CLI level. It’s not documented super clearly, but if you pass something like
--max_statement_time=10 --slow_query_log=ON --long_query_time=2as docker argument it should just workI never understood MySQL/MariaDB query planner (that’s why I became Postgres fanboy :P) - sometimes seems it need help, and index hints might come in handy in such cases (in case
ANALYZE TABLEwas no good)