I would like to ask for help as I'm struggling to understand this issue.

I have two databases main and backup. Both have same data and views. Main is inside VM on QNAP NAS (v10.5.5) and backup is on same NAS but installed natively (v10.5.8). I know this is bad idea but this is only a test setup I'm working on.

Issue I have is with stored views. On the VM they would return result in less then 1s, but directly on NAS it takes up to 70s for exactly the same data set and view. It doesn't matter what view I use they are all slow on the backup DB. I tried to adjust settings in the mariadb.conf on NAS to increase buffers but no change made any difference at all. Only one function reduced the wait by 10s, skip-name-resolve. I have tried to run it via phpMyAdmin, MySQL Workbench and terminal directly on the NAS, result is always the same ~70s or sometimes even slower. Again on main DB inside VM they take less than 1s. That fact makes me think this is a config issue more than views themself.

Here is my config file for mariaDB:

[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 output of SHOW VARIABLES; on both and the only big difference is that main DB uses rocksdb where backup is not.

Does anyone have any idea what is wrong or what I'm missing? Please let me know if you need any extra information.

Kind Regards

UPDATE:

Main explain output:

Backup explain output:

Example view:

CREATE 
ALGORITHM = UNDEFINED 
DEFINER = `root`@`localhost` 
SQL SECURITY DEFINER
VIEW `v_sic_stats` AS
SELECT 
    `q1`.`date_time` AS `date_time`,
    IFNULL(`q2`.`lines_`, 0) AS `lines_`,
    IFNULL(`q2`.`stations`, 0) AS `stations_avg`,
    IFNULL(ROUND(AVG(NULLIF(`q1`.`spol`, 0)), 1), 0) AS `spol_avg`,
    IFNULL(`q2`.`actual_pick`, 0) AS `actual_pick`,
    IFNULL(ROUND(NULLIF(`q2`.`lines_`, 0) / NULLIF(`q2`.`stations`, 0),
                    0),
            0) AS `delivery_rate`,
    IFNULL(ROUND(AVG(NULLIF(`q1`.`src_order_pool`, 0)), 0),
            0) AS `src_pool`,
    IFNULL(ROUND(AVG(NULLIF(`q1`.`wms_order_pool`, 0)), 0),
            0) AS `wms_pool`,
    IFNULL(ROUND(`q2`.`avg_pick_time` / `q2`.`lines_`, 1),
            0) AS `avg_pick_time`,
    IFNULL(`q2`.`sort_ff_c`, 0) AS `sort_ff_c`,
    IFNULL(`q2`.`sort_ff_dt`, '00:00:00') AS `sort_ff_dt`,
    IFNULL(`q2`.`sort_gf_c`, 0) AS `sort_gf_c`,
    IFNULL(`q2`.`sort_gf_dt`, '00:00:00') AS `sort_gf_dt`,
    IFNULL(MAX(CAST(`q1`.`sort_check_gf` AS DECIMAL (10 , 0 ))),
            0) AS `sort_check_gf`,
    IFNULL(MAX(CAST(`q1`.`sort_check_ff` AS DECIMAL (10 , 0 ))),
            0) AS `sort_check_ff`,
    IFNULL(`q2`.`tk01_sort_occupation`, 0) AS `tk01_sort_occupation`,
    IFNULL(`q2`.`tk02_sort_occupation`, 0) AS `tk02_sort_occupation`,
    IFNULL(`q2`.`tk01_sort_reloops`, 0) AS `tk01_sort_reloops`,
    IFNULL(`q2`.`tk02_sort_reloops`, 0) AS `tk02_sort_reloops`,
    IFNULL(`q2`.`tk01_emp_occ`, 0) AS `tk01_emp_occ`,
    IFNULL(`q2`.`tk01_emp_relop`, 0) AS `tk01_emp_relop`,
    IFNULL(`q2`.`tk02_emp_occ`, 0) AS `tk02_emp_occ`,
    IFNULL(`q2`.`tk02_emp_relop`, 0) AS `tk02_emp_relop`,
    IFNULL(`q2`.`pick_order_lead_time`, 0) AS `pick_order_lead_time`,
    IFNULL(ROUND(AVG(`q1`.`pick_order_postp_count`), 0),
            0) AS `pick_order_postp_count`,
    IFNULL(`q2`.`tk01_err_occ`, 0) AS `tk01_err_occ`,
    IFNULL(`q2`.`tk01_err_relop`, 0) AS `tk01_err_relop`,
    IFNULL(`q2`.`tk02_err_occ`, 0) AS `tk02_err_occ`,
    IFNULL(`q2`.`tk02_err_relop`, 0) AS `tk02_err_relop`,
    IFNULL(`q2`.`stations_tk01`, 0) AS `stations_tk01`,
    IFNULL(`q2`.`stations_tk02`, 0) AS `stations_tk02`,
    IFNULL(`q2`.`tk01_sf_sorter`, 0) AS `tk01_sf_sorter`,
    IFNULL(`q2`.`tk01_sf_prezone`, 0) AS `tk01_sf_prezone`,
    IFNULL(`q2`.`tk02_sf_sorter`, 0) AS `tk02_sf_sorter`,
    IFNULL(`q2`.`tk02_sf_prezone`, 0) AS `tk02_sf_prezone`
FROM
    (`SOME_DB`.`sic_report` `q1`
    JOIN (SELECT 
        `SOME_DB`.`sic_report`.`id` AS `id`,
            `SOME_DB`.`sic_report`.`date_time` AS `date_time`,
            `SOME_DB`.`sic_report`.`lines_` AS `lines_`,
            `SOME_DB`.`sic_report`.`stations` AS `stations`,
            `SOME_DB`.`sic_report`.`spol` AS `spol`,
            `SOME_DB`.`sic_report`.`actual_pick` AS `actual_pick`,
            `SOME_DB`.`sic_report`.`created_at` AS `created_at`,
            `SOME_DB`.`sic_report`.`delivery_rate` AS `delivery_rate`,
            `SOME_DB`.`sic_report`.`src_order_pool` AS `src_order_pool`,
            `SOME_DB`.`sic_report`.`src_order_pool_qty` AS `src_order_pool_qty`,
            `SOME_DB`.`sic_report`.`wms_order_pool` AS `wms_order_pool`,
            `SOME_DB`.`sic_report`.`wms_order_pool_qty` AS `wms_order_pool_qty`,
            `SOME_DB`.`sic_report`.`avg_pick_time` AS `avg_pick_time`,
            `SOME_DB`.`sic_report`.`sort_ff_c` AS `sort_ff_c`,
            `SOME_DB`.`sic_report`.`sort_ff_dt` AS `sort_ff_dt`,
            `SOME_DB`.`sic_report`.`sort_gf_c` AS `sort_gf_c`,
            `SOME_DB`.`sic_report`.`sort_gf_dt` AS `sort_gf_dt`,
            `SOME_DB`.`sic_report`.`sort_check_gf` AS `sort_check_gf`,
            `SOME_DB`.`sic_report`.`sort_check_ff` AS `sort_check_ff`,
            `SOME_DB`.`sic_report`.`tk01_sort_occupation` AS `tk01_sort_occupation`,
            `SOME_DB`.`sic_report`.`tk02_sort_occupation` AS `tk02_sort_occupation`,
            `SOME_DB`.`sic_report`.`tk01_sort_reloops` AS `tk01_sort_reloops`,
            `SOME_DB`.`sic_report`.`tk02_sort_reloops` AS `tk02_sort_reloops`,
            `SOME_DB`.`sic_report`.`tk01_emp_occ` AS `tk01_emp_occ`,
            `SOME_DB`.`sic_report`.`tk01_emp_relop` AS `tk01_emp_relop`,
            `SOME_DB`.`sic_report`.`tk02_emp_occ` AS `tk02_emp_occ`,
            `SOME_DB`.`sic_report`.`tk02_emp_relop` AS `tk02_emp_relop`,
            `SOME_DB`.`sic_report`.`pick_order_lead_time` AS `pick_order_lead_time`,
            `SOME_DB`.`sic_report`.`pick_order_postp_count` AS `pick_order_postp_count`,
            `SOME_DB`.`sic_report`.`tk01_err_occ` AS `tk01_err_occ`,
            `SOME_DB`.`sic_report`.`tk01_err_relop` AS `tk01_err_relop`,
            `SOME_DB`.`sic_report`.`tk02_err_occ` AS `tk02_err_occ`,
            `SOME_DB`.`sic_report`.`tk02_err_relop` AS `tk02_err_relop`,
            `SOME_DB`.`sic_report`.`stations_tk01` AS `stations_tk01`,
            `SOME_DB`.`sic_report`.`stations_tk02` AS `stations_tk02`,
            `SOME_DB`.`sic_report`.`tk01_sf_sorter` AS `tk01_sf_sorter`,
            `SOME_DB`.`sic_report`.`tk01_sf_prezone` AS `tk01_sf_prezone`,
            `SOME_DB`.`sic_report`.`tk02_sf_sorter` AS `tk02_sf_sorter`,
            `SOME_DB`.`sic_report`.`tk02_sf_prezone` AS `tk02_sf_prezone`
    FROM
        `SOME_DB`.`sic_report`
    WHERE
        `SOME_DB`.`sic_report`.`id` IN (SELECT 
                MAX(`SOME_DB`.`sic_report`.`id`)
            FROM
                `SOME_DB`.`sic_report`
            GROUP BY `SOME_DB`.`sic_report`.`date_time`)) `q2` ON (`q1`.`date_time` = `q2`.`date_time`))
WHERE
    `q1`.`date_time` > SYSDATE() - INTERVAL 2 DAY
GROUP BY `q1`.`date_time`

CodePudding user response：

It looks like your table statistics are wildly different so the index choices are different. You can update the stats for these tables with -

ANALYZE TABLE table_name_1, table_name_2;

Read more here

You may also want to look at the underlying query in more detail as it will almost certainly benefit from additional indexing.

CodePudding user response：

innodb_buffer_pool_size = 134217728

is terrible, especially for such large table like you have. Set that to about 70% of available RAM -- 12G for the 16GB machine but only 1200M for the smaller one. These should provide much better performance on both, but the smaller one is likely to be I/O-bound.

Also, avoid the construct IN ( SELECT ... ). Usually that can be changed to a JOIN.

How many rows in each table?

Please provide SHOW CREATE TABLE for each table; there may be some missing INDEXes.