Yet another MariaDB "help me tune the **** thing"

[lpallard]

Title says it all I am running a MariaDB/MySQL server on a Rocky Linux VM hosted on a Proxmox server. VM is dedicated to the DB server and has the following specs:

-4 Cores (2 cores x 2 sockets)
-32GB DDR3 RAM
-DB storage is on an older RAID0 pair of Hitachi 15k600 SAS hard drives (will be upgraded soon to PCIE SSD or simnilar)

I ran this DB server for many years with the same config but in the last year or so, its gotten progressively slower and slower. I tried playing with the variables in my.cnf and letting it run for weeks at the time only to run mysqltuner.pl and see what recommendations it gave me...

Today I decided to give it another try because refreshing a webpage or a program that relies on this DB server shouldn't take 40+ seconds. FYI, after a fresh start of MariaDB, everything is lightning fast so clearly something is misconfigured and is causing mariadb to bloat itself up...

Overall, I think its a matter of improper buffer sizing and innodb pool size VS available RAM.... Here's the mysqltuner output:

perl mysqltuner.pl
  >>  MySQLTuner 2.0.1
         * Jean-Marie Renouard <jmrenouard@gmail.com>
         * Major Hayden <major@mhtx.net>
  >>  Bug reports, feature requests, and downloads at http://mysqltuner.pl/
  >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: [OK] Currently
running supported MySQL version 10.3.35-MariaDB-log
[OK] Operating on 64-bit architecture

-------- Log file Recommendations
------------------------------------------------------------------
[OK] Log file /var/log/mariadb/mariadb.log exists
[--] Log file: /var/log/mariadb/mariadb.log (263B)
[OK] Log file /var/log/mariadb/mariadb.log is not empty
[OK] Log file /var/log/mariadb/mariadb.log is smaller than 32 Mb
[OK] Log file /var/log/mariadb/mariadb.log is readable.
[!!] /var/log/mariadb/mariadb.log contains 2 warning(s).
[OK] /var/log/mariadb/mariadb.log doesn't contain any error.
[--] 0 start(s) detected in /var/log/mariadb/mariadb.log
[--] 0 shutdown(s) detected in /var/log/mariadb/mariadb.log

-------- Storage Engine Statistics
-----------------------------------------------------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MEMORY
+MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in InnoDB tables: 748.4M (Tables: 761)
[--] Data in MyISAM tables: 10.2M (Tables: 44)
[OK] Total fragmented tables: 0

-------- Analysis Performance Metrics
--------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.

-------- Views Metrics
-----------------------------------------------------------------------------

-------- Triggers Metrics
--------------------------------------------------------------------------

-------- Routines Metrics
--------------------------------------------------------------------------

-------- Security Recommendations
------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] There are 620 basic passwords in the list.

-------- CVE Security Recommendations
--------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION

-------- Performance Metrics
-----------------------------------------------------------------------
[--] Up for: 9d 2h 55m 30s (34M q [43.387 qps], 390K conn, TX: 19G, RX: 3G)
[--] Reads / Writes: 80% / 20%
[--] Binary logging is disabled
[--] Physical Memory     : 31.4G
[--] Max MySQL memory    : 43.2G
[--] Other process memory: 0B
[--] Total buffers: 24.9G global + 186.2M per thread (100 max threads)
[--] P_S Max memory usage: 95M
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 29.5G (94.11% of installed RAM)
[!!] Maximum possible memory usage: 43.2G (137.55% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/34M)
[OK] Highest usage of available connections: 25% (25/100)
[OK] Aborted connections: 0.00%  (1/390706)
[!!] Name resolution is active: a reverse name resolution is made for
each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 18.2% (4M cached / 24M selects)
[!!] Query cache prunes per day: 10993
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1M sorts)
[!!] Joins performed without indexes: 12889
[OK] Temporary tables created on disk: 5% (54K on disk / 1M total)
[OK] Thread cache hit rate: 99% (187 created / 390K connections)
[OK] Table cache hit rate: 99% (21M hits / 21M requests)
[!!] table_definition_cache (400) is less than number of tables (968)
[OK] Open file limit used: 0% (148/128K)
[OK] Table locks acquired immediately: 100% (5K immediate / 5K locks)

-------- Performance schema
------------------------------------------------------------------------
[--] Performance_schema is activated.
[--] Memory used by P_S: 95.1M
[--] Sys schema isn't installed.

-------- ThreadPool Metrics
------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics
----------------------------------------------------------------------------
[!!] Key buffer used: 18.3% (23.4M used / 128.0M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/610.0K
[OK] Read Key buffer hit rate: 97.4% (21K cached / 545 reads)
[!!] Write Key buffer hit rate: 49.3% (408 cached / 201 writes)

-------- InnoDB Metrics
----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 24.0G/748.4M
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 2.0G *
3/24.0G should be equal to 25%
[!!] InnoDB buffer pool instances: 8
[--] Number of InnoDB Buffer Pool Chunk: 192 for 8 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with
Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 100.00% (5511951044 hits/
5512010134 total)
[!!] InnoDB Write Log efficiency: 88.47% (2723033 hits/ 3077843 total)
[OK] InnoDB log waits: 0.00% (0 waits / 354810 writes)

-------- Aria Metrics
------------------------------------------------------------------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/0B
[OK] Aria pagecache hit rate: 98.9% (4M cached / 54K reads)

-------- TokuDB Metrics
----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics
----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- Galera Metrics
----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics
-----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: MIXED
[--] XA support enabled: ON
[--] Semi synchronous replication Master: OFF
[--] Semi synchronous replication Slave: OFF
[--] This is a standalone server

-------- Recommendations
---------------------------------------------------------------------------
General recommendations:
     Check warning line(s) in /var/log/mariadb/mariadb.log file
     Reduce your overall MySQL memory footprint for system stability
     Dedicate this server to your database for highest performance.
     Configure your accounts with ip or subnets only, then update your
configuration with skip-name-resolve=1
     Increasing the query_cache size over 128M may reduce performance
     We will suggest raising the 'join_buffer_size' until JOINs not
using indexes are found.
              See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
              (specially the conclusions at the bottom of the page).
     Consider installing Sys schema from
https://github.com/mysql/mysql-sys for MySQL
Variables to adjust:
   *** MySQL's maximum memory usage is dangerously high ***
   *** Add RAM before increasing MySQL buffer variables ***
     skip-name-resolve=1
     query_cache_size (=0)
     query_cache_type (=0)
     query_cache_limit (> 12M, or use smaller result sets)
     query_cache_size (> 128M) [see warning above]
     join_buffer_size (> 16.0M, or always use indexes with JOINs)
     table_definition_cache(400) > 968 or -1 (autosizing if supported)
     key_buffer_size (~ 24M)
     innodb_buffer_pool_instances(=24)

free -m
               total        used        free      shared  buff/cache 
available
Mem:          32146        4260       24489          19        3397   
    27455
Swap:          1023           0        1023

I would be very grateful is someone could help me get some sort of performance out of this....
Thanks!

 

[lpallard]

I rarely bump one of my own threads but since I got no replies yet (perhaps because the question is too specific i.e. I should post on a DB centric forum), I feel the need to ask about storage. As I mentioned in my previous post, I need to upgrade the current SAS-based storage to a SSD model to improve performance, reduce noise and heat.

Motherboard is H8DCL-iF and therefore limited to PCI-E 2.0 x8.

1. Would a PCI-E SSD provide significant performance improvement over SATA SDD for this kind of use case?
2. What would you recommend? I imagine anything around 100GB would be plenty enough. Reliability and data integrity are paramount to me.

 

[unwind-protect]

I rarely bump one of my own threads but since I got no replies yet (perhaps because the question is too specific i.e. I should post on a DB centric forum), I feel the need to ask about storage. As I mentioned in my previous post, I need to upgrade the current SAS-based storage to a SSD model to improve performance, reduce noise and heat.

Motherboard is H8DCL-iF and therefore limited to PCI-E 2.0 x8.

1. Would a PCI-E SSD provide significant performance improvement over SATA SDD for this kind of use case?
2. What would you recommend? I imagine anything around 100GB would be plenty enough. Reliability and data integrity are paramount to me.

I estimate that you won't be able to feel much of a difference between SATA SSDs and NVMe SSDs, assuming they have similar IOps. The database just doesn't do large linear transfers often enough.

I would simply replace your existing drives with quality SATA or SAS SSDs and concentrate on other things.

 

[amalurk]

What is in the slow query log?
Do you have caches set so ridiculously high that memory for MariaDB spills over into slow swap space on disk?
Is your version of MariaDB up to date?
Could one of your disks be dying?

 

[lpallard]

What is in the slow query log?

slow query log is active, and only contains hundreds of lines like these: (probably only due to mariadb service startup):

/usr/libexec/mysqld, Version: 10.3.35-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time            Id Command    Argument
/usr/libexec/mysqld, Version: 10.3.35-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time            Id Command    Argument
/usr/libexec/mysqld, Version: 10.3.35-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time            Id Command    Argument
/usr/libexec/mysqld, Version: 10.3.35-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time            Id Command    Argument
/usr/libexec/mysqld, Version: 10.3.35-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time            Id Command    Argument
/usr/libexec/mysqld, Version: 10.3.35-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time            Id Command    Argument
/usr/libexec/mysqld, Version: 10.3.35-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time            Id Command    Argument
/usr/libexec/mysqld, Version: 10.3.35-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time            Id Command    Argument
/usr/libexec/mysqld, Version: 10.3.35-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time            Id Command    Argument
/usr/libexec/mysqld, Version: 10.3.35-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time            Id Command    Argument
/usr/libexec/mysqld, Version: 10.3.35-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time            Id Command    Argument
/usr/libexec/mysqld, Version: 10.3.35-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time            Id Command    Argument
/usr/libexec/mysqld, Version: 10.3.35-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time            Id Command    Argument
/usr/libexec/mysqld, Version: 10.3.35-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time            Id Command    Argument
/usr/libexec/mysqld, Version: 10.3.35-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time            Id Command    Argument
/usr/libexec/mysqld, Version: 10.3.35-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time            Id Command    Argument
/usr/libexec/mysqld, Version: 10.3.35-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time            Id Command    Argument
/usr/libexec/mysqld, Version: 10.3.35-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time            Id Command    Argument
/usr/libexec/mysqld, Version: 10.3.35-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time            Id Command    Argument
/usr/libexec/mysqld, Version: 10.3.35-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time            Id Command    Argument
/usr/libexec/mysqld, Version: 10.3.35-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time            Id Command    Argument
/usr/libexec/mysqld, Version: 10.3.35-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time            Id Command    Argument
/usr/libexec/mysqld, Version: 10.3.35-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time            Id Command    Argument
/usr/libexec/mysqld, Version: 10.3.35-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time            Id Command    Argument
/usr/libexec/mysqld, Version: 10.3.35-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time            Id Command    Argument
/usr/libexec/mysqld, Version: 10.3.35-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time            Id Command    Argument
/usr/libexec/mysqld, Version: 10.3.35-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time            Id Command    Argument
/usr/libexec/mysqld, Version: 10.3.35-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time            Id Command    Argument
/usr/libexec/mysqld, Version: 10.3.35-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time            Id Command    Argument
/usr/libexec/mysqld, Version: 10.3.35-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time            Id Command    Argument
/usr/libexec/mysqld, Version: 10.3.35-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time            Id Command    Argument
/usr/libexec/mysqld, Version: 10.3.35-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time            Id Command    Argument
/usr/libexec/mysqld, Version: 10.3.35-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time            Id Command    Argument
/usr/libexec/mysqld, Version: 10.3.35-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time            Id Command    Argument
/usr/libexec/mysqld, Version: 10.3.35-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time            Id Command    Argument
/usr/libexec/mysqld, Version: 10.3.35-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time            Id Command    Argument
/usr/libexec/mysqld, Version: 10.3.35-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time            Id Command    Argument
/usr/libexec/mysqld, Version: 10.3.35-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time            Id Command    Argument
/usr/libexec/mysqld, Version: 10.3.35-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time            Id Command    Argument
/usr/libexec/mysqld, Version: 10.3.35-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time            Id Command    Argument
/usr/libexec/mysqld, Version: 10.3.35-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock

Do you have caches set so ridiculously high that memory for MariaDB spills over into slow swap space on disk?

I dont think so but again I'm no expert (quite the opposite) so perhaps a combination of settings or parameters is causing memory usage to balloon out of control... After all these messages from mysqltuner caught my eye:

[!!] Maximum reached memory usage: 29.5G (94.11% of installed RAM)
[!!] Maximum possible memory usage: 43.2G (137.55% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory

Here's "my.cnf" just in case one of you spot something BAD...

[client]
port=3306
socket=/var/lib/mysql/mysql.sock

# GENERAL PROGRAM OPTIONS
[mysqld]
collation-server=utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server=utf8
port=3306
socket=/var/lib/mysql/mysql.sock
pid-file=/var/run/mariadb/mariadb.pid
log-error=/var/log/mariadb/mariadb.log
datadir=/mnt/sql-databases
user=mysql
symbolic-links=0
back_log = 50
performance_schema = ON

# NETWORKING OPTIONS
max_connections=100
max_connect_errors=10
table_open_cache=8000
max_allowed_packet=128M

binlog_cache_size=1M
max_heap_table_size=512M
read_buffer_size=2M
read_rnd_buffer_size=24M
sort_buffer_size=16M

join_buffer_size=16M
thread_cache_size=8

host_cache_size=100

#Modified these 2 lines
query_cache_size=128M
query_cache_limit=12M

ft_min_word_len=4
default-storage-engine=INNODB
thread_stack=192K
transaction_isolation=REPEATABLE-READ
tmp_table_size=512M
group_concat_max_len=8192
#log-bin=mysql-bin
max_binlog_size=1073741824
#expire_logs_days=1
binlog_format=mixed
log_warnings
slow_query_log
long_query_time=5
key_buffer_size=128M
bulk_insert_buffer_size=64M
myisam_sort_buffer_size=128M
myisam_max_sort_file_size=10G
myisam_repair_threads=1
myisam_recover

# INNODB OPTIONS
innodb_buffer_pool_size=24G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_thread_concurrency=0
innodb_flush_log_at_trx_commit=0
innodb_log_buffer_size=32M
innodb_log_file_size=2G
innodb_log_files_in_group=3
innodb_max_dirty_pages_pct=90
innodb_lock_wait_timeout=120
innodb_file_per_table
innodb_flush_method=O_DIRECT
#innodb_additional_mem_pool_size=16M

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size=256M
sort_buffer_size=512M
read_buffer=8M
write_buffer=8M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
open-files-limit=8192

Is your version of MariaDB up to date?

Yes

Could one of your disks be dying?

Not AFAIK. The SAS RAID controller reports drives OK, so are they (via Smart). No errors in dmesg or anywhere else indicating a faulty drive or a hardware malfunction...

 

[lpallard]

I would simply replace your existing drives with quality SATA or SAS SSDs and concentrate on other things.

I like the idea of mirroring the OS and VM storage which brings me to the following question: Would you get rid of the IBM M5016 SAS controller and simply go for two SATA SSD's (like Intel S3700) with RAID1 via ZFS?

That would allow me to get away from the aging SAS drives and get better performance from SSD's while having mirroring for the PS and the VM's. The DB storage would then be on the same physicasl drives (not sure though how DB performance and SSD longevity would be affected by having ZFS on top of that...)

 

[amalurk]

Have you run mysqlcheck to see if you have corrupted databases? Maybe try 1/4 of the innodb_buffer_pool_size=24G to to see if it makes any difference.

Could you be running custom code or buggy app connecting to the database that might not be closing any connections and so constantly spawning new ones on each webpage or app load.... that could be reason why a reboot fixes for awhile until it bogs down again.

 

[lpallard]

No corrupt DB's (checked with mysqlcheck).

I changed a bunch of variables according to your recommendation to reduce the innodb buffer pool to something more reasonable. I believe I had set a few variables in a way that they are too large which may impede performance. Will let the DB server run for a few days and re-run the mysqltuner.pl and see if things are better.

 

[T_Minus]

Figure out if you need innodb and myisam tables, if not then standardize on innodb and clean up the config.
I'd also personally add timeouts to the config so you can recoup resource\connection slots quicker.

A newer CPU and NVME will be a HUGE performance upgrade over that really slow CPU and HDD. But I retired 15K spinners a year ago too, on a Sandy-bridge E3 and it was still running dozens of databases and websites just fine, usually 20GB \ 32GB used by the DB.
Note- I didn't say "new" just newer like E3 v3 would be 2x passmark single core and nearly multi-core over the highest end 4000 series opteron.
If you want more ram go with E3 v5\6 or more ram and pcie go E5 v3. These are all very cheap upgrade options that will have big performance jump.
If you want it to be even more snappy go to a 5600x AMD. More costly, but very fast CPU and RAM.

I haven't used it yet for this but I've hard gpt4 is great at helping optimize queries, so you could feed schema in the prompt and ask how to optimize schema in case you need to tweak things there, and then also share the slow queries and ask how to improve them based on the schema.

 

[oldpenguin]

Find which query is taking that web app seconds to run, then manually run it with EXPLAIN in front of it, inside mysql client. Might simply be a case of missing indexes and dataset having grown.

You could also do a complete mysql dump of everything, purge mariadb and its configs, install percona, configure and import things back. And keep everything as much as possible in innodb engine.