[MDEV-8246] Possible 3 times performance improvement for InnoDB update - Jira

XML

Word

Printable

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Won't Fix
Affects Version/s: 10.0.8, 10.0.19
Fix Version/s: 10.0.29
Component/s: Optimizer, Storage Engine - InnoDB
Labels:
None
Environment:
RHEL6

Description

Following the case on my OSX laptop with no traffic on 10.0.19 that reflect what our user
is observing in linux on an older release.

Full table scan insert into new table 2 to 3 times faster than equivalent full table update

The query we initially try to run looks like :

update NORMA_SMA.NORMA_PARC_SMA_10M p, NORMA_SMA.REF_PROD_F_SMA r

set p.PART_SOCT=case

                               when r.CO2<100 and p.PART_SOCT='PARTIC.' then 'ECO'

                               when r.cO2>=100 and r.cO2< 150 then 'MOYEN'

                               else 'MAX'

end

where p.cle_produit=r.cle_produit;

| Innodb_rows_read                              | 61302                        |

| Innodb_rows_read                              | 66186                        |

| Innodb_rows_read                              | 66226                        |

We now replace by materialization of the full query and fully recreate the table

use NORMA_SMA;

create or replace table NORMA_PARC_SMA_NEW like NORMA_PARC_SMA_10M;

insert into NORMA_PARC_SMA_NEW

select p.immat_siv, case

                when r.CO2<100 and p.PART_SOCT='PARTIC.' then 'ECO'

                when r.cO2>=100 and r.cO2< 150 then 'MOYEN'

                else 'MAX' end as PART_SOCT, p.cle_produit,id

from NORMA_PARC_SMA_10M p, REF_PROD_F_SMA r where p.cle_produit=r.cle_produit;

| Innodb_rows_read                              | 177906                       |

| Innodb_rows_read                              | 204158                       |

| Innodb_rows_read                              | 195762                       |

table definition is following

CREATE TABLE `NORMA_PARC_SMA_10M` (

  `IMMAT_SIV` varchar(128) NOT NULL DEFAULT '',

  `PART_SOCT` varchar(512) DEFAULT NULL,

  `cle_produit` int(10) unsigned DEFAULT NULL,

  `id` bigint(20) unsigned DEFAULT NULL,

  PRIMARY KEY (`IMMAT_SIV`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `REF_PROD_F_SMA` (

  `AN_NAISS_MOD` varchar(512) DEFAULT NULL,

  `CALCUL` varchar(512) DEFAULT NULL,

  `CARROS` varchar(512) DEFAULT NULL,

  `CARROS_UE` varchar(512) DEFAULT NULL,

  `CAT_ENVIRONN` varchar(512) DEFAULT NULL,

  `CLE_PRODUIT` int(10) unsigned NOT NULL,

  `CO2` int(11) DEFAULT NULL,

  `CODE_MOTEUR` varchar(512) DEFAULT NULL,

  `COMMENTAIRE` varchar(512) DEFAULT NULL,

  `CONS_120` double DEFAULT NULL,

  `CONS_90` double DEFAULT NULL,

  `CONS_EXURB` double DEFAULT NULL,

  `CONS_MIXTE` double DEFAULT NULL,

  `CONS_MOY` double DEFAULT NULL,

  `CONS_URB` double DEFAULT NULL,

  `CONS_VILLE` double DEFAULT NULL,

  `CSTR_VIN` varchar(512) DEFAULT NULL,

  `CYLINDREE` double DEFAULT NULL,

  `C_CAT_CNIT` varchar(512) DEFAULT NULL,

  `C_CAT_FIAT` varchar(512) DEFAULT NULL,

  `C_CAT_RNLT` varchar(512) DEFAULT NULL,

  `C_GENRE` varchar(512) DEFAULT NULL,

  `C_MARQUE` varchar(512) DEFAULT NULL,

  `C_MAR_CNIT` varchar(512) DEFAULT NULL,

  `C_MAR_EURO` varchar(512) DEFAULT NULL,

  `C_NAT_TYPE` varchar(512) DEFAULT NULL,

  `C_SGENRFNA` varchar(512) DEFAULT NULL,

  `C_SGVP_REN` varchar(512) DEFAULT NULL,

  `DATE_DEB_EFFET` varchar(512) DEFAULT NULL,

  `DATE_DEB_IMM` varchar(512) DEFAULT NULL,

  `DATE_DER_IMM` varchar(512) DEFAULT NULL,

  `DATE_DER_MAJ` varchar(512) DEFAULT NULL,

  `DATE_FIN_BT` varchar(512) DEFAULT NULL,

  `DATE_FIN_IMM` varchar(512) DEFAULT NULL,

  `DEPOLLUT` varchar(512) DEFAULT NULL,

  `DUREE_MOY_DETENTION` double DEFAULT NULL,

  `EFFECTIF_DETENTION` double DEFAULT NULL,

  `EMPAT` double DEFAULT NULL,

  `EMPREINTE_SOL` int(11) DEFAULT NULL,

  `ENERG` varchar(512) DEFAULT NULL,

  `FORCE_LITIGE` varchar(512) DEFAULT NULL,

  `GAMME_EURO` varchar(512) DEFAULT NULL,

  `GAMME_MAR` varchar(512) DEFAULT NULL,

  `GENRE_UE` varchar(512) DEFAULT NULL,

  `GENRE_V` varchar(512) DEFAULT NULL,

  `HAUTEUR` double DEFAULT NULL,

  `INDMOD_STT_VN` double DEFAULT NULL,

  `INDMOD_STT_VO` double DEFAULT NULL,

  `INDMRQ_STT_VN` double DEFAULT NULL,

  `INDMRQ_STT_VO` double DEFAULT NULL,

  `LARGEUR` int(11) DEFAULT NULL,

  `LONGUEUR` int(11) DEFAULT NULL,

  `MARQUE` varchar(512) DEFAULT NULL,

  `MARQUE_CARROS` varchar(512) DEFAULT NULL,

  `MARQUE_EURO` varchar(512) DEFAULT NULL,

  `MAR_FR_ETR` varchar(512) DEFAULT NULL,

  `MODELE` varchar(512) DEFAULT NULL,

  `MODELE_ETUDE` varchar(512) DEFAULT NULL,

  `MODELE_EURO` varchar(512) DEFAULT NULL,

  `MODE_INJ` varchar(512) DEFAULT NULL,

  `MODE_REFROID` varchar(512) DEFAULT NULL,

  `NB_CYLIND` int(11) DEFAULT NULL,

  `NB_DECIBELS` double DEFAULT NULL,

  `NB_ESSIEUX` int(11) DEFAULT NULL,

  `NB_PL_ASS` int(11) DEFAULT NULL,

  `NB_PORTES` int(11) DEFAULT NULL,

  `NB_SOUPAPES` int(11) DEFAULT NULL,

  `NB_TEMPS` varchar(512) DEFAULT NULL,

  `NB_VITESSES` int(11) DEFAULT NULL,

  `NB_VOLUMES` int(11) DEFAULT NULL,

  `OPT_CO2` varchar(512) DEFAULT NULL,

  `PAYS_ASS` varchar(512) DEFAULT NULL,

  `PAYS_TR` varchar(512) DEFAULT NULL,

  `POIDS_VIDE` int(11) DEFAULT NULL,

  `PROPULS` varchar(512) DEFAULT NULL,

  `PTR` double DEFAULT NULL,

  `PTR_EURO` double DEFAULT NULL,

  `PUIS_CH` int(11) DEFAULT NULL,

  `PUIS_FISC` int(11) DEFAULT NULL,

  `PUIS_KW` int(11) DEFAULT NULL,

  `RAP_PUIS_POIDS` double DEFAULT NULL,

  `REGM_DECIBELS` int(11) DEFAULT NULL,

  `REP_SPORT` varchar(512) DEFAULT NULL,

  `REP_VU_PSA` varchar(512) DEFAULT NULL,

  `SEG1_VU_06` varchar(512) DEFAULT NULL,

  `SEG2_VU_06` varchar(512) DEFAULT NULL,

  `SEGT_PSA` varchar(512) DEFAULT NULL,

  `SEGT_PSA_2006` varchar(512) DEFAULT NULL,

  `SEG_BMW` varchar(512) DEFAULT NULL,

  `SEG_GVF` varchar(512) DEFAULT NULL,

  `SEG_VGF` varchar(10) DEFAULT NULL,

  `SEG_MINI` varchar(512) DEFAULT NULL,

  `SEG_MITSUBISHI` varchar(512) DEFAULT NULL,

  `SEG_NISSAN` varchar(512) DEFAULT NULL,

  `SEG_NISSAN_EU` varchar(512) DEFAULT NULL,

  `SEG_REN_EU` varchar(512) DEFAULT NULL,

  `SEG_ROAD` varchar(512) DEFAULT NULL,

  `SEG_SP_PSA` varchar(512) DEFAULT NULL,

  `SEG_VU_06` varchar(512) DEFAULT NULL,

  `TMM_TECH` varchar(512) DEFAULT NULL,

  `TP_BOITE_V` varchar(512) DEFAULT NULL,

  `TRANSMISS` varchar(512) DEFAULT NULL,

  `TURBO_C` varchar(512) DEFAULT NULL,

  `TYPE` varchar(512) DEFAULT NULL,

  `TYPE_SUR8` varchar(512) DEFAULT NULL,

  `TYPE_TRAV` varchar(512) DEFAULT NULL,

  `TYPE_VAR_VERS` varchar(512) DEFAULT NULL,

  `TYPE_VIN` varchar(512) DEFAULT NULL,

  `UNIVERS` varchar(512) DEFAULT NULL,

  `VERSION` varchar(512) DEFAULT NULL,

  `VERS_CONSO` varchar(512) DEFAULT NULL,

  `VOIE_3` double DEFAULT NULL,

  `VOIE_AR` double DEFAULT NULL,

  `VOIE_AV` double DEFAULT NULL,

  `CARROSSERIE` varchar(512) DEFAULT NULL,

  `ENERGIE` varchar(512) DEFAULT NULL,

  `PROPULSION` varchar(512) DEFAULT NULL,

  `TP_BOITE_VIT` varchar(512) DEFAULT NULL,

  `GAMME_MARCHE` varchar(512) DEFAULT NULL,

  `GENRE_VEH` varchar(512) DEFAULT NULL,

  `TURBO_COMPR` varchar(512) DEFAULT NULL,

  `PAYS_TRAITE` varchar(512) DEFAULT NULL,

  PRIMARY KEY (`CLE_PRODUIT`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Table distribution is

          Name: NORMA_PARC_SMA_10M

         Engine: InnoDB

        Version: 10

     Row_format: Compact

           Rows: 10005945

 Avg_row_length: 39

    Data_length: 399491072

Max_data_length: 0

   Index_length: 0

      Data_free: 4194304

*************************** 2. row ***************************

           Name: REF_PROD_F_SMA

         Engine: InnoDB

        Version: 10

     Row_format: Compact

           Rows: 464467

 Avg_row_length: 544

    Data_length: 252674048

Max_data_length: 0

   Index_length: 0

      Data_free: 5242880

*************************** 3. row ***************************

           Name: norma_parc_sma_new

         Engine: InnoDB

        Version: 10

     Row_format: Compact

           Rows: 9824807

 Avg_row_length: 38

    Data_length: 374308864

And following innodb settings used all rest is default settings

performance_schema = 0

innodb_buffer_pool_instances = 1

innodb_flush_method = O_DIRECT

innodb_log_file_size = 1G

innodb_log_files_in_group=8

innodb_buffer_pool_size = 3G

innodb_additional_mem_pool_size = 16M

innodb_log_buffer_size = 512M

innodb_max_dirty_pages_pct = 30

innodb_file_per_table

innodb_flush_method = O_DIRECT

innodb_doublewrite = 1

innodb_flush_log_at_trx_commit = 0

Attachments

Activity

People

Assignee:: Oleksandr Byelkin

Reporter:: VAROQUI Stephane

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2015-05-28 18:18

Updated:: 2017-01-03 14:52

Resolved:: 2017-01-03 14:52

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.