Simple queries taking ages to run after changing from MyISAM to InnoDB

Question

I have a quite large (300GB) table on MyISAM, which I've "converted" to InnoDB, and performance for a specific query has dropped through the floor.

When I say "converted", I can't afford to take the table offline for a few hours to run an ALTER statement, but most of the data in there is old, so I actually renamed the existing table to table_archive, then created a new one from the old one's schema. The new one has all the same indexes as the old one.

There's a particular query which used to run in under a second, which is now taking many minutes - sometimes more than 30, but the EXPLAIN still shows it only accessing a small number of rows, and still using the indexes.

The query is

SELECT il.source_domain_id, ilc.link_check_date FROM inbound_links il
INNER JOIN inbound_link_changes ilc ON il.link_id = ilc.link_id
WHERE il.site_id = 51764 AND il.index_date = 1346248502 AND ilc.change_type = 'new';

and my EXPLAIN shows:

+----+-------------+-------+------+-----------------------+---------------+---------+--------------------------+------+-------------+
| id | select_type | table | type | possible_keys         | key           | key_len | ref                      | rows | Extra       |
+----+-------------+-------+------+-----------------------+---------------+---------+--------------------------+------+-------------+
|  1 | SIMPLE      | il    | ref  | PRIMARY,idx_site_date | idx_site_date | 8       | const,const              | 2612 |             | 
|  1 | SIMPLE      | ilc   | ref  | idx_link_id           | idx_link_id   | 4       | dizzyhei1_seo.il.link_id |    1 | Using where | 
+----+-------------+-------+------+-----------------------+---------------+---------+--------------------------+------+-------------+

The inbound_links table is the one which was created as a new empty InnoDB table using the same schema as the old MyISAM version, although we have now starting populating this new table with data, so there should be results returned by this query.

Other queries are still running as normal on this server. There are 15 separate processes all running similar queries (i.e. the same query for different parameter values), but we have been running this load for many months while the table was still on MyISAM - it's a fairly meaty server, with lots of fast RAID10 SAS disks.

Any suggestions why the change of storage engine would cause such a catastrophic drop in performance?

Additional info:

CREATE TABLE `inbound_links` (
  `link_id` bigint(20) NOT NULL auto_increment,
  `site_id` int(11) NOT NULL,
  `index_date` int(11) NOT NULL,
  `source_page` varchar(512) NOT NULL,
  `source_domain_id` int(11) NOT NULL,
  `target_page` varchar(512) NOT NULL,
  `anchor_text` varchar(512) NOT NULL,
  `nofollow` tinyint(4) NOT NULL,
  `link_score` int(11) NOT NULL,
  `md5` varchar(32) NOT NULL,
  `exclude` tinyint(4) NOT NULL,
  `exclude_anchor` tinyint(4) NOT NULL,
  `old_link_id` int(11) NOT NULL,
  `redirect` tinyint(4) NOT NULL,
  `majestic_date` int(11) NOT NULL,
  PRIMARY KEY  (`link_id`),
  KEY `idx_site_date` (`site_id`,`index_date`),
  KEY `idx_old_link` (`old_link_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2147623881 DEFAULT CHARSET=utf8;

CREATE TABLE `inbound_link_changes` (
  `site_id` int(11) NOT NULL,
  `link_check_date` int(11) NOT NULL,
  `prev_link_check_date` int(11) NOT NULL,
  `change_type` varchar(10) NOT NULL,
  `link_id` int(11) NOT NULL,
  `exclude` int(11) NOT NULL default '0',
  KEY `idx_link_changes` (`site_id`,`link_check_date`),
  KEY `idx_link_id` (`link_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


+-----------------------------------------+--------------------------------------+
| Variable_name                           | Value                                |
+-----------------------------------------+--------------------------------------+
| innodb_additional_mem_pool_size         | 20971520                             | 
| innodb_autoextend_increment             | 8                                    | 
| innodb_buffer_pool_awe_mem_mb           | 0                                    | 
| innodb_buffer_pool_size                 | 8841592832                           | 
| innodb_checksums                        | ON                                   | 
| innodb_commit_concurrency               | 0                                    | 
| innodb_concurrency_tickets              | 500                                  | 
| innodb_data_file_path                   | ibdata1:2000M;ibdata2:10M:autoextend | 
| innodb_data_home_dir                    | /storage/mysql/innodb/               | 
| innodb_adaptive_hash_index              | ON                                   | 
| innodb_doublewrite                      | ON                                   | 
| innodb_fast_shutdown                    | 1                                    | 
| innodb_file_io_threads                  | 4                                    | 
| innodb_file_per_table                   | ON                                   | 
| innodb_flush_log_at_trx_commit          | 1                                    | 
| innodb_flush_method                     | O_DIRECT                             | 
| innodb_force_recovery                   | 0                                    | 
| innodb_lock_wait_timeout                | 180                                  | 
| innodb_locks_unsafe_for_binlog          | OFF                                  | 
| innodb_log_arch_dir                     | /storage/mysql/innodb/               | 
| innodb_log_archive                      | OFF                                  | 
| innodb_log_buffer_size                  | 67108864                             | 
| innodb_log_file_size                    | 104857600                            | 
| innodb_log_files_in_group               | 2                                    | 
| innodb_log_group_home_dir               | /storage/mysql/innodb/               | 
| innodb_max_dirty_pages_pct              | 90                                   | 
| innodb_max_purge_lag                    | 0                                    | 
| innodb_mirrored_log_groups              | 1                                    | 
| innodb_open_files                       | 300                                  | 
| innodb_rollback_on_timeout              | OFF                                  | 
| innodb_support_xa                       | ON                                   | 
| innodb_sync_spin_loops                  | 20                                   | 
| innodb_table_locks                      | ON                                   | 
| innodb_thread_concurrency               | 8                                    | 
| innodb_thread_sleep_delay               | 10000                                | 
| innodb_use_legacy_cardinality_algorithm | ON                                   | 
+-----------------------------------------+--------------------------------------+

Answer 1

A few notes and suggestions:

I was asking for the innodb% params to verify you're not running with silly defaults. You're not, and setup looks OK.

The inbound_link_changes table doesn't have a PRIMARY KEY. It should, as with all InnoDB tables, since these use a clustered index, which is the PRIMARY KEY. Yours does not have one, so it's clustered by internal row id, which is not optimal. Is there any UNIQUE combination on this table?

If not, I recommend making up a surrogate key, via AUTO_INCREMENT.

This could actually affect your query, since secondary keys are affected by clustering index (explanation is outside scope of this answer).

In addition, consider setting up an index on (link_id, change_type). Since you're giving away change_type as constant, this will be taken into account when looking up the index.

Answer 2

Here is a discussion of the things that can cause trouble when switching from MyISAM to InnoDB, and what to do about them: http://mysql.rjweb.org/doc.php/myisam2innodb

I see 8.8GB in the buffer_pool; I assume you have at least 16GB of RAM?

innodb_flush_log_at_trx_commit = 1 is the safest, but it is the slowest. Change that to 2; see if things run faster. If so, then read about it to see about the speed/safety tradeoffs.

Were you using LOCK TABLES? If so, convert to BEGIN...COMMIT.

More tuning suggestions here: http://mysql.rjweb.org/doc.php/memory