Question 63610 in PhpMyAdmin Question posted in
The official documentation can be found here.

mariaDB 10.5.8 very slow stored view after moving to newer version – Phpmyadmin

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:
enter image description here
enter image description here

id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
‘1’ ‘PRIMARY’ ” ‘ALL’ NULL NULL NULL NULL ‘44803765561’ ‘0.00’ ‘100.00’ ‘100.00’ ‘Using where’
‘2’ ‘DERIVED’ ‘q1’ ‘ALL’ NULL NULL NULL NULL ‘211669’ ‘227012.00’ ‘100.00’ ‘0.00’ ‘Using where; Using temporary; Using filesort’
‘2’ ‘DERIVED’ ” ‘ALL’ ‘distinct_key’ NULL NULL NULL ‘211669’ NULL ‘100.00’ NULL ‘Using join buffer (flat
‘2’ ‘DERIVED’ ‘sic_report’ ‘eq_ref’ ‘PRIMARY’ ‘PRIMARY’ ‘4’ ‘.max(SOME_DB.sic_report.id)’ ‘1’ NULL ‘100.00’ NULL ‘Using where’
‘4’ ‘MATERIALIZED’ ‘sic_report’ ‘ALL’ NULL NULL NULL NULL ‘211669’ NULL ‘100.00’ NULL ‘Using temporary’

Backup explain output:
enter image description here
enter image description here

id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
‘1’ ‘PRIMARY’ ” ‘ALL’ NULL NULL NULL NULL ‘230068224’ ‘1.00’ ‘100.00’ ‘100.00’ ‘Using where’
‘2’ ‘DERIVED’ ‘q1’ ‘ALL’ NULL NULL NULL NULL ‘15168’ ‘15586.00’ ‘100.00’ ‘100.00’ ‘Using temporary; Using filesort’
‘2’ ‘DERIVED’ ‘sic_report’ ‘ALL’ ‘PRIMARY’ NULL NULL NULL ‘15168’ ‘15586.00’ ‘100.00’ ‘0.19’ ‘Using where; Using join buffer (flat
‘2’ ‘DERIVED’ ” ‘eq_ref’ ‘distinct_key’ ‘distinct_key’ ‘4’ ‘SOME_DB.sic_report.id’ ‘1’ ‘0.03’ ‘100.00’ ‘100.00’ ”
‘4’ ‘MATERIALIZED’ ‘sic_report’ ‘ALL’ NULL NULL NULL NULL ‘15168’ ‘15586.00’ ‘100.00’ ‘100.00’ ‘Using temporary’

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`

UPDATE2:

Main innodb_buffer:

‘innodb_buffer_pool_chunk_size’ ‘134217728’
‘innodb_buffer_pool_dump_at_shutdown’ ‘ON’
‘innodb_buffer_pool_dump_now’ ‘OFF’
‘innodb_buffer_pool_dump_pct’ ’25’
‘innodb_buffer_pool_filename’ ‘ib_buffer_pool’
‘innodb_buffer_pool_instances’ ‘1’
‘innodb_buffer_pool_load_abort’ ‘OFF’
‘innodb_buffer_pool_load_at_startup’ ‘ON’
‘innodb_buffer_pool_load_now’ ‘OFF’
‘innodb_buffer_pool_populate’ ‘OFF’
‘innodb_buffer_pool_size’ ‘805306368’

Backup innodb_buffer:

‘innodb_buffer_pool_chunk_size’ ‘134217728’
‘innodb_buffer_pool_dump_at_shutdown’ ‘ON’
‘innodb_buffer_pool_dump_now’ ‘OFF’
‘innodb_buffer_pool_dump_pct’ ’25’
‘innodb_buffer_pool_filename’ ‘ib_buffer_pool’
‘innodb_buffer_pool_instances’ ‘1’
‘innodb_buffer_pool_load_abort’ ‘OFF’
‘innodb_buffer_pool_load_at_startup’ ‘ON’
‘innodb_buffer_pool_load_now’ ‘OFF’
‘innodb_buffer_pool_size’ ‘1073741824’

Create Statement:

CREATE TABLE `sic_report` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date_time` timestamp NOT NULL DEFAULT current_timestamp(),
`lines_` text DEFAULT NULL,
`stations` text DEFAULT NULL,
`spol` text DEFAULT NULL,
`actual_pick` text DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
`delivery_rate` text DEFAULT NULL,
`src_order_pool` text DEFAULT NULL,
`src_order_pool_qty` text DEFAULT NULL,
`wms_order_pool` text DEFAULT NULL,
`wms_order_pool_qty` text DEFAULT NULL,
`avg_pick_time` text DEFAULT NULL,
`sort_ff_c` text DEFAULT NULL,
`sort_ff_dt` text DEFAULT NULL,
`sort_gf_c` text DEFAULT NULL,
`sort_gf_dt` text DEFAULT NULL,
`sort_check_gf` text DEFAULT NULL,
`sort_check_ff` text DEFAULT NULL,
`tk01_sort_occupation` text DEFAULT NULL,
`tk02_sort_occupation` text DEFAULT NULL,
`tk01_sort_reloops` text DEFAULT NULL,
`tk02_sort_reloops` text DEFAULT NULL,
`tk01_emp_occ` text DEFAULT NULL,
`tk01_emp_relop` text DEFAULT NULL,
`tk02_emp_occ` text DEFAULT NULL,
`tk02_emp_relop` text DEFAULT NULL,
`pick_order_lead_time` text DEFAULT NULL,
`pick_order_postp_count` text DEFAULT NULL,
`tk01_err_occ` text DEFAULT NULL,
`tk01_err_relop` text DEFAULT NULL,
`tk02_err_occ` text DEFAULT NULL,
`tk02_err_relop` text DEFAULT NULL,
`stations_tk01` text DEFAULT NULL,
`stations_tk02` text DEFAULT NULL,
`tk01_sf_sorter` text DEFAULT NULL,
`tk01_sf_prezone` text DEFAULT NULL,
`tk02_sf_sorter` text DEFAULT NULL,
`tk02_sf_prezone` text DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=329266 DEFAULT CHARSET=latin1;

Main DB:

TABLE_NAME ENGINE TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH INDEX_LENGTH
‘sic_report’ ‘InnoDB’ ‘15644’ ‘235’ ‘3686400’ ‘0’

Backup DB :

TABLE_NAME ENGINE TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH INDEX_LENGTH
‘sic_report’ ‘InnoDB’ ‘16266’ ‘226’ ‘3686400’ ‘0’
Tags:

2

Answers


  1. 0

    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.

    UPDATE after adding CREATE TABLE statement

    There are no indices other than the primary key. Given that you are grouping by and joining on date_time there should be an index –

    ALTER TABLE `SOME_DB`.`sic_report`
    ADD INDEX `idx_date_time` (`date_time`);
    Login or Signup to reply.
  2. 0 
    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search