mariaDB 10 on Qnap very slow stored views performance
I would like to ask for some help please. I have recently installed mariaDB 10.5.8 on my NAS QNAP TS-453D (qts v220.127.116.115). 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