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 |
+-----------------------------------------+--------------------------------------+
Best Answer
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_changestable doesn't have aPRIMARY KEY. It should, as with all InnoDB tables, since these use a clustered index, which is thePRIMARY KEY. Yours does not have one, so it's clustered by internal row id, which is not optimal. Is there anyUNIQUEcombination 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 awaychange_typeas constant, this will be taken into account when looking up the index.