Miałem wszystkie moje tabele w myISAM, ale blokowanie na poziomie tabeli zaczęło mnie zabijać, gdy miałem długo działające zadania aktualizacji. Przekształciłem swoje podstawowe tabele w InnoDB, a teraz wiele moich zapytań trwa ponad minutę, aby zakończyć je, gdy były one niemal natychmiastowe w myISAM. Zwykle tkwią wSorting result krok. Czy zrobiłem coś złego?

Na przykład :

SELECT * FROM `metaward_achiever` 
 INNER JOIN `metaward_alias` ON (`metaward_achiever`.`alias_id` = `metaward_alias`.`id`) 
 WHERE `metaward_achiever`.`award_id` = 1507  
 ORDER BY `metaward_achiever`.`modified` DESC 
 LIMIT 100  

Teraz zajmuje około 90 sekund. Oto opis:

+----+-------------+-------------------+--------+-------------------------------------------------------+----------------------------+---------+---------------------------------+-------+-----------------------------+
| id | select_type | table             | type   | possible_keys                                         | key                        | key_len | ref                             | rows  | Extra                       |
+----+-------------+-------------------+--------+-------------------------------------------------------+----------------------------+---------+---------------------------------+-------+-----------------------------+
|  1 | SIMPLE      | metaward_achiever | ref    | metaward_achiever_award_id,metaward_achiever_alias_id | metaward_achiever_award_id | 4       | const                           | 66424 | Using where; Using filesort | 
|  1 | SIMPLE      | metaward_alias    | eq_ref | PRIMARY                                               | PRIMARY                    | 4       | paul.metaward_achiever.alias_id |     1 |                             | 
+----+-------------+-------------------+--------+-------------------------------------------------------+----------------------------+---------+---------------------------------+-------+-----------------------------+

Wygląda na to, że teraz TONY moich zapytań utknęły w kroku „Wynik sortowania”:

mysql> show processlist;
+--------+------+-----------+------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
| Id     | User | Host      | db   | Command | Time | State          | Info                                                                                                 |
+--------+------+-----------+------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
| 460568 | paul | localhost | paul | Query   |    0 | NULL           | show processlist                                                                                     | 
| 460638 | paul | localhost | paul | Query   |    0 | Sorting result | SELECT `metaward_achiever`.`id`, `metaward_achiever`.`modified`, `metaward_achiever`.`created`, `met | 
| 460710 | paul | localhost | paul | Query   |   79 | Sending data   | SELECT `metaward_achiever`.`id`, `metaward_achiever`.`modified`, `metaward_achiever`.`created`, `met | 
| 460722 | paul | localhost | paul | Query   |   49 | Updating       | UPDATE `metaward_alias` SET `modified` = '2009-09-15 12:43:50', `created` = '2009-08-24 11:55:24', ` | 
| 460732 | paul | localhost | paul | Query   |   25 | Sorting result | SELECT `metaward_achiever`.`id`, `metaward_achiever`.`modified`, `metaward_achiever`.`created`, `met | 
+--------+------+-----------+------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

Dlaczego ta prosta aktualizacja utknęła na 49 sekund?

Jeśli to pomoże, oto schematy:

| metaward_alias | CREATE TABLE `metaward_alias` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `modified` datetime NOT NULL,
  `created` datetime NOT NULL,
  `string_id` varchar(255) DEFAULT NULL,
  `shortname` varchar(100) NOT NULL,
  `remote_image` varchar(500) DEFAULT NULL,
  `image` varchar(100) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `type_id` int(11) NOT NULL,
  `md5` varchar(32) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `string_id` (`string_id`),
  KEY `metaward_alias_user_id` (`user_id`),
  KEY `metaward_alias_type_id` (`type_id`)
) ENGINE=InnoDB AUTO_INCREMENT=858381 DEFAULT CHARSET=utf8 | 

| metaward_award | CREATE TABLE `metaward_award` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `modified` datetime NOT NULL,
  `created` datetime NOT NULL,
  `string_id` varchar(20) NOT NULL,
  `owner_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `description` longtext NOT NULL,
  `owner_points` int(11) NOT NULL,
  `url` varchar(500) NOT NULL,
  `remote_image` varchar(500) DEFAULT NULL,
  `image` varchar(100) NOT NULL,
  `parent_award_id` int(11) DEFAULT NULL,
  `slug` varchar(110) NOT NULL,
  `true_points` double DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `string_id` (`string_id`),
  KEY `metaward_award_owner_id` (`owner_id`),
  KEY `metaward_award_parent_award_id` (`parent_award_id`),
  KEY `metaward_award_slug` (`slug`),
  KEY `metaward_award_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=122176 DEFAULT CHARSET=utf8 | 

| metaward_achiever | CREATE TABLE `metaward_achiever` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `modified` datetime NOT NULL,
  `created` datetime NOT NULL,
  `award_id` int(11) NOT NULL,
  `alias_id` int(11) NOT NULL,
  `count` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `metaward_achiever_award_id` (`award_id`),
  KEY `metaward_achiever_alias_id` (`alias_id`)
) ENGINE=InnoDB AUTO_INCREMENT=77175366 DEFAULT CHARSET=utf8 | 

A te w moim my.cnf

innodb_file_per_table
innodb_buffer_pool_size = 2048M
innodb_additional_mem_pool_size = 16M
innodb_flush_method=O_DIRECT