Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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                                   | 
+-----------------------------------------+--------------------------------------+
share|improve this question
    
Please provide with SHOW CREATE TABLE for both tables involved. –  Shlomi Noach Aug 29 '12 at 15:58
    
Also, output of SHOW GLOBAL VARIABLES LIKE 'innodb%', please. –  Shlomi Noach Aug 29 '12 at 15:59
    
You have innodb_thread_concurrency = 8. Please set this to zero. I learned from Percona Conference last year that 0 makes InnoDB operate at infinite concurrency, which is best for InnoDB. –  RolandoMySQLDBA Aug 29 '12 at 16:18
    
What is your datadir ??? –  RolandoMySQLDBA Aug 29 '12 at 16:31
    
@RolandoMySQLDBA, innodb_thread_concurrency only takes effect on Solaris. See here –  Shlomi Noach Aug 29 '12 at 17:12
show 4 more comments

2 Answers

up vote 1 down vote accepted

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.

share|improve this answer
    
Thanks for the detailed suggestions. These are definitely changes that I will make to the inbound_link_changes table, but does this explain why the performance changed so much when I changed the storage engine of the inbound_links table from MyISAM to InnoDB? If the lack of a primary key on inbound_link_changes was going to cause a performance issue, wouldn't we have seen that against the MyISAM inbound_links table too? –  Mark B Aug 29 '12 at 19:31
    
I'm not sure that the lack of PRIMARY KEY is indeed the cause for this major impact, but it just might: MyISAM does not use a clustered key structure, and all indexes are independent of one another, as opposed to InnoDB, where all indexes depend on the PRIMARY KEY, –  Shlomi Noach Aug 30 '12 at 2:41
    
I've now finished adding the primary key to the inbound_link_changes table, and also changed link_id in that table to a bigint. Performance is now much better - 0.2 seconds for my original sample query. We are now reviewing all our InnoDB tables to ensure they all have a sensible primary key. Thanks for helping out with this! –  Mark B Sep 3 '12 at 9:30
    
Cool! Check out this view from common_schema, which lets you know which InnoDB tables do not have a primary key. Disclaimer: development of mine. –  Shlomi Noach Sep 3 '12 at 10:28
add comment

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

share|improve this answer
    
Rick, innodb_flush_log_at_trx_commit relates to write performance, but the problem described relates to an unreasonable SELECT time. –  Shlomi Noach Aug 30 '12 at 2:43
    
Thanks Rick - that server has 24GB RAM, so we should be fine with the 8GB buffer pool. We're not using any explicit locking. –  Mark B Aug 30 '12 at 9:25
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.