query hangs after migration from mysql 5.5 to mysql 5.6
Another problem after migration from mysql 5.5. to mysql 5.6 .
this time i work on a huge table (108 milion of rows) and a second table with 82000 rows, on mysql 5.5 the query is very fast (few millisecs ) on mysql 5.6 the query hangs , i killed after 1600 secs .....
the query is :
select
`LTV_AGGREGATE`.`provider` as `LTV_AGGREGATE_provider`,
`LTV_AGGREGATE`.`DATA_INIZIO` as `LTV_AGGREGATE_DATA_INIZIO`,
`GES_L_PRODOTTO`.`prodotto_liv_2` as `GES_L_PRODOTTO_prodotto_liv_2`,
`GES_L_PRODOTTO`.`prodotto_liv_1` as `GES_L_PRODOTTO_prodotto_liv_1`
from `LTV_AGGREGATE`, `GES_L_PRODOTTO`
where
`LTV_AGGREGATE`.`product_id` = `GES_L_PRODOTTO`.`id_prodotto`
and (`LTV_AGGREGATE`.`DATA_INIZIO` >= '2015-12-09' and
`LTV_AGGREGATE`.`DATA_INIZIO` <= '2015-12-16')
and (((`LTV_AGGREGATE`.`product_id`
not in ('EI_ADIG_ENI1W', 'EI_CALP_ENI1W', 'EI_CENT_ENI1W', 'EI_CSAL_ENI1W', 'EI_ESP_ENI1W', 'EI_GMAN_ENI1W', 'EI_GMON_ENI1W',
'EI_GREG_ENI1W', 'EI_MPAD_ENI1W', 'EI_NFER_ENI1W', 'EI_NSAR_ENI1W', 'EI_NVEN_ENI1W', 'EI_PIC_ENI1W', 'EI_PPAV_ENI1W',
'EI_REP_ENI1W', 'EI_SCAN_ENI1W', 'EI_TIRR_ENI1W', 'EI_TREN_ENI1W', 'EI_TTRE_ENI1W'))
))
and `GES_L_PRODOTTO`.`prodotto_liv_2` in ('BUNDLE', 'ESPRESSO', 'LIMES WEB', 'MANCANTE', 'REPUBBLICA+', 'REPUBBLICAMOBILE')
order by `LTV_AGGREGATE`.`DATA_INIZIO`
limit 200;
the table are the same in the two databases , :
mysql> SHOW CREATE TABLE `GES_L_PRODOTTO`\G;
*************************** 1. row ***************************
Table: GES_L_PRODOTTO
Create Table: CREATE TABLE `GES_L_PRODOTTO` (
`Ids` int(10) NOT NULL,
`id_prodotto` varchar(50) DEFAULT NULL,
`prodotto_liv_1` varchar(50) DEFAULT NULL,
`prodotto_liv_2` varchar(50) DEFAULT NULL,
`categoria_prodotto` varchar(10) DEFAULT NULL,
`id_prodotto_descr` varchar(255) DEFAULT NULL,
`prodotto` varchar(255) DEFAULT NULL,
PRIMARY KEY (`Ids`),
KEY `Ids` (`Ids`),
KEY `id_prodotto` (`id_prodotto`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.02 sec)
mysql> SHOW TABLE STATUS FROM LIKE 'GES_L_PRODOTTO'\G;
*************************** 1. row ***************************
Name: GES_L_PRODOTTO
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 1047
Avg_row_length: 234
Data_length: 245760
Max_data_length: 0
Index_length: 81920
Data_free: 0
Auto_increment: NULL
Create_time: 2015-12-16 21:18:49
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.03 sec)
ERROR:
No query specified
mysql> SHOW CREATE TABLE `LTV_AGGREGATE`\G;
*************************** 1. row ***************************
Table: LTV_AGGREGATE
Create Table: CREATE TABLE `LTV_AGGREGATE` (
`DATA_INIZIO` date DEFAULT NULL,
`provider` varchar(103) NOT NULL DEFAULT '',
`platform` varchar(50) NOT NULL DEFAULT '',
`application` varchar(50) NOT NULL DEFAULT '',
`product_id` varchar(50) NOT NULL DEFAULT '',
`source` varchar(50) NOT NULL DEFAULT '',
`activation_date` date DEFAULT NULL,
`cancel_date` date DEFAULT NULL,
`expire_date` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
`sconto` varchar(50) DEFAULT NULL,
`nr_trans_att` decimal(65,0) DEFAULT NULL,
`att_promo` decimal(63,0) DEFAULT NULL,
`att_promo_pag` decimal(63,0) DEFAULT NULL,
`att_paganti` decimal(63,0) DEFAULT NULL,
`pag_primo_billing` decimal(63,0) DEFAULT NULL,
`pag_confermato` decimal(63,0) DEFAULT NULL,
`retry_0_pag` decimal(63,0) DEFAULT NULL,
`retry_4_weeks` decimal(63,0) DEFAULT NULL,
`retry_4_8_weeks` decimal(63,0) DEFAULT NULL,
`retry_8_weeks` decimal(63,0) DEFAULT NULL,
`promo` decimal(64,0) DEFAULT NULL,
`retry` decimal(65,0) DEFAULT NULL,
`paganti` decimal(65,0) DEFAULT NULL,
`nr_trans_dis` decimal(65,0) DEFAULT NULL,
`disatt_promo` decimal(63,0) DEFAULT NULL,
`disatt_promo_pag` decimal(63,0) DEFAULT NULL,
`disatt_paganti` decimal(63,0) DEFAULT NULL,
`canc_pag_primo_billing` decimal(63,0) DEFAULT NULL,
`canc_pag_confermato` decimal(63,0) DEFAULT NULL,
`disatt_retry_0_pag` decimal(63,0) DEFAULT NULL,
`disatt_retry_4_weeks` decimal(63,0) DEFAULT NULL,
`disatt_retry_4_8_weeks` decimal(63,0) DEFAULT NULL,
`disatt_retry_8_weeks` decimal(63,0) DEFAULT NULL,
`deleted_in_promo` decimal(65,0) DEFAULT NULL,
`deleted_out_promo` decimal(65,0) DEFAULT NULL,
KEY `product_id` (`product_id`),
KEY `application` (`application`),
KEY `platform` (`platform`),
KEY `source` (`source`),
KEY `activation_date` (`activation_date`),
KEY `expire_date` (`expire_date`),
KEY `DATA_INIZIO` (`DATA_INIZIO`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> SHOW TABLE STATUS FROM LIKE 'LTV_AGGREGATE'\G;
*************************** 1. row ***************************
Name: LTV_AGGREGATE
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 108783105
Avg_row_length: 905
Data_length: 98478063616
Max_data_length: 0
Index_length: 17516478464
Data_free: 6291456
Auto_increment: NULL
Create_time: 2015-04-03 16:44:28
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (1.65 sec)
-- on mysql 5.5, i have the output after 0.01 secs
...
-----+-------------------------------+
200 rows in set (0.01 sec)
- on 5.6 i kill my query after 1600 secs !!!!!!!!
| 286 | root | 172.31.136.21:37823 | db_jdatamart | Query | 1604 | Sending data | select
`LTV_AGGREGATE`.`provider` as `LTV_AGGREGATE_provider`, |
explain 5.6
+----+-------------+----------------+------+------------------------+------------+---------+-----------------------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+------+------------------------+------------+---------+-----------------------------------------+------+----------------------------------------------+
| 1 | SIMPLE | GES_L_PRODOTTO | ALL | id_prodotto | NULL | NULL | NULL | 1182 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | LTV_AGGREGATE | ref | product_id,DATA_INIZIO | product_id | 52 | db_jdatamart.GES_L_PRODOTTO.id_prodotto | 1413 | Using where |
+----+-------------+----------------+------+------------------------+------------+---------+-----------------------------------------+------+----------------------------------------------+
explain 5.5
+----+-------------+----------------+-------+------------------------+-------------+---------+---------------------------------------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+-------+------------------------+-------------+---------+---------------------------------------+---------+-------------+
| 1 | SIMPLE | LTV_AGGREGATE | range | product_id,DATA_INIZIO | DATA_INIZIO | 4 | NULL | 6973602 | Using where |
| 1 | SIMPLE | GES_L_PRODOTTO | ref | id_prodotto | id_prodotto | 53 | db_jdatamart.LTV_AGGREGATE.product_id | 1 | Using where |
+----+-------------+----------------+-------+------------------------+-------------+---------+---------------------------------------+---------+-------------+
how can debug/tracing query on mysql 5.6 ,
any suggest ?
w