SELECT execution is slow on InnoDB when compared to MyISAM
We experiencing a slow select on InnoDB comparing to MyISAM - difference for execution on MyISAM is about 2 min., on InnoDB is 2 h.
The number of rows involved is 33 M. The row set in result is about 23M.
Server:
Location: Google Cloud
Disk: 4T SSD
Memory:
[root@gcpjobrunner01 ~]# free -g
total used free shared buff/cache available
Mem: 58 44 0 0 12 13
Swap: 0 0 0
MySQL exact version:
mysql> show global variables like '%version%';
+-------------------------+----------------------------------------------------+
| Variable_name | Value |
+-------------------------+----------------------------------------------------+
| innodb_version | 5.7.24-27 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1,TLSv1.2 |
| version | 5.7.24-27-log |
| version_comment | Percona Server (GPL), Release 27, Revision bd42700 |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
| version_suffix | -log |
+-------------------------+----------------------------------------------------+
The DDL of the relevant table:
CREATE TABLE `account_balances` (
`abl_aad_id` double NOT NULL,
`abl_txn_type_cd` varchar(8) NOT NULL,
`abl_prm_code` varchar(30) NOT NULL,
`abl_bal_sort` double NOT NULL DEFAULT '1',
`abl_bal_abl` double NOT NULL,
`abl_bal` double NOT NULL,
`abl_bal_pd` double NOT NULL,
`abl_bal_waived` double NOT NULL,
`abl_bal_chgoff` double NOT NULL DEFAULT '0',
`abl_bal_recovered` double NOT NULL DEFAULT '0',
`abl_bal_adjusted_plus` double NOT NULL DEFAULT '0',
`abl_bal_adjusted_minus` double NOT NULL DEFAULT '0',
`abl_bal_open_ctd` double NOT NULL DEFAULT '0',
`abl_bal_ctd` double NOT NULL,
`abl_bal_pd_ctd` double NOT NULL,
`abl_bal_waived_ctd` double NOT NULL,
`abl_bal_chgoff_open_ctd` double NOT NULL,
`abl_bal_chgoff_ctd` double NOT NULL,
`abl_bal_recovered_ctd` double NOT NULL,
`abl_bal_adjusted_plus_ctd` double NOT NULL,
`abl_bal_adjusted_minus_ctd` double NOT NULL,
`abl_bal_open_ytd` double NOT NULL,
`abl_bal_ytd` double NOT NULL,
`abl_bal_pd_ytd` double NOT NULL,
`abl_bal_waived_ytd` double NOT NULL,
`abl_bal_chgoff_open_ytd` double NOT NULL,
`abl_bal_chgoff_ytd` double NOT NULL,
`abl_bal_recovered_ytd` double NOT NULL,
`abl_bal_adjusted_plus_ytd` double NOT NULL,
`abl_bal_adjusted_minus_ytd` double NOT NULL,
`abl_bal_billed_ind` varchar(30) NOT NULL,
`abl_interest_accrued_ind` varchar(30) NOT NULL DEFAULT 'y',
`abl_chargeoff_method_cd` varchar(30) NOT NULL,
`abl_reschedule_method_cd` varchar(30) NOT NULL,
`abl_writeoff_method_cd` varchar(30) NOT NULL,
`abl_bal_open_ctd1` double NOT NULL DEFAULT '0',
`abl_bal_ctd1` double NOT NULL,
`abl_bal_pd_ctd1` double NOT NULL,
`abl_bal_waived_ctd1` double NOT NULL,
`abl_bal_chgoff_open_ctd1` double NOT NULL,
`abl_bal_chgoff_ctd1` double NOT NULL,
`abl_bal_recovered_ctd1` double NOT NULL,
`abl_bal_adjusted_plus_ctd1` double NOT NULL,
`abl_bal_adjusted_minus_ctd1` double NOT NULL,
`abl_bal_open_ytd1` double NOT NULL,
`abl_bal_ytd1` double NOT NULL,
`abl_bal_pd_ytd1` double NOT NULL,
`abl_bal_waived_ytd1` double NOT NULL,
`abl_bal_chgoff_open_ytd1` double NOT NULL,
`abl_bal_chgoff_ytd1` double NOT NULL DEFAULT '0',
`abl_bal_recovered_ytd1` double NOT NULL DEFAULT '0',
`abl_bal_adjusted_plus_ytd1` double NOT NULL DEFAULT '0',
`abl_bal_adjusted_minus_ytd1` double NOT NULL DEFAULT '0',
`abl_bill_method_cd` varchar(30) NOT NULL DEFAULT 'lv',
`abl_pmt_amt` double NOT NULL,
`abl_pmt_per` double NOT NULL DEFAULT '0',
`abl_dt` datetime NOT NULL,
`abl_prom_type_cd` varchar(30) NOT NULL,
`abl_rate` double NOT NULL,
`abl_term` double NOT NULL,
`abl_prm_end_dt` datetime NOT NULL,
`abl_due_amt1` double NOT NULL,
`abl_due_amt2` double NOT NULL,
`abl_due_amt3` double NOT NULL,
`abl_due_amt4` double NOT NULL DEFAULT '0',
`abl_due_amt5` double NOT NULL,
`abl_due_amt_pd1` double NOT NULL,
`abl_due_amt_pd2` double NOT NULL,
`abl_due_amt_pd3` double NOT NULL,
`abl_due_amt_pd4` double NOT NULL,
`abl_due_amt_pd5` double NOT NULL,
`abl_pin_code` varchar(30) NOT NULL DEFAULT 'undefined',
`abl_ins_sub_type_cd` varchar(30) NOT NULL DEFAULT 'undefined',
`abl_ins_status_cd` varchar(30) NOT NULL,
`abl_acc_orig_sys_xref` varchar(30) NOT NULL,
`created_by` varchar(30) NOT NULL,
`creation_date` datetime NOT NULL,
`last_updated_by` varchar(30) NOT NULL,
`last_update_date` datetime NOT NULL,
`abl_bal_terminate` double NOT NULL,
`abl_bal_terminate_ctd` double NOT NULL,
`abl_bal_terminate_ytd` double NOT NULL,
`abl_bal_terminate_ctd1` double NOT NULL,
`abl_bal_terminate_ytd1` double NOT NULL,
`abl_terminate_ind` varchar(30) NOT NULL,
`abl_bal_abl_ctd1` double NOT NULL,
`abl_bal_abl_ctd2` double NOT NULL,
`abl_bal_abl_ctd3` double NOT NULL,
`abl_bal_abl_ctd4` double NOT NULL DEFAULT '0',
`abl_bal_abl_ctd5` double NOT NULL DEFAULT '0',
`abl_non_perform_roll_ind` varchar(30) NOT NULL,
`abl_non_perform_txn_type_cd` varchar(30) NOT NULL,
`abl_bal_xfer2non_perform` double NOT NULL,
`abl_bal_pd_xfer2non_perform` double NOT NULL,
`abl_non_perform_bal` double NOT NULL,
`abl_non_perform_bal_pd` double NOT NULL,
`abl_non_perform_bal_pd_excess` double NOT NULL,
`abl_non_perform_bal_waived` double NOT NULL,
`abl_non_perform_bal_adj_plus` double NOT NULL,
`abl_non_perform_bal_adj_minus` double NOT NULL,
`abl_bal_billed` double NOT NULL,
`abl_bal_billed_ctd` double NOT NULL,
`abl_bal_billed_ctd1` double NOT NULL,
`abl_bal_billed_ytd` double NOT NULL,
UNIQUE KEY `account_balances_abl_udx` (`abl_aad_id`,`abl_txn_type_cd`,`abl_prm_code`),
KEY `abl_txn_type_cd` (`abl_txn_type_cd`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
The DDL is the same for the query run on MyISAM.
Here is the query:
SELECT
abl_aad_id,
abl_txn_type_cd,
abl_bal_chgoff,
abl_bal_recovered,
abl_bal,
abl_bal_pd,
abl_bal_waived,
abl_bal_adjusted_minus,
abl_bal_adjusted_plus
FROM
account_balances
WHERE abl_txn_type_cd IN ('ADV','INT','FLC','FNSF','FEXT','FOTH1','FPHP','EBKR','ERPO','ESVC','EOTH1','ADV');
Here is the actual SQL from SP:
SELECT NOW(), 'Creating tmp_mv_account_balances';
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));
DROP TABLE IF EXISTS tmp_mv_account_balances;
CREATE TABLE tmp_mv_account_balances AS
SELECT acc_nbr AS "acc_nbr_bal",
abl_aad_id AS "aad_id_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'ADV' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus END ELSE 0 END ) , 2) AS "advance_principal_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'INT' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus END ELSE 0 END ) , 2) AS "interest_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'FLC' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus END ELSE 0 END ) , 2) AS "fee_late_charge_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'FNSF' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus END ELSE 0 END ) , 2) AS "fee_nsf_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'FEXT' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus END ELSE 0 END ) , 2) AS "fee_extension_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'FOTH1' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus END ELSE 0 END ) , 2) AS "fee_convenience_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'FPHP' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus END ELSE 0 END ) , 2) AS "fee_phone_pay_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'EBKR' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus END ELSE 0 END ) , 2) AS "expense_legal_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'ERPO' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus END ELSE 0 END ) , 2) AS "expense_repo_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'ESVC' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus END ELSE 0 END ) , 2) AS "expense_dmv_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'EOTH1' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus END ELSE 0 END ) , 2) AS "expense_deferred_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'ADV' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus - acc_int_rebate_amt END ELSE 0 END ) , 2) AS "net_advance_principal_bal",
acc_int_rebate_amt AS "int_rebate_bal"
FROM
account_balances
JOIN accounts ON abl_aad_id = acc_aad_id
GROUP BY
abl_aad_id;
SELECT NOW(), 'Creating indexes';
ALTER TABLE tmp_mv_account_balances ADD INDEX idx01_bal ( acc_nbr_bal );
ALTER TABLE tmp_mv_account_balances ADD INDEX idx02_bal ( aad_id_bal );
MyISAM is returning the result in 2 min., but in InnoDB it is taking "forever".
Explain plan for InnoDB:
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ---------------- ---------- ------ --------------- ------ ------- ------ -------- -------- -------------
1 SIMPLE account_balances (NULL) ALL abl_txn_type_cd (NULL) (NULL) (NULL) 28858313 100.00 Using where
Explain plan in MyISAM:
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ---------------- ---------- ------ ------------- ------ ------- ------ -------- -------- -------------
1 SIMPLE account_balances (NULL) ALL (NULL) (NULL) (NULL) (NULL) 33628821 50.00 Using where
Here is explain plan with force index (abl_txn_type_cd)
for InnoDB, we dont have that index in MyISAM, but it does not matter - the SELECT is running forever in InnoDB.
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ---------------- ---------- ------ --------------- --------------- ------- ------ -------- -------- -----------------------
1 SIMPLE account_balances (NULL) range abl_txn_type_cd abl_txn_type_cd 10 (NULL) 40672694 100.00 Using index condition
Here is SHOW TABLE STATUS
InnoDB:
mysql> show table status like 'account_balances';
+------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+-----------------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+-----------------------+---------+
| account_balances | InnoDB | 10 | Compressed | 31858884 | 521 | 16606822400 | 0 | 1128251392 | 4194304 | NULL | 2019-02-09 15:35:01 | NULL | NULL | latin1_swedish_ci | NULL | row_format=COMPRESSED | |
+------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+-----------------------+---------+
1 row in set (0.00 sec)
Here is the InnoDB buffers info:
mysql> show global variables like '%innodb_buffer%';
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 8 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 42949672960 |
+-------------------------------------+----------------+
10 rows in set (0.00 sec)
Can someone provide some advice?
mysql innodb mysql-5.7
add a comment |
We experiencing a slow select on InnoDB comparing to MyISAM - difference for execution on MyISAM is about 2 min., on InnoDB is 2 h.
The number of rows involved is 33 M. The row set in result is about 23M.
Server:
Location: Google Cloud
Disk: 4T SSD
Memory:
[root@gcpjobrunner01 ~]# free -g
total used free shared buff/cache available
Mem: 58 44 0 0 12 13
Swap: 0 0 0
MySQL exact version:
mysql> show global variables like '%version%';
+-------------------------+----------------------------------------------------+
| Variable_name | Value |
+-------------------------+----------------------------------------------------+
| innodb_version | 5.7.24-27 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1,TLSv1.2 |
| version | 5.7.24-27-log |
| version_comment | Percona Server (GPL), Release 27, Revision bd42700 |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
| version_suffix | -log |
+-------------------------+----------------------------------------------------+
The DDL of the relevant table:
CREATE TABLE `account_balances` (
`abl_aad_id` double NOT NULL,
`abl_txn_type_cd` varchar(8) NOT NULL,
`abl_prm_code` varchar(30) NOT NULL,
`abl_bal_sort` double NOT NULL DEFAULT '1',
`abl_bal_abl` double NOT NULL,
`abl_bal` double NOT NULL,
`abl_bal_pd` double NOT NULL,
`abl_bal_waived` double NOT NULL,
`abl_bal_chgoff` double NOT NULL DEFAULT '0',
`abl_bal_recovered` double NOT NULL DEFAULT '0',
`abl_bal_adjusted_plus` double NOT NULL DEFAULT '0',
`abl_bal_adjusted_minus` double NOT NULL DEFAULT '0',
`abl_bal_open_ctd` double NOT NULL DEFAULT '0',
`abl_bal_ctd` double NOT NULL,
`abl_bal_pd_ctd` double NOT NULL,
`abl_bal_waived_ctd` double NOT NULL,
`abl_bal_chgoff_open_ctd` double NOT NULL,
`abl_bal_chgoff_ctd` double NOT NULL,
`abl_bal_recovered_ctd` double NOT NULL,
`abl_bal_adjusted_plus_ctd` double NOT NULL,
`abl_bal_adjusted_minus_ctd` double NOT NULL,
`abl_bal_open_ytd` double NOT NULL,
`abl_bal_ytd` double NOT NULL,
`abl_bal_pd_ytd` double NOT NULL,
`abl_bal_waived_ytd` double NOT NULL,
`abl_bal_chgoff_open_ytd` double NOT NULL,
`abl_bal_chgoff_ytd` double NOT NULL,
`abl_bal_recovered_ytd` double NOT NULL,
`abl_bal_adjusted_plus_ytd` double NOT NULL,
`abl_bal_adjusted_minus_ytd` double NOT NULL,
`abl_bal_billed_ind` varchar(30) NOT NULL,
`abl_interest_accrued_ind` varchar(30) NOT NULL DEFAULT 'y',
`abl_chargeoff_method_cd` varchar(30) NOT NULL,
`abl_reschedule_method_cd` varchar(30) NOT NULL,
`abl_writeoff_method_cd` varchar(30) NOT NULL,
`abl_bal_open_ctd1` double NOT NULL DEFAULT '0',
`abl_bal_ctd1` double NOT NULL,
`abl_bal_pd_ctd1` double NOT NULL,
`abl_bal_waived_ctd1` double NOT NULL,
`abl_bal_chgoff_open_ctd1` double NOT NULL,
`abl_bal_chgoff_ctd1` double NOT NULL,
`abl_bal_recovered_ctd1` double NOT NULL,
`abl_bal_adjusted_plus_ctd1` double NOT NULL,
`abl_bal_adjusted_minus_ctd1` double NOT NULL,
`abl_bal_open_ytd1` double NOT NULL,
`abl_bal_ytd1` double NOT NULL,
`abl_bal_pd_ytd1` double NOT NULL,
`abl_bal_waived_ytd1` double NOT NULL,
`abl_bal_chgoff_open_ytd1` double NOT NULL,
`abl_bal_chgoff_ytd1` double NOT NULL DEFAULT '0',
`abl_bal_recovered_ytd1` double NOT NULL DEFAULT '0',
`abl_bal_adjusted_plus_ytd1` double NOT NULL DEFAULT '0',
`abl_bal_adjusted_minus_ytd1` double NOT NULL DEFAULT '0',
`abl_bill_method_cd` varchar(30) NOT NULL DEFAULT 'lv',
`abl_pmt_amt` double NOT NULL,
`abl_pmt_per` double NOT NULL DEFAULT '0',
`abl_dt` datetime NOT NULL,
`abl_prom_type_cd` varchar(30) NOT NULL,
`abl_rate` double NOT NULL,
`abl_term` double NOT NULL,
`abl_prm_end_dt` datetime NOT NULL,
`abl_due_amt1` double NOT NULL,
`abl_due_amt2` double NOT NULL,
`abl_due_amt3` double NOT NULL,
`abl_due_amt4` double NOT NULL DEFAULT '0',
`abl_due_amt5` double NOT NULL,
`abl_due_amt_pd1` double NOT NULL,
`abl_due_amt_pd2` double NOT NULL,
`abl_due_amt_pd3` double NOT NULL,
`abl_due_amt_pd4` double NOT NULL,
`abl_due_amt_pd5` double NOT NULL,
`abl_pin_code` varchar(30) NOT NULL DEFAULT 'undefined',
`abl_ins_sub_type_cd` varchar(30) NOT NULL DEFAULT 'undefined',
`abl_ins_status_cd` varchar(30) NOT NULL,
`abl_acc_orig_sys_xref` varchar(30) NOT NULL,
`created_by` varchar(30) NOT NULL,
`creation_date` datetime NOT NULL,
`last_updated_by` varchar(30) NOT NULL,
`last_update_date` datetime NOT NULL,
`abl_bal_terminate` double NOT NULL,
`abl_bal_terminate_ctd` double NOT NULL,
`abl_bal_terminate_ytd` double NOT NULL,
`abl_bal_terminate_ctd1` double NOT NULL,
`abl_bal_terminate_ytd1` double NOT NULL,
`abl_terminate_ind` varchar(30) NOT NULL,
`abl_bal_abl_ctd1` double NOT NULL,
`abl_bal_abl_ctd2` double NOT NULL,
`abl_bal_abl_ctd3` double NOT NULL,
`abl_bal_abl_ctd4` double NOT NULL DEFAULT '0',
`abl_bal_abl_ctd5` double NOT NULL DEFAULT '0',
`abl_non_perform_roll_ind` varchar(30) NOT NULL,
`abl_non_perform_txn_type_cd` varchar(30) NOT NULL,
`abl_bal_xfer2non_perform` double NOT NULL,
`abl_bal_pd_xfer2non_perform` double NOT NULL,
`abl_non_perform_bal` double NOT NULL,
`abl_non_perform_bal_pd` double NOT NULL,
`abl_non_perform_bal_pd_excess` double NOT NULL,
`abl_non_perform_bal_waived` double NOT NULL,
`abl_non_perform_bal_adj_plus` double NOT NULL,
`abl_non_perform_bal_adj_minus` double NOT NULL,
`abl_bal_billed` double NOT NULL,
`abl_bal_billed_ctd` double NOT NULL,
`abl_bal_billed_ctd1` double NOT NULL,
`abl_bal_billed_ytd` double NOT NULL,
UNIQUE KEY `account_balances_abl_udx` (`abl_aad_id`,`abl_txn_type_cd`,`abl_prm_code`),
KEY `abl_txn_type_cd` (`abl_txn_type_cd`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
The DDL is the same for the query run on MyISAM.
Here is the query:
SELECT
abl_aad_id,
abl_txn_type_cd,
abl_bal_chgoff,
abl_bal_recovered,
abl_bal,
abl_bal_pd,
abl_bal_waived,
abl_bal_adjusted_minus,
abl_bal_adjusted_plus
FROM
account_balances
WHERE abl_txn_type_cd IN ('ADV','INT','FLC','FNSF','FEXT','FOTH1','FPHP','EBKR','ERPO','ESVC','EOTH1','ADV');
Here is the actual SQL from SP:
SELECT NOW(), 'Creating tmp_mv_account_balances';
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));
DROP TABLE IF EXISTS tmp_mv_account_balances;
CREATE TABLE tmp_mv_account_balances AS
SELECT acc_nbr AS "acc_nbr_bal",
abl_aad_id AS "aad_id_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'ADV' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus END ELSE 0 END ) , 2) AS "advance_principal_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'INT' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus END ELSE 0 END ) , 2) AS "interest_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'FLC' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus END ELSE 0 END ) , 2) AS "fee_late_charge_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'FNSF' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus END ELSE 0 END ) , 2) AS "fee_nsf_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'FEXT' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus END ELSE 0 END ) , 2) AS "fee_extension_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'FOTH1' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus END ELSE 0 END ) , 2) AS "fee_convenience_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'FPHP' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus END ELSE 0 END ) , 2) AS "fee_phone_pay_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'EBKR' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus END ELSE 0 END ) , 2) AS "expense_legal_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'ERPO' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus END ELSE 0 END ) , 2) AS "expense_repo_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'ESVC' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus END ELSE 0 END ) , 2) AS "expense_dmv_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'EOTH1' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus END ELSE 0 END ) , 2) AS "expense_deferred_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'ADV' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus - acc_int_rebate_amt END ELSE 0 END ) , 2) AS "net_advance_principal_bal",
acc_int_rebate_amt AS "int_rebate_bal"
FROM
account_balances
JOIN accounts ON abl_aad_id = acc_aad_id
GROUP BY
abl_aad_id;
SELECT NOW(), 'Creating indexes';
ALTER TABLE tmp_mv_account_balances ADD INDEX idx01_bal ( acc_nbr_bal );
ALTER TABLE tmp_mv_account_balances ADD INDEX idx02_bal ( aad_id_bal );
MyISAM is returning the result in 2 min., but in InnoDB it is taking "forever".
Explain plan for InnoDB:
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ---------------- ---------- ------ --------------- ------ ------- ------ -------- -------- -------------
1 SIMPLE account_balances (NULL) ALL abl_txn_type_cd (NULL) (NULL) (NULL) 28858313 100.00 Using where
Explain plan in MyISAM:
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ---------------- ---------- ------ ------------- ------ ------- ------ -------- -------- -------------
1 SIMPLE account_balances (NULL) ALL (NULL) (NULL) (NULL) (NULL) 33628821 50.00 Using where
Here is explain plan with force index (abl_txn_type_cd)
for InnoDB, we dont have that index in MyISAM, but it does not matter - the SELECT is running forever in InnoDB.
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ---------------- ---------- ------ --------------- --------------- ------- ------ -------- -------- -----------------------
1 SIMPLE account_balances (NULL) range abl_txn_type_cd abl_txn_type_cd 10 (NULL) 40672694 100.00 Using index condition
Here is SHOW TABLE STATUS
InnoDB:
mysql> show table status like 'account_balances';
+------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+-----------------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+-----------------------+---------+
| account_balances | InnoDB | 10 | Compressed | 31858884 | 521 | 16606822400 | 0 | 1128251392 | 4194304 | NULL | 2019-02-09 15:35:01 | NULL | NULL | latin1_swedish_ci | NULL | row_format=COMPRESSED | |
+------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+-----------------------+---------+
1 row in set (0.00 sec)
Here is the InnoDB buffers info:
mysql> show global variables like '%innodb_buffer%';
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 8 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 42949672960 |
+-------------------------------------+----------------+
10 rows in set (0.00 sec)
Can someone provide some advice?
mysql innodb mysql-5.7
1
@varnar - what were the values of both of these in both cases:key_buffer_size
andinnodb_buffer_pool_size
. Note: they compete for RAM, and should be adjusted based on which Engine is dominant.
– Rick James
Feb 9 at 23:54
add a comment |
We experiencing a slow select on InnoDB comparing to MyISAM - difference for execution on MyISAM is about 2 min., on InnoDB is 2 h.
The number of rows involved is 33 M. The row set in result is about 23M.
Server:
Location: Google Cloud
Disk: 4T SSD
Memory:
[root@gcpjobrunner01 ~]# free -g
total used free shared buff/cache available
Mem: 58 44 0 0 12 13
Swap: 0 0 0
MySQL exact version:
mysql> show global variables like '%version%';
+-------------------------+----------------------------------------------------+
| Variable_name | Value |
+-------------------------+----------------------------------------------------+
| innodb_version | 5.7.24-27 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1,TLSv1.2 |
| version | 5.7.24-27-log |
| version_comment | Percona Server (GPL), Release 27, Revision bd42700 |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
| version_suffix | -log |
+-------------------------+----------------------------------------------------+
The DDL of the relevant table:
CREATE TABLE `account_balances` (
`abl_aad_id` double NOT NULL,
`abl_txn_type_cd` varchar(8) NOT NULL,
`abl_prm_code` varchar(30) NOT NULL,
`abl_bal_sort` double NOT NULL DEFAULT '1',
`abl_bal_abl` double NOT NULL,
`abl_bal` double NOT NULL,
`abl_bal_pd` double NOT NULL,
`abl_bal_waived` double NOT NULL,
`abl_bal_chgoff` double NOT NULL DEFAULT '0',
`abl_bal_recovered` double NOT NULL DEFAULT '0',
`abl_bal_adjusted_plus` double NOT NULL DEFAULT '0',
`abl_bal_adjusted_minus` double NOT NULL DEFAULT '0',
`abl_bal_open_ctd` double NOT NULL DEFAULT '0',
`abl_bal_ctd` double NOT NULL,
`abl_bal_pd_ctd` double NOT NULL,
`abl_bal_waived_ctd` double NOT NULL,
`abl_bal_chgoff_open_ctd` double NOT NULL,
`abl_bal_chgoff_ctd` double NOT NULL,
`abl_bal_recovered_ctd` double NOT NULL,
`abl_bal_adjusted_plus_ctd` double NOT NULL,
`abl_bal_adjusted_minus_ctd` double NOT NULL,
`abl_bal_open_ytd` double NOT NULL,
`abl_bal_ytd` double NOT NULL,
`abl_bal_pd_ytd` double NOT NULL,
`abl_bal_waived_ytd` double NOT NULL,
`abl_bal_chgoff_open_ytd` double NOT NULL,
`abl_bal_chgoff_ytd` double NOT NULL,
`abl_bal_recovered_ytd` double NOT NULL,
`abl_bal_adjusted_plus_ytd` double NOT NULL,
`abl_bal_adjusted_minus_ytd` double NOT NULL,
`abl_bal_billed_ind` varchar(30) NOT NULL,
`abl_interest_accrued_ind` varchar(30) NOT NULL DEFAULT 'y',
`abl_chargeoff_method_cd` varchar(30) NOT NULL,
`abl_reschedule_method_cd` varchar(30) NOT NULL,
`abl_writeoff_method_cd` varchar(30) NOT NULL,
`abl_bal_open_ctd1` double NOT NULL DEFAULT '0',
`abl_bal_ctd1` double NOT NULL,
`abl_bal_pd_ctd1` double NOT NULL,
`abl_bal_waived_ctd1` double NOT NULL,
`abl_bal_chgoff_open_ctd1` double NOT NULL,
`abl_bal_chgoff_ctd1` double NOT NULL,
`abl_bal_recovered_ctd1` double NOT NULL,
`abl_bal_adjusted_plus_ctd1` double NOT NULL,
`abl_bal_adjusted_minus_ctd1` double NOT NULL,
`abl_bal_open_ytd1` double NOT NULL,
`abl_bal_ytd1` double NOT NULL,
`abl_bal_pd_ytd1` double NOT NULL,
`abl_bal_waived_ytd1` double NOT NULL,
`abl_bal_chgoff_open_ytd1` double NOT NULL,
`abl_bal_chgoff_ytd1` double NOT NULL DEFAULT '0',
`abl_bal_recovered_ytd1` double NOT NULL DEFAULT '0',
`abl_bal_adjusted_plus_ytd1` double NOT NULL DEFAULT '0',
`abl_bal_adjusted_minus_ytd1` double NOT NULL DEFAULT '0',
`abl_bill_method_cd` varchar(30) NOT NULL DEFAULT 'lv',
`abl_pmt_amt` double NOT NULL,
`abl_pmt_per` double NOT NULL DEFAULT '0',
`abl_dt` datetime NOT NULL,
`abl_prom_type_cd` varchar(30) NOT NULL,
`abl_rate` double NOT NULL,
`abl_term` double NOT NULL,
`abl_prm_end_dt` datetime NOT NULL,
`abl_due_amt1` double NOT NULL,
`abl_due_amt2` double NOT NULL,
`abl_due_amt3` double NOT NULL,
`abl_due_amt4` double NOT NULL DEFAULT '0',
`abl_due_amt5` double NOT NULL,
`abl_due_amt_pd1` double NOT NULL,
`abl_due_amt_pd2` double NOT NULL,
`abl_due_amt_pd3` double NOT NULL,
`abl_due_amt_pd4` double NOT NULL,
`abl_due_amt_pd5` double NOT NULL,
`abl_pin_code` varchar(30) NOT NULL DEFAULT 'undefined',
`abl_ins_sub_type_cd` varchar(30) NOT NULL DEFAULT 'undefined',
`abl_ins_status_cd` varchar(30) NOT NULL,
`abl_acc_orig_sys_xref` varchar(30) NOT NULL,
`created_by` varchar(30) NOT NULL,
`creation_date` datetime NOT NULL,
`last_updated_by` varchar(30) NOT NULL,
`last_update_date` datetime NOT NULL,
`abl_bal_terminate` double NOT NULL,
`abl_bal_terminate_ctd` double NOT NULL,
`abl_bal_terminate_ytd` double NOT NULL,
`abl_bal_terminate_ctd1` double NOT NULL,
`abl_bal_terminate_ytd1` double NOT NULL,
`abl_terminate_ind` varchar(30) NOT NULL,
`abl_bal_abl_ctd1` double NOT NULL,
`abl_bal_abl_ctd2` double NOT NULL,
`abl_bal_abl_ctd3` double NOT NULL,
`abl_bal_abl_ctd4` double NOT NULL DEFAULT '0',
`abl_bal_abl_ctd5` double NOT NULL DEFAULT '0',
`abl_non_perform_roll_ind` varchar(30) NOT NULL,
`abl_non_perform_txn_type_cd` varchar(30) NOT NULL,
`abl_bal_xfer2non_perform` double NOT NULL,
`abl_bal_pd_xfer2non_perform` double NOT NULL,
`abl_non_perform_bal` double NOT NULL,
`abl_non_perform_bal_pd` double NOT NULL,
`abl_non_perform_bal_pd_excess` double NOT NULL,
`abl_non_perform_bal_waived` double NOT NULL,
`abl_non_perform_bal_adj_plus` double NOT NULL,
`abl_non_perform_bal_adj_minus` double NOT NULL,
`abl_bal_billed` double NOT NULL,
`abl_bal_billed_ctd` double NOT NULL,
`abl_bal_billed_ctd1` double NOT NULL,
`abl_bal_billed_ytd` double NOT NULL,
UNIQUE KEY `account_balances_abl_udx` (`abl_aad_id`,`abl_txn_type_cd`,`abl_prm_code`),
KEY `abl_txn_type_cd` (`abl_txn_type_cd`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
The DDL is the same for the query run on MyISAM.
Here is the query:
SELECT
abl_aad_id,
abl_txn_type_cd,
abl_bal_chgoff,
abl_bal_recovered,
abl_bal,
abl_bal_pd,
abl_bal_waived,
abl_bal_adjusted_minus,
abl_bal_adjusted_plus
FROM
account_balances
WHERE abl_txn_type_cd IN ('ADV','INT','FLC','FNSF','FEXT','FOTH1','FPHP','EBKR','ERPO','ESVC','EOTH1','ADV');
Here is the actual SQL from SP:
SELECT NOW(), 'Creating tmp_mv_account_balances';
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));
DROP TABLE IF EXISTS tmp_mv_account_balances;
CREATE TABLE tmp_mv_account_balances AS
SELECT acc_nbr AS "acc_nbr_bal",
abl_aad_id AS "aad_id_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'ADV' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus END ELSE 0 END ) , 2) AS "advance_principal_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'INT' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus END ELSE 0 END ) , 2) AS "interest_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'FLC' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus END ELSE 0 END ) , 2) AS "fee_late_charge_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'FNSF' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus END ELSE 0 END ) , 2) AS "fee_nsf_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'FEXT' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus END ELSE 0 END ) , 2) AS "fee_extension_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'FOTH1' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus END ELSE 0 END ) , 2) AS "fee_convenience_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'FPHP' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus END ELSE 0 END ) , 2) AS "fee_phone_pay_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'EBKR' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus END ELSE 0 END ) , 2) AS "expense_legal_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'ERPO' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus END ELSE 0 END ) , 2) AS "expense_repo_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'ESVC' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus END ELSE 0 END ) , 2) AS "expense_dmv_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'EOTH1' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus END ELSE 0 END ) , 2) AS "expense_deferred_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'ADV' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus - acc_int_rebate_amt END ELSE 0 END ) , 2) AS "net_advance_principal_bal",
acc_int_rebate_amt AS "int_rebate_bal"
FROM
account_balances
JOIN accounts ON abl_aad_id = acc_aad_id
GROUP BY
abl_aad_id;
SELECT NOW(), 'Creating indexes';
ALTER TABLE tmp_mv_account_balances ADD INDEX idx01_bal ( acc_nbr_bal );
ALTER TABLE tmp_mv_account_balances ADD INDEX idx02_bal ( aad_id_bal );
MyISAM is returning the result in 2 min., but in InnoDB it is taking "forever".
Explain plan for InnoDB:
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ---------------- ---------- ------ --------------- ------ ------- ------ -------- -------- -------------
1 SIMPLE account_balances (NULL) ALL abl_txn_type_cd (NULL) (NULL) (NULL) 28858313 100.00 Using where
Explain plan in MyISAM:
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ---------------- ---------- ------ ------------- ------ ------- ------ -------- -------- -------------
1 SIMPLE account_balances (NULL) ALL (NULL) (NULL) (NULL) (NULL) 33628821 50.00 Using where
Here is explain plan with force index (abl_txn_type_cd)
for InnoDB, we dont have that index in MyISAM, but it does not matter - the SELECT is running forever in InnoDB.
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ---------------- ---------- ------ --------------- --------------- ------- ------ -------- -------- -----------------------
1 SIMPLE account_balances (NULL) range abl_txn_type_cd abl_txn_type_cd 10 (NULL) 40672694 100.00 Using index condition
Here is SHOW TABLE STATUS
InnoDB:
mysql> show table status like 'account_balances';
+------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+-----------------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+-----------------------+---------+
| account_balances | InnoDB | 10 | Compressed | 31858884 | 521 | 16606822400 | 0 | 1128251392 | 4194304 | NULL | 2019-02-09 15:35:01 | NULL | NULL | latin1_swedish_ci | NULL | row_format=COMPRESSED | |
+------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+-----------------------+---------+
1 row in set (0.00 sec)
Here is the InnoDB buffers info:
mysql> show global variables like '%innodb_buffer%';
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 8 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 42949672960 |
+-------------------------------------+----------------+
10 rows in set (0.00 sec)
Can someone provide some advice?
mysql innodb mysql-5.7
We experiencing a slow select on InnoDB comparing to MyISAM - difference for execution on MyISAM is about 2 min., on InnoDB is 2 h.
The number of rows involved is 33 M. The row set in result is about 23M.
Server:
Location: Google Cloud
Disk: 4T SSD
Memory:
[root@gcpjobrunner01 ~]# free -g
total used free shared buff/cache available
Mem: 58 44 0 0 12 13
Swap: 0 0 0
MySQL exact version:
mysql> show global variables like '%version%';
+-------------------------+----------------------------------------------------+
| Variable_name | Value |
+-------------------------+----------------------------------------------------+
| innodb_version | 5.7.24-27 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1,TLSv1.2 |
| version | 5.7.24-27-log |
| version_comment | Percona Server (GPL), Release 27, Revision bd42700 |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
| version_suffix | -log |
+-------------------------+----------------------------------------------------+
The DDL of the relevant table:
CREATE TABLE `account_balances` (
`abl_aad_id` double NOT NULL,
`abl_txn_type_cd` varchar(8) NOT NULL,
`abl_prm_code` varchar(30) NOT NULL,
`abl_bal_sort` double NOT NULL DEFAULT '1',
`abl_bal_abl` double NOT NULL,
`abl_bal` double NOT NULL,
`abl_bal_pd` double NOT NULL,
`abl_bal_waived` double NOT NULL,
`abl_bal_chgoff` double NOT NULL DEFAULT '0',
`abl_bal_recovered` double NOT NULL DEFAULT '0',
`abl_bal_adjusted_plus` double NOT NULL DEFAULT '0',
`abl_bal_adjusted_minus` double NOT NULL DEFAULT '0',
`abl_bal_open_ctd` double NOT NULL DEFAULT '0',
`abl_bal_ctd` double NOT NULL,
`abl_bal_pd_ctd` double NOT NULL,
`abl_bal_waived_ctd` double NOT NULL,
`abl_bal_chgoff_open_ctd` double NOT NULL,
`abl_bal_chgoff_ctd` double NOT NULL,
`abl_bal_recovered_ctd` double NOT NULL,
`abl_bal_adjusted_plus_ctd` double NOT NULL,
`abl_bal_adjusted_minus_ctd` double NOT NULL,
`abl_bal_open_ytd` double NOT NULL,
`abl_bal_ytd` double NOT NULL,
`abl_bal_pd_ytd` double NOT NULL,
`abl_bal_waived_ytd` double NOT NULL,
`abl_bal_chgoff_open_ytd` double NOT NULL,
`abl_bal_chgoff_ytd` double NOT NULL,
`abl_bal_recovered_ytd` double NOT NULL,
`abl_bal_adjusted_plus_ytd` double NOT NULL,
`abl_bal_adjusted_minus_ytd` double NOT NULL,
`abl_bal_billed_ind` varchar(30) NOT NULL,
`abl_interest_accrued_ind` varchar(30) NOT NULL DEFAULT 'y',
`abl_chargeoff_method_cd` varchar(30) NOT NULL,
`abl_reschedule_method_cd` varchar(30) NOT NULL,
`abl_writeoff_method_cd` varchar(30) NOT NULL,
`abl_bal_open_ctd1` double NOT NULL DEFAULT '0',
`abl_bal_ctd1` double NOT NULL,
`abl_bal_pd_ctd1` double NOT NULL,
`abl_bal_waived_ctd1` double NOT NULL,
`abl_bal_chgoff_open_ctd1` double NOT NULL,
`abl_bal_chgoff_ctd1` double NOT NULL,
`abl_bal_recovered_ctd1` double NOT NULL,
`abl_bal_adjusted_plus_ctd1` double NOT NULL,
`abl_bal_adjusted_minus_ctd1` double NOT NULL,
`abl_bal_open_ytd1` double NOT NULL,
`abl_bal_ytd1` double NOT NULL,
`abl_bal_pd_ytd1` double NOT NULL,
`abl_bal_waived_ytd1` double NOT NULL,
`abl_bal_chgoff_open_ytd1` double NOT NULL,
`abl_bal_chgoff_ytd1` double NOT NULL DEFAULT '0',
`abl_bal_recovered_ytd1` double NOT NULL DEFAULT '0',
`abl_bal_adjusted_plus_ytd1` double NOT NULL DEFAULT '0',
`abl_bal_adjusted_minus_ytd1` double NOT NULL DEFAULT '0',
`abl_bill_method_cd` varchar(30) NOT NULL DEFAULT 'lv',
`abl_pmt_amt` double NOT NULL,
`abl_pmt_per` double NOT NULL DEFAULT '0',
`abl_dt` datetime NOT NULL,
`abl_prom_type_cd` varchar(30) NOT NULL,
`abl_rate` double NOT NULL,
`abl_term` double NOT NULL,
`abl_prm_end_dt` datetime NOT NULL,
`abl_due_amt1` double NOT NULL,
`abl_due_amt2` double NOT NULL,
`abl_due_amt3` double NOT NULL,
`abl_due_amt4` double NOT NULL DEFAULT '0',
`abl_due_amt5` double NOT NULL,
`abl_due_amt_pd1` double NOT NULL,
`abl_due_amt_pd2` double NOT NULL,
`abl_due_amt_pd3` double NOT NULL,
`abl_due_amt_pd4` double NOT NULL,
`abl_due_amt_pd5` double NOT NULL,
`abl_pin_code` varchar(30) NOT NULL DEFAULT 'undefined',
`abl_ins_sub_type_cd` varchar(30) NOT NULL DEFAULT 'undefined',
`abl_ins_status_cd` varchar(30) NOT NULL,
`abl_acc_orig_sys_xref` varchar(30) NOT NULL,
`created_by` varchar(30) NOT NULL,
`creation_date` datetime NOT NULL,
`last_updated_by` varchar(30) NOT NULL,
`last_update_date` datetime NOT NULL,
`abl_bal_terminate` double NOT NULL,
`abl_bal_terminate_ctd` double NOT NULL,
`abl_bal_terminate_ytd` double NOT NULL,
`abl_bal_terminate_ctd1` double NOT NULL,
`abl_bal_terminate_ytd1` double NOT NULL,
`abl_terminate_ind` varchar(30) NOT NULL,
`abl_bal_abl_ctd1` double NOT NULL,
`abl_bal_abl_ctd2` double NOT NULL,
`abl_bal_abl_ctd3` double NOT NULL,
`abl_bal_abl_ctd4` double NOT NULL DEFAULT '0',
`abl_bal_abl_ctd5` double NOT NULL DEFAULT '0',
`abl_non_perform_roll_ind` varchar(30) NOT NULL,
`abl_non_perform_txn_type_cd` varchar(30) NOT NULL,
`abl_bal_xfer2non_perform` double NOT NULL,
`abl_bal_pd_xfer2non_perform` double NOT NULL,
`abl_non_perform_bal` double NOT NULL,
`abl_non_perform_bal_pd` double NOT NULL,
`abl_non_perform_bal_pd_excess` double NOT NULL,
`abl_non_perform_bal_waived` double NOT NULL,
`abl_non_perform_bal_adj_plus` double NOT NULL,
`abl_non_perform_bal_adj_minus` double NOT NULL,
`abl_bal_billed` double NOT NULL,
`abl_bal_billed_ctd` double NOT NULL,
`abl_bal_billed_ctd1` double NOT NULL,
`abl_bal_billed_ytd` double NOT NULL,
UNIQUE KEY `account_balances_abl_udx` (`abl_aad_id`,`abl_txn_type_cd`,`abl_prm_code`),
KEY `abl_txn_type_cd` (`abl_txn_type_cd`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
The DDL is the same for the query run on MyISAM.
Here is the query:
SELECT
abl_aad_id,
abl_txn_type_cd,
abl_bal_chgoff,
abl_bal_recovered,
abl_bal,
abl_bal_pd,
abl_bal_waived,
abl_bal_adjusted_minus,
abl_bal_adjusted_plus
FROM
account_balances
WHERE abl_txn_type_cd IN ('ADV','INT','FLC','FNSF','FEXT','FOTH1','FPHP','EBKR','ERPO','ESVC','EOTH1','ADV');
Here is the actual SQL from SP:
SELECT NOW(), 'Creating tmp_mv_account_balances';
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));
DROP TABLE IF EXISTS tmp_mv_account_balances;
CREATE TABLE tmp_mv_account_balances AS
SELECT acc_nbr AS "acc_nbr_bal",
abl_aad_id AS "aad_id_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'ADV' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus END ELSE 0 END ) , 2) AS "advance_principal_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'INT' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus END ELSE 0 END ) , 2) AS "interest_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'FLC' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus END ELSE 0 END ) , 2) AS "fee_late_charge_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'FNSF' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus END ELSE 0 END ) , 2) AS "fee_nsf_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'FEXT' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus END ELSE 0 END ) , 2) AS "fee_extension_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'FOTH1' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus END ELSE 0 END ) , 2) AS "fee_convenience_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'FPHP' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus END ELSE 0 END ) , 2) AS "fee_phone_pay_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'EBKR' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus END ELSE 0 END ) , 2) AS "expense_legal_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'ERPO' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus END ELSE 0 END ) , 2) AS "expense_repo_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'ESVC' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus END ELSE 0 END ) , 2) AS "expense_dmv_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'EOTH1' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus END ELSE 0 END ) , 2) AS "expense_deferred_bal",
ROUND(SUM( CASE WHEN abl_txn_type_cd = 'ADV' THEN CASE WHEN acc_status_cd = 'CHGOFF' THEN abl_bal_chgoff - abl_bal_recovered ELSE abl_bal - abl_bal_pd - abl_bal_waived - abl_bal_adjusted_minus + abl_bal_adjusted_plus - acc_int_rebate_amt END ELSE 0 END ) , 2) AS "net_advance_principal_bal",
acc_int_rebate_amt AS "int_rebate_bal"
FROM
account_balances
JOIN accounts ON abl_aad_id = acc_aad_id
GROUP BY
abl_aad_id;
SELECT NOW(), 'Creating indexes';
ALTER TABLE tmp_mv_account_balances ADD INDEX idx01_bal ( acc_nbr_bal );
ALTER TABLE tmp_mv_account_balances ADD INDEX idx02_bal ( aad_id_bal );
MyISAM is returning the result in 2 min., but in InnoDB it is taking "forever".
Explain plan for InnoDB:
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ---------------- ---------- ------ --------------- ------ ------- ------ -------- -------- -------------
1 SIMPLE account_balances (NULL) ALL abl_txn_type_cd (NULL) (NULL) (NULL) 28858313 100.00 Using where
Explain plan in MyISAM:
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ---------------- ---------- ------ ------------- ------ ------- ------ -------- -------- -------------
1 SIMPLE account_balances (NULL) ALL (NULL) (NULL) (NULL) (NULL) 33628821 50.00 Using where
Here is explain plan with force index (abl_txn_type_cd)
for InnoDB, we dont have that index in MyISAM, but it does not matter - the SELECT is running forever in InnoDB.
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ---------------- ---------- ------ --------------- --------------- ------- ------ -------- -------- -----------------------
1 SIMPLE account_balances (NULL) range abl_txn_type_cd abl_txn_type_cd 10 (NULL) 40672694 100.00 Using index condition
Here is SHOW TABLE STATUS
InnoDB:
mysql> show table status like 'account_balances';
+------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+-----------------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+-----------------------+---------+
| account_balances | InnoDB | 10 | Compressed | 31858884 | 521 | 16606822400 | 0 | 1128251392 | 4194304 | NULL | 2019-02-09 15:35:01 | NULL | NULL | latin1_swedish_ci | NULL | row_format=COMPRESSED | |
+------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+-----------------------+---------+
1 row in set (0.00 sec)
Here is the InnoDB buffers info:
mysql> show global variables like '%innodb_buffer%';
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 8 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 42949672960 |
+-------------------------------------+----------------+
10 rows in set (0.00 sec)
Can someone provide some advice?
mysql innodb mysql-5.7
mysql innodb mysql-5.7
edited Feb 10 at 5:48
Paul White♦
52.2k14279452
52.2k14279452
asked Feb 9 at 18:16
varnarvarnar
113
113
1
@varnar - what were the values of both of these in both cases:key_buffer_size
andinnodb_buffer_pool_size
. Note: they compete for RAM, and should be adjusted based on which Engine is dominant.
– Rick James
Feb 9 at 23:54
add a comment |
1
@varnar - what were the values of both of these in both cases:key_buffer_size
andinnodb_buffer_pool_size
. Note: they compete for RAM, and should be adjusted based on which Engine is dominant.
– Rick James
Feb 9 at 23:54
1
1
@varnar - what were the values of both of these in both cases:
key_buffer_size
and innodb_buffer_pool_size
. Note: they compete for RAM, and should be adjusted based on which Engine is dominant.– Rick James
Feb 9 at 23:54
@varnar - what were the values of both of these in both cases:
key_buffer_size
and innodb_buffer_pool_size
. Note: they compete for RAM, and should be adjusted based on which Engine is dominant.– Rick James
Feb 9 at 23:54
add a comment |
1 Answer
1
active
oldest
votes
Don't use DOUBLE
(or FLOAT
) for money, you could get rounding errors due to the back and forth between binary and decimal. Instead, pick suitable, but not excessive, values for m,n
in DECIMAL(m,n)
.
33M rows, each about 1KB in size. That adds up to about enough to fill the buffer_pool (after allowing for other things allocated in it.) This implies that a table scan (which you are doing) will bump most stuff out of the buffer_pool and be I/O bound. Update: TABLE STATUS
shows 17GB in the InnoDB version of the table; that is too small a fraction of the 40G buffer_pool for the following to take effect.
When you run the query again, the same thing will happen -- I/O bound and bump everything out.
Please provide SHOW TABLE STATUS
for both the MyISAM and InnoDB cases.
OK, why did MyISAM work better?...
MyISAM is tighter when it comes to the size of a row; I suspect that it is half the size. So... But first, let's discuss the memory usage of MyISAM. The "key_buffer" holds index blocks only. The data blocks are managed by the OS. Assuming that you did not have any excessive memory usage in the MyISAM timing test, then the OS probably had room to keep all the data blocks in RAM. This means that a second run of the same query will be fast, and not I/O bound.
But... If you double the size of the MyISAM table, it, too, will suddenly take hours instead of minutes.
That is, you are falling off a cliff. But InnoDB, due to its allocation mechanisms, will fall off sooner.
So, what to do??
- Shrink the size of the table.
DECIMAL(11, 2)
takes 6 bytes and handles up to a billion dollars/euros/yen/etc., as opposed to 8 bytes for eachDOUBLE
. - Break up the table (vertical partitioning).
- Build and maintain a Summary Table.
- Get more ram, and more ram and ...
- (My favorite) Rethink the need for the query. If, as the
EXPLAIN
estimates, it is returning 28M rows, what the heck will you do with that ton of data? If the client will further process it, perhaps SQL could do more processing? - Etc. Explain more of your application; there are probably helpful clues.
Comments are not for extended discussion; this conversation has been moved to chat.
– Paul White♦
Feb 10 at 5:44
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f229315%2fselect-execution-is-slow-on-innodb-when-compared-to-myisam%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Don't use DOUBLE
(or FLOAT
) for money, you could get rounding errors due to the back and forth between binary and decimal. Instead, pick suitable, but not excessive, values for m,n
in DECIMAL(m,n)
.
33M rows, each about 1KB in size. That adds up to about enough to fill the buffer_pool (after allowing for other things allocated in it.) This implies that a table scan (which you are doing) will bump most stuff out of the buffer_pool and be I/O bound. Update: TABLE STATUS
shows 17GB in the InnoDB version of the table; that is too small a fraction of the 40G buffer_pool for the following to take effect.
When you run the query again, the same thing will happen -- I/O bound and bump everything out.
Please provide SHOW TABLE STATUS
for both the MyISAM and InnoDB cases.
OK, why did MyISAM work better?...
MyISAM is tighter when it comes to the size of a row; I suspect that it is half the size. So... But first, let's discuss the memory usage of MyISAM. The "key_buffer" holds index blocks only. The data blocks are managed by the OS. Assuming that you did not have any excessive memory usage in the MyISAM timing test, then the OS probably had room to keep all the data blocks in RAM. This means that a second run of the same query will be fast, and not I/O bound.
But... If you double the size of the MyISAM table, it, too, will suddenly take hours instead of minutes.
That is, you are falling off a cliff. But InnoDB, due to its allocation mechanisms, will fall off sooner.
So, what to do??
- Shrink the size of the table.
DECIMAL(11, 2)
takes 6 bytes and handles up to a billion dollars/euros/yen/etc., as opposed to 8 bytes for eachDOUBLE
. - Break up the table (vertical partitioning).
- Build and maintain a Summary Table.
- Get more ram, and more ram and ...
- (My favorite) Rethink the need for the query. If, as the
EXPLAIN
estimates, it is returning 28M rows, what the heck will you do with that ton of data? If the client will further process it, perhaps SQL could do more processing? - Etc. Explain more of your application; there are probably helpful clues.
Comments are not for extended discussion; this conversation has been moved to chat.
– Paul White♦
Feb 10 at 5:44
add a comment |
Don't use DOUBLE
(or FLOAT
) for money, you could get rounding errors due to the back and forth between binary and decimal. Instead, pick suitable, but not excessive, values for m,n
in DECIMAL(m,n)
.
33M rows, each about 1KB in size. That adds up to about enough to fill the buffer_pool (after allowing for other things allocated in it.) This implies that a table scan (which you are doing) will bump most stuff out of the buffer_pool and be I/O bound. Update: TABLE STATUS
shows 17GB in the InnoDB version of the table; that is too small a fraction of the 40G buffer_pool for the following to take effect.
When you run the query again, the same thing will happen -- I/O bound and bump everything out.
Please provide SHOW TABLE STATUS
for both the MyISAM and InnoDB cases.
OK, why did MyISAM work better?...
MyISAM is tighter when it comes to the size of a row; I suspect that it is half the size. So... But first, let's discuss the memory usage of MyISAM. The "key_buffer" holds index blocks only. The data blocks are managed by the OS. Assuming that you did not have any excessive memory usage in the MyISAM timing test, then the OS probably had room to keep all the data blocks in RAM. This means that a second run of the same query will be fast, and not I/O bound.
But... If you double the size of the MyISAM table, it, too, will suddenly take hours instead of minutes.
That is, you are falling off a cliff. But InnoDB, due to its allocation mechanisms, will fall off sooner.
So, what to do??
- Shrink the size of the table.
DECIMAL(11, 2)
takes 6 bytes and handles up to a billion dollars/euros/yen/etc., as opposed to 8 bytes for eachDOUBLE
. - Break up the table (vertical partitioning).
- Build and maintain a Summary Table.
- Get more ram, and more ram and ...
- (My favorite) Rethink the need for the query. If, as the
EXPLAIN
estimates, it is returning 28M rows, what the heck will you do with that ton of data? If the client will further process it, perhaps SQL could do more processing? - Etc. Explain more of your application; there are probably helpful clues.
Comments are not for extended discussion; this conversation has been moved to chat.
– Paul White♦
Feb 10 at 5:44
add a comment |
Don't use DOUBLE
(or FLOAT
) for money, you could get rounding errors due to the back and forth between binary and decimal. Instead, pick suitable, but not excessive, values for m,n
in DECIMAL(m,n)
.
33M rows, each about 1KB in size. That adds up to about enough to fill the buffer_pool (after allowing for other things allocated in it.) This implies that a table scan (which you are doing) will bump most stuff out of the buffer_pool and be I/O bound. Update: TABLE STATUS
shows 17GB in the InnoDB version of the table; that is too small a fraction of the 40G buffer_pool for the following to take effect.
When you run the query again, the same thing will happen -- I/O bound and bump everything out.
Please provide SHOW TABLE STATUS
for both the MyISAM and InnoDB cases.
OK, why did MyISAM work better?...
MyISAM is tighter when it comes to the size of a row; I suspect that it is half the size. So... But first, let's discuss the memory usage of MyISAM. The "key_buffer" holds index blocks only. The data blocks are managed by the OS. Assuming that you did not have any excessive memory usage in the MyISAM timing test, then the OS probably had room to keep all the data blocks in RAM. This means that a second run of the same query will be fast, and not I/O bound.
But... If you double the size of the MyISAM table, it, too, will suddenly take hours instead of minutes.
That is, you are falling off a cliff. But InnoDB, due to its allocation mechanisms, will fall off sooner.
So, what to do??
- Shrink the size of the table.
DECIMAL(11, 2)
takes 6 bytes and handles up to a billion dollars/euros/yen/etc., as opposed to 8 bytes for eachDOUBLE
. - Break up the table (vertical partitioning).
- Build and maintain a Summary Table.
- Get more ram, and more ram and ...
- (My favorite) Rethink the need for the query. If, as the
EXPLAIN
estimates, it is returning 28M rows, what the heck will you do with that ton of data? If the client will further process it, perhaps SQL could do more processing? - Etc. Explain more of your application; there are probably helpful clues.
Don't use DOUBLE
(or FLOAT
) for money, you could get rounding errors due to the back and forth between binary and decimal. Instead, pick suitable, but not excessive, values for m,n
in DECIMAL(m,n)
.
33M rows, each about 1KB in size. That adds up to about enough to fill the buffer_pool (after allowing for other things allocated in it.) This implies that a table scan (which you are doing) will bump most stuff out of the buffer_pool and be I/O bound. Update: TABLE STATUS
shows 17GB in the InnoDB version of the table; that is too small a fraction of the 40G buffer_pool for the following to take effect.
When you run the query again, the same thing will happen -- I/O bound and bump everything out.
Please provide SHOW TABLE STATUS
for both the MyISAM and InnoDB cases.
OK, why did MyISAM work better?...
MyISAM is tighter when it comes to the size of a row; I suspect that it is half the size. So... But first, let's discuss the memory usage of MyISAM. The "key_buffer" holds index blocks only. The data blocks are managed by the OS. Assuming that you did not have any excessive memory usage in the MyISAM timing test, then the OS probably had room to keep all the data blocks in RAM. This means that a second run of the same query will be fast, and not I/O bound.
But... If you double the size of the MyISAM table, it, too, will suddenly take hours instead of minutes.
That is, you are falling off a cliff. But InnoDB, due to its allocation mechanisms, will fall off sooner.
So, what to do??
- Shrink the size of the table.
DECIMAL(11, 2)
takes 6 bytes and handles up to a billion dollars/euros/yen/etc., as opposed to 8 bytes for eachDOUBLE
. - Break up the table (vertical partitioning).
- Build and maintain a Summary Table.
- Get more ram, and more ram and ...
- (My favorite) Rethink the need for the query. If, as the
EXPLAIN
estimates, it is returning 28M rows, what the heck will you do with that ton of data? If the client will further process it, perhaps SQL could do more processing? - Etc. Explain more of your application; there are probably helpful clues.
edited Feb 10 at 0:19
answered Feb 9 at 23:35
Rick JamesRick James
42.9k22259
42.9k22259
Comments are not for extended discussion; this conversation has been moved to chat.
– Paul White♦
Feb 10 at 5:44
add a comment |
Comments are not for extended discussion; this conversation has been moved to chat.
– Paul White♦
Feb 10 at 5:44
Comments are not for extended discussion; this conversation has been moved to chat.
– Paul White♦
Feb 10 at 5:44
Comments are not for extended discussion; this conversation has been moved to chat.
– Paul White♦
Feb 10 at 5:44
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f229315%2fselect-execution-is-slow-on-innodb-when-compared-to-myisam%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
@varnar - what were the values of both of these in both cases:
key_buffer_size
andinnodb_buffer_pool_size
. Note: they compete for RAM, and should be adjusted based on which Engine is dominant.– Rick James
Feb 9 at 23:54