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 May 8 '20 at 7:10
  • @Akina i've just added the screens from explain and it seems that PIUMENOVEN is used.. – Igor Mytyuk May 8 '20 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 May 8 '20 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 – Igor Mytyuk May 8 '20 at 7:23
  • Try to force index usage (but remember that this can both help and hinder). – Akina May 8 '20 at 7:26
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! – Igor Mytyuk May 18 '20 at 7:10
  • View my profile, Network profile for contact info and free downloadable Utility Scripts to also improve performance tuning. – Wilson Hauck May 18 '20 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 – Igor Mytyuk May 11 '20 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 – Igor Mytyuk May 11 '20 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 – Igor Mytyuk May 11 '20 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) – Igor Mytyuk May 11 '20 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 May 11 '20 at 14:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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