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

Details

Type: Bug
Status: Open
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.0.8, 10.0.19
Fix Version/s: 10.0
Component/s: Optimizer, Storage Engine - InnoDB
Labels:
None
Environment:
REHL6

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

Gliffy Diagrams

Attachments

Activity

Hide

Permalink

VAROQUI Stephane added a comment - 2015-05-28 19:07

I have a 100M data dump please send me instruction to send.

Show

VAROQUI Stephane added a comment - 2015-05-28 19:07 I have a 100M data dump please send me instruction to send. Tx

People

Assignee:

Sergei Petrunia

Reporter:

VAROQUI Stephane

Votes:

0 Vote for this issue

Watchers:

2 Start watching this issue

Dates

Created:

2015-05-28 18:18

Updated:

2015-05-28 19:07

Agile

View on Board