Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.1.12, 10.1.13
-
Debian 7.9 Weezy 64 bit
-
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
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
Activity
stephane@skysql.com, could you try these on 10.1:
set optimizer_switch='extended_keys=off';
|
EXPLAIN SELECT ... -- run and note the query output, time it took, and SHOW PROFILE output for this query
|
And also
EXPLAIN SELECT ... FROM replay_job IGNORE INDEX (idx_replay_job_diffusion_id_type)
|
Sergei , All my apology
After our chat of last day , we have retested extended_keys=off and indeed it works and get back x5 performance
I have the dump of the table if you still need
extended_keys=on is default, after so many cases where it do not help , do you see a way out to identified such cases ?
if no way we can probably close this ticket as duplicate !
> extended_keys=on is default, after so many cases where it do not help , do you see a way out to identified such cases ?
I'm not sure how exactly this issue should be fixed. We need to resolve it, though.
I'm leaving this issue open, so it is not forgotten.
Summarizing some data from the bug report:
Relevant table indexes:
PRIMARY KEY(job_id)idx_replay_job_diffusion_id_type(diffusion_id, type /* , job_id (because of extended keys) */)the query is:
select * from replay_job wherediffusion_id IN (<100 values>) ANDtype IN (<3 values>) ANDjob_id IN(<144 values>)job_id is PK, so query output is at most 144 rows (ANALYZE output confirms it's 144 rows)
SHOW PROFILE hints at the problem being in the range optimizer ("statistics" phase takes 96% of the time).