The Ultimate Guide to High Availability with MariaDB
This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-16. For the latest, interactive version please visit https://mariadb.com/kb/.

mariaDB 10 on Qnap very slow stored views performance

Hello All

I would like to ask for some help please. I have recently installed mariaDB 10.5.8 on my NAS QNAP TS-453D (qts v5.0.1.2145). So far it is working as intended, but, there is one particular issue that I'm struggling to rectify.

The database has been moved from another mariaDB running inside VM on same NAS, using mysqldump export. Problem is with stored views performance. If I run already stored view on database inside vm it will take less than 1s to finish, but on the mariaDB directly on NAS it takes 69-70s everytime. This slow performance is same on MySQL Workbench, phpMyAdmin (running on NAS) and even directly via command line on NAS(1 row in set, 2 warnings (1 min 10.826 sec)). Like mentioned above exactly same view and data set will run in less than 1s in the VM. I have tried to increase some of the config values for buffers and read/write values, please see below:

[mysqld]
tmpdir = /share/CACHEDEV1_DATA/.mariadb10/tmp
#skip-networking
user=admin
skip-external-locking
socket = /var/run/mariadb10.sock
key_buffer_size = 16M
max_allowed_packet = 16M
table_open_cache = 64
sort_buffer_size = 2M
net_buffer_length = 16K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 128M
#default-storage-engine = MyISAM
default-storage-engine = InnoDB
pid-file = /var/lock/mariadb10.pid
log-error = /var/log/mariadb10/mariadb.err
skip-name-resolve

I have compared and matched the values between VM and NAS to see if that will help. I have restarted mariaDB10 service after changes for them to take effect. None of the change made any difference. Does anyone has any idea what could be the problem that normal queries run fine but views are super slow?

Thanks for any help

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.