Mysql – SELECT execution is slow on InnoDB when compared to MyISAM

innodbMySQLmysql-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?

Best Answer

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 each DOUBLE.
  • 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.
Related Question