0

I've got a new server so i had to configure MySQL on it, the old server had installed MySQL 5.0.24 so i've done backup of my DBs and restored them on newly installed MySQL 8.0.20 on the new server..

First of all i've done some simple SELECT queryes to test the speed of old and new server and till here both were almost fast the same..

When the queryes become more complex so with WHERE and GROUP BY (test done on a MyISAM table), the old server was fetching almost 13000 rows from almost 3m table in 3s while the new server with new MySQL was fetching the same rows in 21s so i started to do some test like changing the table from MyISAM to InnoDB in the new server and after that and after setting innodb_buffer_pool_size to 32G and i've got a fetch time of 9s a better result than 21s but anyway three times slower than the old MySQL 5.0.24...

The query i'm testing is the following (the WHERE for NPV_SC and NCASSA_SC in this case select all rows):

SELECT 
    DESCRIZIONE_SC AS DESCRIZIONE,
    COUNT(DESCRIZIONE_SC) AS QTA,
    SUM(IMPORTO_SC) AS IMPORTO
FROM
    scontrini
WHERE
    (NPV_SC , NCASSA_SC) IN ((1 , 1) , (1 , 2),
        (1 , 2),
        (2 , 1),
        (2 , 2),
        (3 , 1),
        (4 , 1),
        (4 , 2),
        (5 , 1),
        (6 , 1),
        (6 , 2),
        (7 , 1),
        (10 , 1),
        (11 , 1),
        (12 , 1),
        (13 , 1),
        (16 , 1),
        (23 , 1),
        (24 , 1),
        (24 , 2),
        (108 , 1))
        AND DATA_SC BETWEEN '2019-11-01' AND '2020-05-07'
GROUP BY DESCRIZIONE_SC
ORDER BY IMPORTO_SC DESC;

So the query above equals to:

SELECT 
    DESCRIZIONE_SC AS DESCRIZIONE,
    COUNT(DESCRIZIONE_SC) AS QTA,
    SUM(IMPORTO_SC) AS IMPORTO
FROM
    scontrini
WHERE DATA_SC BETWEEN '2019-11-01' AND '2020-05-07'
GROUP BY DESCRIZIONE_SC
ORDER BY IMPORTO_SC DESC;

Which still has same execution time

UPDATED: the above queries has changed after suggestion in comments on how to set the WHERE but anyway the fetch time is 5 seconds VS 3/2.80 seconds of old server with MySQL 5.0.24 (so the fetch time by optimizing the WHERE changed from 9s to 5 but still "far" from the old MySQL..)

The index are on NPV_SC, NCASSA_SC, DATA_SC, DESCRIZIONE_SC and IMPORTO_SC

Here is the create statment (without insignificant columns that was like other 10)

CREATE TABLE `scontrini` (
  `NPV_SC` int NOT NULL DEFAULT '0',
  `NCASSA_SC` int NOT NULL DEFAULT '0',
  `TIPODOC_SC` varchar(250) NOT NULL DEFAULT '',
  `DATA_SC` date NOT NULL DEFAULT '2001-01-01',
  `AZZ_SC` varchar(10) NOT NULL DEFAULT '0000',
  `NSC_SC` int NOT NULL DEFAULT '0',
  `ID_SC` int NOT NULL DEFAULT '0',
  `DESCRIZIONE_SC` varchar(250) DEFAULT '',
  `IMPORTO_SC` float(10,3) DEFAULT '0.000',
  PRIMARY KEY (`NPV_SC`,`NCASSA_SC`,`TIPODOC_SC`,`DATA_SC`,`AZZ_SC`,`NSC_SC`,`ID_SC`),
  KEY `PIUMENOVEN` (`NPV_SC`,`NCASSA_SC`,`DATA_SC`),
  KEY `REP` (`REPARTO_SC`),
  KEY `RC` (`RC_SC`),
  KEY `MENU` (`MENU_SC`),
  KEY `idx_scontrini_IMPORTO_SC` (`IMPORTO_SC`),
  FULLTEXT KEY `idx_scontrini_DESCRIZIONE_SC` (`DESCRIZIONE_SC`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Here is the explain

+----+-------------+-----------+------------+------+-------------------------------------------------+------+---------+------+---------+----------+----------------------------------------------+
| id | select_type | table     | partitions | type | possible_keys                                   | key  | key_len | ref  | rows    | filtered | Extra                                        |
+----+-------------+-----------+------------+------+-------------------------------------------------+------+---------+------+---------+----------+----------------------------------------------+
| 1  | SIMPLE      | scontrini | NULL       | ALL  | PRIMARY,PIUMENOVEN,idx_scontrini_DESCRIZIONE_SC | NULL | NULL    | NULL | 2483404 | 11.11    | Using where; Using temporary; Using filesort |
+----+-------------+-----------+------------+------+-------------------------------------------------+------+---------+------+---------+----------+----------------------------------------------+

enter image description here

The server has 64GB Ram, 1.5TB os SSD and 10x Intel(R) Xeon(R) CPU E5-2630 @ 2.20GHz

Here is my my.ini hosted on github

16
  • Replace your OR bunch with WHERE (NPV_SC, NCASSA_SC) IN ((1,1), (1,2), (2,1), ... (108,1)). Do not use quoting for literals in integer conditions - this converts them into strings. Ensure that the index PIUMENOVEN is used (does this query selects less than 5% of table records?).
    – Akina
    Commented May 8, 2020 at 7:10
  • @Akina i've just added the screens from explain and it seems that PIUMENOVEN is used.. Commented May 8, 2020 at 7:11
  • i've just added the screens from explain This is NOT EXPLAIN - it must be table-formatted text. Do not use visual tools, get EXPLAIN using CLI. it seems that PIUMENOVEN is used I see fullscan and do not see index usage.
    – Akina
    Commented May 8, 2020 at 7:15
  • @Akina added the explain in table-format, but actually if i check "Query Stats" it's saying that Index Usadge: No Index Used.. anyway by trying the where without all OR the fetch time become from 9s to 6s so still twice slower than MySQL 5.0.24 Commented May 8, 2020 at 7:23
  • Try to force index usage (but remember that this can both help and hinder).
    – Akina
    Commented May 8, 2020 at 7:26

2 Answers 2

1

Igor, you may find this workaround improves your speed on your MySQL 8.0.20 instance

SET GLOBAL internal_tmp_mem_storage_engine=MEMORY;

Refer to dba.stackexchange.com Question 267143 and look for Shane Bester's mention with the bug report. You will discover this was an error in 8.0.20, that will be corrected when 8.0.21 becomes available. Make this change to your my.ini as well to be in place after stop/start of your 8.0.20 instance.

2
  • 1
    yea damn even after optimizing the query as suggested by Rick James that was taking (2s to execute the query) by setting internal_tmp_mem_storage_engine=MEMORY it take now 0.02s. thank you! Commented May 18, 2020 at 7:10
  • View my profile, Network profile for contact info and free downloadable Utility Scripts to also improve performance tuning. Commented May 18, 2020 at 10:16
1

Add both of these; see which one it likes:

INDEX(data_sc, descrizione_sc, importo_sc)
INDEX(descrizione_sc, data_sc, importo_sc)

You provided EXPLAIN for one server; was the other one identical?

(the WHERE for NPV_SC and NCASSA_SC in this case select all rows):

All rows? Useless. Why even discuss NPV_SC, NCASSA_SC if you are not filtering on them?

WHERE (NPV_SC , NCASSA_SC) IN ((1 , 1) , (1 , 2), ... )

Before 5.7, "row constructors" were not optimized at all. Afterward, it does some optimization; maybe that misdirects the optimizer. In particular, the Optimizer will not discover that that list is the entire table.

tmp_table size and max_heap_table_size = 4G

Dangerously high. Don't go over about 1% of RAM.

The change from MyISAM to InnoDB -- this will increase the disk footprint by between 2x and 3x (usually).

Did you turn off the "query cache" and run each timing test twice?

What does "3/2.80" mean?

6
  • By checking the explain on the old server, it wasn't using at all the index 'PIUMENOVEN' the possible keys is just null, 3/2.80 means that on fresh query execute it takes 3 seconds and reexecuted 2.80 seconds. i'd now try with indexes you suggested, by changing tmp_table and by turning off query cache Commented May 11, 2020 at 6:52
  • And the NPV_SC and NCASSA_SC has been discussed as it's a "standart" for that query, it's like a filter of which "shop and it's operator" get the data, the user on front end can check like shop 1 and it's operator 1 and 2 and by default all shops are selected Commented May 11, 2020 at 6:55
  • After trying the two index you suggested, the query got the 1st one and by testing it 1 by 1 the 1st was faster, i've added to the index the NPV and NCASSA after importo and hell yeah now the query execution time is 1.92 seconds Commented May 11, 2020 at 7:14
  • but at this point how the hell could MySQL 5.0.24 that wasn't using any Index could be that fast versus MySQL 8? (i'm talking about when i've just imported the db so the table was MyISAM on both servers but on the old one the query execute time was 3 seconds and on MySQL 8 of 31 seconds) Commented May 11, 2020 at 7:16
  • 1
    @IgorMytyuk - INDEX usage may not have changed between 5.0 and 8.0 for this use case. InnoDB has had a lot of development since 5.0, but MyISAM has had zero development.
    – Rick James
    Commented May 11, 2020 at 14:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.