Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10046

InnoDB Range Optimizer Regression

    XMLWordPrintable

Details

    • 10.2.1-2

    Description

      Hello,

      After a dump restore from MySQL 5.5.38 to 10.1.13 .

      Response time of following query increase 5x to 0.5s. The CPU time is spend in step statistics affecting the Explain response time as well

      the global CPU response time of a the specific page is display before and after the migration in the attached graph

      None of the follow action works

      • Disable extended_keys
      • Downgrade 10.1.12
      • set global innodb_stats_persistence_sample_pages=8
      • set global innodb_persistente=OFF
      • innodb_persistente=OFF in cnf restart
      • simplified the query SELECT single job_id

      What helps is

      • Removing the condition on diffusion_id get back to 0.1s
      • Downgrade to MySQL 5.5.38 execution time is 0.1 with around same settings for innodb and restore dump

      MariaDB [catchup]> desc replay_job;
      +----------------------------+---------------+------+-----+---------+----------------+
      | Field                      | Type          | Null | Key | Default | Extra          |
      +----------------------------+---------------+------+-----+---------+----------------+
      | job_id                     | int(11)       | NO   | PRI | NULL    | auto_increment |
      | job_key                    | varchar(32)   | NO   | MUL | NULL    |                |
      | created_at                 | datetime      | NO   | MUL | NULL    |                |
      | updated_at                 | datetime      | YES  | MUL | NULL    |                |
      | diffusion_id               | int(11)       | YES  | MUL | NULL    |                |
      | type                       | varchar(32)   | YES  |     | NULL    |                |
      | top_start                  | datetime      | YES  |     | NULL    |                |
      | top_end                    | datetime      | YES  |     | NULL    |                |
      | frame_start                | int(11)       | YES  |     | NULL    |                |
      | frame_end                  | int(11)       | YES  |     | NULL    |                |
      | locked_at                  | datetime      | YES  |     | NULL    |                |
      | locked_for                 | varchar(64)   | YES  |     | NULL    |                |
      | job_done                   | datetime      | YES  |     | NULL    |                |
      | job_stop                   | datetime      | YES  |     | NULL    |                |
      | prepare_done               | datetime      | YES  |     | NULL    |                |
      | prepare_ingest_done        | datetime      | YES  |     | NULL    |                |
      | prepare_ingest_complete    | datetime      | YES  |     | NULL    |                |
      | droits_data                | text          | YES  |     | NULL    |                |
      | droits_web                 | tinyint(1)    | YES  |     | NULL    |                |
      | droits_fai                 | tinyint(1)    | YES  |     | NULL    |                |
      | volonte_startover          | tinyint(1)    | YES  |     | NULL    |                |
      | volonte_anevia             | tinyint(1)    | YES  |     | NULL    |                |
      | volonte_decoupe            | tinyint(1)    | YES  |     | NULL    |                |
      | volonte_web                | tinyint(1)    | YES  |     | NULL    |                |
      | volonte_fai                | tinyint(1)    | YES  |     | NULL    |                |
      | volonte_details_fai        | text          | YES  |     | NULL    |                |
      | anevia_id                  | varchar(32)   | YES  |     | NULL    |                |
      | anevia_creation_done       | datetime      | YES  | MUL | NULL    |                |
      | anevia_offline_done        | datetime      | YES  | MUL | NULL    |                |
      | anevia_delete_done         | datetime      | YES  |     | NULL    |                |
      | ohe_decoupe_done           | datetime      | YES  |     | NULL    |                |
      | ohe_decoupe_id             | varchar(32)   | YES  |     | NULL    |                |
      | ohe_decoupe_id1            | varchar(16)   | YES  |     | NULL    |                |
      | ohe_decoupe_id2            | varchar(16)   | YES  |     | NULL    |                |
      | ohe_decoupe_started        | datetime      | YES  |     | NULL    |                |
      | ohe_decoupe_started1       | datetime      | YES  |     | NULL    |                |
      | ohe_decoupe_started2       | datetime      | YES  |     | NULL    |                |
      | ohe_decoupe_host           | varchar(32)   | YES  |     | NULL    |                |
      | ohe_decoupe_complete       | datetime      | YES  |     | NULL    |                |
      | ohe_decoupe_complete1      | datetime      | YES  |     | NULL    |                |
      | ohe_decoupe_complete2      | datetime      | YES  |     | NULL    |                |
      | ohe_sprites_done           | datetime      | YES  |     | NULL    |                |
      | ohe_sprites_id             | varchar(32)   | YES  |     | NULL    |                |
      | ohe_sprites_id1            | varchar(32)   | YES  |     | NULL    |                |
      | ohe_sprites_id2            | varchar(32)   | YES  |     | NULL    |                |
      | ohe_sprites_started        | datetime      | YES  |     | NULL    |                |
      | ohe_sprites_started1       | datetime      | YES  |     | NULL    |                |
      | ohe_sprites_started2       | datetime      | YES  |     | NULL    |                |
      | ohe_sprites_host           | varchar(32)   | YES  |     | NULL    |                |
      | ohe_sprites_complete       | datetime      | YES  |     | NULL    |                |
      | ohe_sprites_complete1      | datetime      | YES  |     | NULL    |                |
      | ohe_sprites_complete2      | datetime      | YES  |     | NULL    |                |
      | transfer_src_rel_path      | varchar(255)  | YES  |     | NULL    |                |
      | transfer_src_alias         | varchar(32)   | YES  |     | NULL    |                |
      | transfer_dst_rel_path      | varchar(255)  | YES  |     | NULL    |                |
      | cdn_rel_path               | varchar(255)  | YES  |     | NULL    |                |
      | transfo_subtitles_complete | datetime      | YES  |     | NULL    |                |
      | transfo_web_id             | varchar(32)   | YES  | MUL | NULL    |                |
      | transfo_web_done           | datetime      | YES  |     | NULL    |                |
      | transfo_web_progress       | decimal(10,0) | YES  |     | NULL    |                |
      | transfo_web_started        | datetime      | YES  |     | NULL    |                |
      | transfo_web_complete       | datetime      | YES  |     | NULL    |                |
      | transfo_web_audio          | datetime      | YES  |     | NULL    |                |
      | transfo_web_video          | datetime      | YES  |     | NULL    |                |
      | transfo_web_transfer       | datetime      | YES  |     | NULL    |                |
      | activation_web_done        | datetime      | YES  |     | NULL    |                |
      | transfo_web_subtitles      | datetime      | YES  |     | NULL    |                |
      | transfo_subtitles_done     | datetime      | YES  |     | NULL    |                |
      | transfer_web_id            | varchar(16)   | YES  | MUL | NULL    |                |
      | transfer_web_done          | datetime      | YES  |     | NULL    |                |
      | transfer_web_progress      | decimal(5,2)  | YES  |     | NULL    |                |
      | transfer_web_started       | datetime      | YES  |     | NULL    |                |
      | transfer_web_complete      | datetime      | YES  |     | NULL    |                |
      | transfer_sprites_id        | varchar(32)   | YES  | MUL | NULL    |                |
      | transfer_sprites_done      | datetime      | YES  |     | NULL    |                |
      | transfer_sprites_progress  | decimal(5,2)  | YES  |     | NULL    |                |
      | transfer_sprites_started   | datetime      | YES  |     | NULL    |                |
      | transfer_sprites_complete  | datetime      | YES  |     | NULL    |                |
      | transfo_fai_done           | datetime      | YES  |     | NULL    |                |
      | transfo_fai_id             | varchar(32)   | YES  | MUL | NULL    |                |
      | transfo_fai_progress       | decimal(5,2)  | YES  |     | NULL    |                |
      | transfo_fai_started        | datetime      | YES  |     | NULL    |                |
      | transfo_fai_video          | datetime      | YES  |     | NULL    |                |
      | transfo_fai_complete       | datetime      | YES  |     | NULL    |                |
      | transfer_fai_id            | varchar(16)   | YES  | MUL | NULL    |                |
      | transfer_fai_done          | datetime      | YES  |     | NULL    |                |
      | transfer_fai_progress      | decimal(5,2)  | YES  |     | NULL    |                |
      | transfer_fai_started       | datetime      | YES  |     | NULL    |                |
      | transfer_fai_complete      | datetime      | YES  |     | NULL    |                |
      | transfo_ingest_done        | datetime      | YES  |     | NULL    |                |
      | transfo_ingest_id          | varchar(32)   | YES  |     | NULL    |                |
      | transfo_ingest_complete    | datetime      | YES  |     | NULL    |                |
      | transfo_ingest_error       | varchar(255)  | YES  |     | NULL    |                |
      | transfo_ingest_data        | varchar(4000) | YES  |     | NULL    |                |
      | transfo_ingest_thumbnail   | datetime      | YES  |     | NULL    |                |
      | activation_ingest_done     | datetime      | YES  |     | NULL    |                |
      | qc_pivot_done              | datetime      | YES  |     | NULL    |                |
      | qc_pivot_id                | varchar(255)  | YES  |     | NULL    |                |
      | qc_pivot_complete          | datetime      | YES  |     | NULL    |                |
      | qc_pivot_error             | varchar(255)  | YES  |     | NULL    |                |
      +----------------------------+---------------+------+-----+---------+----------------+
      100 rows in set (0.00 sec)
      

      Query

       SELECT   r0_.job_id                     AS job_id_0,
               r0_.job_key                    AS job_key_1,
               r0_.created_at                 AS created_at_2,
               r0_.updated_at                 AS updated_at_3,
               r0_.diffusion_id               AS diffusion_id_4,
               r0_.top_start                  AS top_start_5,
               r0_.top_end                    AS top_end_6,
               r0_.frame_start                AS frame_start_7,
               r0_.frame_end                  AS frame_end_8,
               r0_.locked_at                  AS locked_at_9,
               r0_.locked_for                 AS locked_for_10,
               r0_.job_done                   AS job_done_11,
               r0_.job_stop                   AS job_stop_12,
               r0_.droits_data                AS droits_data_13,
               r0_.droits_web                 AS droits_web_14,
               r0_.droits_fai                 AS droits_fai_15,
               r0_.prepare_done               AS prepare_done_16,
               r0_.prepare_ingest_done        AS prepare_ingest_done_17,
               r0_.prepare_ingest_complete    AS prepare_ingest_complete_18,
               r0_.volonte_startover          AS volonte_startover_19,
               r0_.volonte_anevia             AS volonte_anevia_20,
               r0_.volonte_decoupe            AS volonte_decoupe_21,
               r0_.volonte_web                AS volonte_web_22,
               r0_.volonte_fai                AS volonte_fai_23,
               r0_.volonte_details_fai        AS volonte_details_fai_24,
               r0_.type                       AS type_25,
               r0_.anevia_creation_done       AS anevia_creation_done_26,
               r0_.anevia_id                  AS anevia_id_27,
               r0_.anevia_offline_done        AS anevia_offline_done_28,
               r0_.anevia_delete_done         AS anevia_delete_done_29,
               r0_.transfer_src_rel_path      AS transfer_src_rel_path_30,
               r0_.transfer_src_alias         AS transfer_src_alias_31,
               r0_.transfer_dst_rel_path      AS transfer_dst_rel_path_32,
               r0_.cdn_rel_path               AS cdn_rel_path_33,
               r0_.ohe_decoupe_done           AS ohe_decoupe_done_34,
               r0_.ohe_decoupe_id             AS ohe_decoupe_id_35,
               r0_.ohe_decoupe_id1            AS ohe_decoupe_id1_36,
               r0_.ohe_decoupe_id2            AS ohe_decoupe_id2_37,
               r0_.ohe_decoupe_started        AS ohe_decoupe_started_38,
               r0_.ohe_decoupe_started1       AS ohe_decoupe_started1_39,
               r0_.ohe_decoupe_started2       AS ohe_decoupe_started2_40,
               r0_.ohe_decoupe_complete       AS ohe_decoupe_complete_41,
               r0_.ohe_decoupe_complete1      AS ohe_decoupe_complete1_42,
               r0_.ohe_decoupe_complete2      AS ohe_decoupe_complete2_43,
               r0_.ohe_decoupe_host           AS ohe_decoupe_host_44,
               r0_.ohe_sprites_id             AS ohe_sprites_id_45,
               r0_.ohe_sprites_id1            AS ohe_sprites_id1_46,
               r0_.ohe_sprites_id2            AS ohe_sprites_id2_47,
               r0_.ohe_sprites_done           AS ohe_sprites_done_48,
               r0_.ohe_sprites_started        AS ohe_sprites_started_49,
               r0_.ohe_sprites_started1       AS ohe_sprites_started1_50,
               r0_.ohe_sprites_started2       AS ohe_sprites_started2_51,
               r0_.ohe_sprites_complete       AS ohe_sprites_complete_52,
               r0_.ohe_sprites_complete1      AS ohe_sprites_complete1_53,
               r0_.ohe_sprites_complete2      AS ohe_sprites_complete2_54,
               r0_.ohe_sprites_host           AS ohe_sprites_host_55,
               r0_.transfer_sprites_id        AS transfer_sprites_id_56,
               r0_.transfer_sprites_started   AS transfer_sprites_started_57,
               r0_.transfer_sprites_progress  AS transfer_sprites_progress_58,
               r0_.transfer_sprites_complete  AS transfer_sprites_complete_59,
               r0_.transfer_sprites_done      AS transfer_sprites_done_60,
               r0_.transfer_web_id            AS transfer_web_id_61,
               r0_.transfer_web_done          AS transfer_web_done_62,
               r0_.transfer_web_progress      AS transfer_web_progress_63,
               r0_.transfer_web_started       AS transfer_web_started_64,
               r0_.transfer_web_complete      AS transfer_web_complete_65,
               r0_.transfo_web_done           AS transfo_web_done_66,
               r0_.transfo_web_id             AS transfo_web_id_67,
               r0_.transfo_web_progress       AS transfo_web_progress_68,
               r0_.transfo_web_started        AS transfo_web_started_69,
               r0_.transfo_web_complete       AS transfo_web_complete_70,
               r0_.transfo_web_subtitles      AS transfo_web_subtitles_71,
               r0_.transfo_web_audio          AS transfo_web_audio_72,
               r0_.transfo_web_video          AS transfo_web_video_73,
               r0_.transfo_web_transfer       AS transfo_web_transfer_74,
               r0_.activation_web_done        AS activation_web_done_75,
               r0_.transfo_subtitles_done     AS transfo_subtitles_done_76,
               r0_.transfo_subtitles_complete AS transfo_subtitles_complete_77,
               r0_.transfo_fai_done           AS transfo_fai_done_78,
               r0_.transfo_fai_id             AS transfo_fai_id_79,
               r0_.transfo_fai_started        AS transfo_fai_started_80,
               r0_.transfo_fai_progress       AS transfo_fai_progress_81,
               r0_.transfo_fai_video          AS transfo_fai_video_82,
               r0_.transfo_fai_complete       AS transfo_fai_complete_83,
               r0_.transfer_fai_done          AS transfer_fai_done_84,
               r0_.transfer_fai_id            AS transfer_fai_id_85,
               r0_.transfer_fai_started       AS transfer_fai_started_86,
               r0_.transfer_fai_progress      AS transfer_fai_progress_87,
               r0_.transfer_fai_complete      AS transfer_fai_complete_88,
               r0_.transfo_ingest_done        AS transfo_ingest_done_89,
               r0_.transfo_ingest_id          AS transfo_ingest_id_90,
               r0_.transfo_ingest_complete    AS transfo_ingest_complete_91,
               r0_.transfo_ingest_error       AS transfo_ingest_error_92,
               r0_.transfo_ingest_data        AS transfo_ingest_data_93,
               r0_.transfo_ingest_thumbnail   AS transfo_ingest_thumbnail_94,
               r0_.activation_ingest_done     AS activation_ingest_done_95,
               r0_.qc_pivot_done              AS qc_pivot_done_96,
               r0_.qc_pivot_id                AS qc_pivot_id_97,
               r0_.qc_pivot_complete          AS qc_pivot_complete_98,
               r0_.qc_pivot_error             AS qc_pivot_error_99
      FROM     replay_job r0_
      WHERE    r0_.diffusion_id IN ('139563997',
                                    '139634884',
                                    '140052371',
                                    '139695776',
                                    '139112601',
                                    '139634882',
                                    '139646633',
                                    '139695775',
                                    '140052370',
                                    '139697965',
                                    '139112600',
                                    '139634881',
                                    '139492212',
                                    '139644937',
                                    '139697964',
                                    '139697963',
                                    '139492211',
                                    '139644936',
                                    '139922068',
                                    '139643268',
                                    '139645093',
                                    '139643267',
                                    '139644935',
                                    '139491265',
                                    '139645092',
                                    '139646129',
                                    '140481208',
                                    '139646128',
                                    '139646127',
                                    '140481207',
                                    '139646126',
                                    '139643266',
                                    '140481206',
                                    '140481205',
                                    '139643265',
                                    '140481204',
                                    '140481190',
                                    '139649207',
                                    '140481189',
                                    '140481187',
                                    '139649206',
                                    '139643264',
                                    '140481186',
                                    '139646125',
                                    '139649205',
                                    '139649204',
                                    '140481184',
                                    '139646124',
                                    '140481183',
                                    '140481182',
                                    '139112599',
                                    '139563981',
                                    '139646123',
                                    '139646915',
                                    '139643263',
                                    '139560186',
                                    '139918226',
                                    '139918225',
                                    '139649203',
                                    '139922065',
                                    '139922064',
                                    '139646122',
                                    '139646121',
                                    '139649202',
                                    '139922063',
                                    '139922061',
                                    '139560184',
                                    '139922060',
                                    '139646120',
                                    '139649201',
                                    '139646909',
                                    '139643260',
                                    '139560183',
                                    '139649200',
                                    '139646907',
                                    '139646906',
                                    '139646119',
                                    '139646905',
                                    '139646118',
                                    '139634868',
                                    '139643258',
                                    '139646903',
                                    '140487763',
                                    '139648583',
                                    '140487762',
                                    '140487761',
                                    '139646117',
                                    '140487760',
                                    '140487759',
                                    '139646116',
                                    '140487758',
                                    '139646114',
                                    '140487757',
                                    '140487756',
                                    '139643257',
                                    '140487755',
                                    '140487754',
                                    '139112596',
                                    '139646113',
                                    '139643256')
      AND      r0_.type IN ('tops',
                            'manual',
                            'ingest')
      AND      r0_.job_id IN ('1121245',
                              '1121211',
                              '1121199',
                              '1121165',
                              '1121161',
                              '1121127',
                              '1121119',
                              '1121109',
                              '1121105',
                              '1121091',
                              '1121087',
                              '1121085',
                              '1121083',
                              '1121069',
                              '1121063',
                              '1121049',
                              '1121041',
                              '1121039',
                              '1121037',
                              '1120981',
                              '1120979',
                              '1120975',
                              '1120973',
                              '1120961',
                              '1120937',
                              '1120909',
                              '1120905',
                              '1120903',
                              '1120901',
                              '1120893',
                              '1120891',
                              '1120889',
                              '1120885',
                              '1120883',
                              '1120881',
                              '1120877',
                              '1120875',
                              '1120873',
                              '1120869',
                              '1120867',
                              '1120865',
                              '1120863',
                              '1120847',
                              '1120837',
                              '1120835',
                              '1120833',
                              '1120831',
                              '1120819',
                              '1120817',
                              '1120815',
                              '1120811',
                              '1120809',
                              '1120807',
                              '1120805',
                              '1120803',
                              '1120799',
                              '1120787',
                              '1120785',
                              '1120783',
                              '1120781',
                              '1120779',
                              '1120777',
                              '1120775',
                              '1120773',
                              '1120771',
                              '1120767',
                              '1120765',
                              '1120763',
                              '1120761',
                              '1120749',
                              '1120739',
                              '1120725',
                              '1120713',
                              '1120703',
                              '1120699',
                              '1120697',
                              '1120683',
                              '1120657',
                              '1120567',
                              '1120507',
                              '1120497',
                              '1120491',
                              '1120463',
                              '1120447',
                              '1120437',
                              '1120409',
                              '1120405',
                              '1120377',
                              '1120369',
                              '1120363',
                              '1120361',
                              '1120341',
                              '1120311',
                              '1120293',
                              '1120279',
                              '1120271',
                              '1120249',
                              '1120225',
                              '1120207',
                              '1120183',
                              '1120177',
                              '1120161',
                              '1120155',
                              '1120149',
                              '1120141',
                              '1120109',
                              '1120079',
                              '1120077',
                              '1120047',
                              '1120027',
                              '1120007',
                              '1119997',
                              '1119995',
                              '1119993',
                              '1119987',
                              '1119985',
                              '1119983',
                              '1119975',
                              '1119973',
                              '1119971',
                              '1119969',
                              '1119965',
                              '1119945',
                              '1119943',
                              '1119941',
                              '1119931',
                              '1119927',
                              '1119911',
                              '1119909',
                              '1119897',
                              '1119895',
                              '1119893',
                              '1119889',
                              '1119869',
                              '1119867',
                              '1119865',
                              '1119853',
                              '1119845',
                              '1119843',
                              '1119839',
                              '1119831',
                              '1119805',
                              '1119797',
                              '1119781')
      ORDER BY r0_.job_id DESC;
      

      Explain

       
      id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
      1    SIMPLE    r0_    range    PRIMARY,idx_replay_job_diffusion_id_type    PRIMARY    4    NULL    144    Using where
      

      Profiling

      Status    Duration
      starting                0.001663
      checking permissions    0.000247
      Opening tables            0.000290
      After opening tables    0.000247
      System lock                0.000221
      Table lock                0.000229
      init                    0.000639
      optimizing                0.000371
      statistics                0.578585
      preparing                0.000405
      executing                0.000148
      Sorting result            0.000146
      Sending data            0.010018
      end                        0.000291
      query end                0.000233
      closing tables            0.000229
      Unlocking tables        0.000242
      freeing items            0.000250
      updating status            0.002588
      logging slow query        0.000452
      cleaning up                0.000201
       
      real    0m0.816s
      user    0m0.028s
      sys        0m0.064s
      

      Profile all

       Status: statistics
                 Duration: 0.541196
                 CPU_user: 0.608038
               CPU_system: 0.000000
        Context_voluntary: 48
      Context_involuntary: 19
             Block_ops_in: 0
            Block_ops_out: 23
            Messages_sent: 0
        Messages_received: 0
        Page_faults_major: 0
        Page_faults_minor: 208
                    Swaps: 0
          Source_function: optimize_inner
              Source_file: sql_select.cc
              Source_line: 1373
      

      Index stats

      MariaDB [catchup]> show index from replay_job;
      +------------+------------+------------------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      | Table      | Non_unique | Key_name                           | Seq_in_index | Column_name          | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
      +------------+------------+------------------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      | replay_job |          0 | PRIMARY                            |            1 | job_id               | A         |      180111 |     NULL | NULL   |      | BTREE      |         |               |
      | replay_job |          1 | idx_replay_job_key                 |            1 | job_key              | A         |      180111 |     NULL | NULL   |      | BTREE      |         |               |
      | replay_job |          1 | idx_replay_job_updated_at          |            1 | updated_at           | A         |      180111 |     NULL | NULL   | YES  | BTREE      |         |               |
      | replay_job |          1 | idx_replay_job_diffusion_id_type   |            1 | diffusion_id         | A         |      180111 |     NULL | NULL   | YES  | BTREE      |         |               |
      | replay_job |          1 | idx_replay_job_diffusion_id_type   |            2 | type                 | A         |      180111 |     NULL | NULL   | YES  | BTREE      |         |               |
      | replay_job |          1 | idx_replay_job_transfo_fai_id      |            1 | transfo_fai_id       | A         |       60037 |     NULL | NULL   | YES  | BTREE      |         |               |
      | replay_job |          1 | idx_replay_job_transfo_web_id      |            1 | transfo_web_id       | A         |       90055 |     NULL | NULL   | YES  | BTREE      |         |               |
      | replay_job |          1 | idx_replay_job_transfer_web_id     |            1 | transfer_web_id      | A         |       90055 |     NULL | NULL   | YES  | BTREE      |         |               |
      | replay_job |          1 | idx_replay_job_transfer_fai_id     |            1 | transfer_fai_id      | A         |       60037 |     NULL | NULL   | YES  | BTREE      |         |               |
      | replay_job |          1 | idx_replay_job_transfer_sprites_id |            1 | transfer_sprites_id  | A         |       90055 |     NULL | NULL   | YES  | BTREE      |         |               |
      | replay_job |          1 | idx_replay_job_worker              |            1 | created_at           | A         |      180111 |     NULL | NULL   |      | BTREE      |         |               |
      | replay_job |          1 | idx_replay_job_worker              |            2 | locked_at            | A         |      180111 |     NULL | NULL   | YES  | BTREE      |         |               |
      | replay_job |          1 | idx_replay_job_worker              |            3 | job_done             | A         |      180111 |     NULL | NULL   | YES  | BTREE      |         |               |
      | replay_job |          1 | idx_replay_job_worker              |            4 | job_stop             | A         |      180111 |     NULL | NULL   | YES  | BTREE      |         |               |
      | replay_job |          1 | idx_replay_job_anevia_offline      |            1 | anevia_creation_done | A         |       60037 |     NULL | NULL   | YES  | BTREE      |         |               |
      | replay_job |          1 | idx_replay_job_anevia_offline      |            2 | anevia_offline_done  | A         |       60037 |     NULL | NULL   | YES  | BTREE      |         |               |
      | replay_job |          1 | idx_replay_job_anevia_offline      |            3 | updated_at           | A         |      180111 |     NULL | NULL   | YES  | BTREE      |         |               |
      | replay_job |          1 | idx_replay_job_anevia_delete       |            1 | anevia_offline_done  | A         |       60037 |     NULL | NULL   | YES  | BTREE      |         |               |
      | replay_job |          1 | idx_replay_job_anevia_delete       |            2 | anevia_delete_done   | A         |       60037 |     NULL | NULL   | YES  | BTREE      |         |               |
      | replay_job |          1 | idx_replay_job_anevia_delete       |            3 | updated_at           | A         |      180111 |     NULL | NULL   | YES  | BTREE      |         |               |
      +------------+------------+------------------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      

      10.1 InnoDB variables

      MariaDB [(none)]> show global variables like '%innodb_%';
      +---------------------------------------------+------------------------+
      | Variable_name                               | Value                  |
      +---------------------------------------------+------------------------+
      | innodb_adaptive_flushing                    | ON                     |
      | innodb_adaptive_flushing_lwm                | 10.000000              |
      | innodb_adaptive_hash_index                  | ON                     |
      | innodb_adaptive_hash_index_partitions       | 1                      |
      | innodb_adaptive_max_sleep_delay             | 150000                 |
      | innodb_additional_mem_pool_size             | 8388608                |
      | innodb_api_bk_commit_interval               | 5                      |
      | innodb_api_disable_rowlock                  | OFF                    |
      | innodb_api_enable_binlog                    | OFF                    |
      | innodb_api_enable_mdl                       | OFF                    |
      | innodb_api_trx_level                        | 0                      |
      | innodb_autoextend_increment                 | 64                     |
      | innodb_autoinc_lock_mode                    | 1                      |
      | innodb_background_scrub_data_check_interval | 3600                   |
      | innodb_background_scrub_data_compressed     | OFF                    |
      | innodb_background_scrub_data_interval       | 604800                 |
      | innodb_background_scrub_data_uncompressed   | OFF                    |
      | innodb_buf_dump_status_frequency            | 0                      |
      | innodb_buffer_pool_dump_at_shutdown         | OFF                    |
      | innodb_buffer_pool_dump_now                 | OFF                    |
      | innodb_buffer_pool_dump_pct                 | 100                    |
      | innodb_buffer_pool_filename                 | ib_buffer_pool         |
      | innodb_buffer_pool_instances                | 4                      |
      | innodb_buffer_pool_load_abort               | OFF                    |
      | innodb_buffer_pool_load_at_startup          | OFF                    |
      | innodb_buffer_pool_load_now                 | OFF                    |
      | innodb_buffer_pool_populate                 | OFF                    |
      | innodb_buffer_pool_size                     | 7516192768             |
      | innodb_change_buffer_max_size               | 25                     |
      | innodb_change_buffering                     | all                    |
      | innodb_checksum_algorithm                   | INNODB                 |
      | innodb_checksums                            | ON                     |
      | innodb_cleaner_lsn_age_factor               | HIGH_CHECKPOINT        |
      | innodb_cmp_per_index_enabled                | OFF                    |
      | innodb_commit_concurrency                   | 0                      |
      | innodb_compression_algorithm                | none                   |
      | innodb_compression_failure_threshold_pct    | 5                      |
      | innodb_compression_level                    | 6                      |
      | innodb_compression_pad_pct_max              | 50                     |
      | innodb_concurrency_tickets                  | 5000                   |
      | innodb_corrupt_table_action                 | assert                 |
      | innodb_data_file_path                       | ibdata1:12M:autoextend |
      | innodb_data_home_dir                        |                        |
      | innodb_default_encryption_key_id            | 1                      |
      | innodb_defragment                           | OFF                    |
      | innodb_defragment_fill_factor               | 0.900000               |
      | innodb_defragment_fill_factor_n_recs        | 20                     |
      | innodb_defragment_frequency                 | 40                     |
      | innodb_defragment_n_pages                   | 7                      |
      | innodb_defragment_stats_accuracy            | 0                      |
      | innodb_disable_sort_file_cache              | OFF                    |
      | innodb_disallow_writes                      | OFF                    |
      | innodb_doublewrite                          | ON                     |
      | innodb_empty_free_list_algorithm            | BACKOFF                |
      | innodb_encrypt_log                          | OFF                    |
      | innodb_encrypt_tables                       | OFF                    |
      | innodb_encryption_rotate_key_age            | 1                      |
      | innodb_encryption_rotation_iops             | 100                    |
      | innodb_encryption_threads                   | 0                      |
      | innodb_fake_changes                         | OFF                    |
      | innodb_fast_shutdown                        | 1                      |
      | innodb_fatal_semaphore_wait_threshold       | 600                    |
      | innodb_file_format                          | Barracuda              |
      | innodb_file_format_check                    | ON                     |
      | innodb_file_format_max                      | Antelope               |
      | innodb_file_per_table                       | ON                     |
      | innodb_flush_log_at_timeout                 | 1                      |
      | innodb_flush_log_at_trx_commit              | 2                      |
      | innodb_flush_method                         | O_DIRECT               |
      | innodb_flush_neighbors                      | 1                      |
      | innodb_flushing_avg_loops                   | 30                     |
      | innodb_force_load_corrupted                 | OFF                    |
      | innodb_force_primary_key                    | OFF                    |
      | innodb_force_recovery                       | 0                      |
      | innodb_foreground_preflush                  | EXPONENTIAL_BACKOFF    |
      | innodb_ft_aux_table                         |                        |
      | innodb_ft_cache_size                        | 8000000                |
      | innodb_ft_enable_diag_print                 | OFF                    |
      | innodb_ft_enable_stopword                   | ON                     |
      | innodb_ft_max_token_size                    | 84                     |
      | innodb_ft_min_token_size                    | 3                      |
      | innodb_ft_num_word_optimize                 | 2000                   |
      | innodb_ft_result_cache_limit                | 2000000000             |
      | innodb_ft_server_stopword_table             |                        |
      | innodb_ft_sort_pll_degree                   | 2                      |
      | innodb_ft_total_cache_size                  | 640000000              |
      | innodb_ft_user_stopword_table               |                        |
      | innodb_idle_flush_pct                       | 100                    |
      | innodb_immediate_scrub_data_uncompressed    | OFF                    |
      | innodb_instrument_semaphores                | OFF                    |
      | innodb_io_capacity                          | 800                    |
      | innodb_io_capacity_max                      | 2000                   |
      | innodb_kill_idle_transaction                | 0                      |
      | innodb_large_prefix                         | OFF                    |
      | innodb_lock_wait_timeout                    | 400                    |
      | innodb_locking_fake_changes                 | ON                     |
      | innodb_locks_unsafe_for_binlog              | OFF                    |
      | innodb_log_arch_dir                         | ./                     |
      | innodb_log_arch_expire_sec                  | 0                      |
      | innodb_log_archive                          | OFF                    |
      | innodb_log_block_size                       | 512                    |
      | innodb_log_buffer_size                      | 134217728              |
      | innodb_log_checksum_algorithm               | INNODB                 |
      | innodb_log_compressed_pages                 | OFF                    |
      | innodb_log_file_size                        | 524288000              |
      | innodb_log_files_in_group                   | 4                      |
      | innodb_log_group_home_dir                   | ./                     |
      | innodb_lru_scan_depth                       | 1024                   |
      | innodb_max_bitmap_file_size                 | 104857600              |
      | innodb_max_changed_pages                    | 1000000                |
      | innodb_max_dirty_pages_pct                  | 40.000000              |
      | innodb_max_dirty_pages_pct_lwm              | 0.001000               |
      | innodb_max_purge_lag                        | 0                      |
      | innodb_max_purge_lag_delay                  | 0                      |
      | innodb_mirrored_log_groups                  | 1                      |
      | innodb_monitor_disable                      |                        |
      | innodb_monitor_enable                       |                        |
      | innodb_monitor_reset                        |                        |
      | innodb_monitor_reset_all                    |                        |
      | innodb_mtflush_threads                      | 8                      |
      | innodb_old_blocks_pct                       | 37                     |
      | innodb_old_blocks_time                      | 1000                   |
      | innodb_online_alter_log_max_size            | 134217728              |
      | innodb_open_files                           | 2048                   |
      | innodb_optimize_fulltext_only               | OFF                    |
      | innodb_page_size                            | 16384                  |
      | innodb_prefix_index_cluster_optimization    | OFF                    |
      | innodb_print_all_deadlocks                  | OFF                    |
      | innodb_purge_batch_size                     | 300                    |
      | innodb_purge_threads                        | 1                      |
      | innodb_random_read_ahead                    | OFF                    |
      | innodb_read_ahead_threshold                 | 56                     |
      | innodb_read_io_threads                      | 4                      |
      | innodb_read_only                            | OFF                    |
      | innodb_replication_delay                    | 0                      |
      | innodb_rollback_on_timeout                  | OFF                    |
      | innodb_rollback_segments                    | 128                    |
      | innodb_sched_priority_cleaner               | 19                     |
      | innodb_scrub_log                            | OFF                    |
      | innodb_scrub_log_speed                      | 256                    |
      | innodb_show_locks_held                      | 10                     |
      | innodb_show_verbose_locks                   | 0                      |
      | innodb_simulate_comp_failures               | 0                      |
      | innodb_sort_buffer_size                     | 1048576                |
      | innodb_spin_wait_delay                      | 6                      |
      | innodb_stats_auto_recalc                    | ON                     |
      | innodb_stats_method                         | nulls_equal            |
      | innodb_stats_modified_counter               | 0                      |
      | innodb_stats_on_metadata                    | OFF                    |
      | innodb_stats_persistent                     | ON                     |
      | innodb_stats_persistent_sample_pages        | 20                     |
      | innodb_stats_sample_pages                   | 8                      |
      | innodb_stats_traditional                    | ON                     |
      | innodb_stats_transient_sample_pages         | 8                      |
      | innodb_status_output                        | OFF                    |
      | innodb_status_output_locks                  | OFF                    |
      | innodb_strict_mode                          | OFF                    |
      | innodb_support_xa                           | ON                     |
      | innodb_sync_array_size                      | 1                      |
      | innodb_sync_spin_loops                      | 30                     |
      | innodb_table_locks                          | ON                     |
      | innodb_thread_concurrency                   | 0                      |
      | innodb_thread_sleep_delay                   | 10000                  |
      | innodb_track_changed_pages                  | OFF                    |
      | innodb_undo_directory                       | .                      |
      | innodb_undo_logs                            | 128                    |
      | innodb_undo_tablespaces                     | 0                      |
      | innodb_use_atomic_writes                    | OFF                    |
      | innodb_use_fallocate                        | OFF                    |
      | innodb_use_global_flush_log_at_trx_commit   | ON                     |
      | innodb_use_mtflush                          | OFF                    |
      | innodb_use_native_aio                       | ON                     |
      | innodb_use_stacktrace                       | OFF                    |
      | innodb_use_sys_malloc                       | ON                     |
      | innodb_use_trim                             | OFF                    |
      | innodb_version                              | 5.6.28-76.1            |
      | innodb_write_io_threads                     | 16                     |
      +---------------------------------------------+------------------------+
      177 rows in set (0.00 sec)
      

      analyze statement

      *************************** 1. row ***************************
                 id: 1
        select_type: SIMPLE
              table: r0_
               type: range
      possible_keys: PRIMARY,idx_replay_job_diffusion_id_type
                key: PRIMARY
            key_len: 4
                ref: NULL
               rows: 144
             r_rows: 144.00
           filtered: 100.00
         r_filtered: 100.00
              Extra: Using where
      
      

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              stephane@skysql.com VAROQUI Stephane
              Votes:
              1 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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