[MDEV-8246] Possible 3 times performance improvement for InnoDB update Created: 2015-05-28  Updated: 2017-01-03  Resolved: 2017-01-03

Status: Closed
Project: MariaDB Server
Component/s: Optimizer, Storage Engine - InnoDB
Affects Version/s: 10.0.8, 10.0.19
Fix Version/s: 10.0.29

Type: Bug Priority: Major
Reporter: VAROQUI Stephane Assignee: Oleksandr Byelkin
Resolution: Won't Fix Votes: 0
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



 Comments   
Comment by VAROQUI Stephane [ 2015-05-28 ]

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

Tx

Comment by Oleksandr Byelkin [ 2015-12-15 ]

I think the fact that UPDATE create temporary table with records it going to update with row-id (on innodb it is primary key) then goes by the IDs and update could take its tall (true only if many records updated).

Comment by Oleksandr Byelkin [ 2017-01-03 ]

If there is not arguments about the close, I'll close the bug.

Generated at Thu Feb 08 07:25:43 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.