MySQL Forums
Forum List  »  InnoDB

Re: COMMIT function take too much time to respond
Posted by: He Ha
Date: April 10, 2011 08:09PM

UPDATE statement is just a statement that use CASE to help update multiple id (row) with only one query. That query should update about 3 rows. I have 4GB RAM but it's the same machine with web server too. (using ligghttpd/php-cgi). At this time, I'm curious that it maybe some deadlock of InnoDB and make the process to wait. When I killed the process, webserver keep sending a new one and make new COMMIT appeared. I do not monitor lighttpd/php-cgi process at that time but maybe it's the cause of problem?

And here:

mysql> SHOW CREATE TABLE gc_monster;
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| gc_monster | CREATE TABLE `gc_monster` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `in_party` enum('N','Y') NOT NULL DEFAULT 'N',
  `party_id` int(11) NOT NULL DEFAULT '0' COMMENT 'for travel',
  `deleted` enum('N','Y') DEFAULT 'N',
  `user_id` bigint(20) DEFAULT '0',
  `farm_id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `status` enum('egg','active') DEFAULT 'active',
  `specie` varchar(30) DEFAULT NULL,
  `form_level` int(11) DEFAULT '1',
  `specie_type` varchar(20) NOT NULL DEFAULT 'none',
  `element` int(11) DEFAULT '0',
  `time_lastrefresh` timestamp NULL DEFAULT '0000-00-00 00:00:00',
  `time_birthday` datetime DEFAULT '0000-00-00 00:00:00',
  `revivecount` int(11) DEFAULT '0',
  `rank` int(11) DEFAULT '0',
  `loyalty` int(11) DEFAULT '0',
  `time_loyalty` datetime NOT NULL,
  `happiness` int(11) DEFAULT '50',
  `level` int(11) DEFAULT '1',
  `exp` int(11) DEFAULT '0',
  `evo_exp` int(11) NOT NULL,
  `hp` int(11) DEFAULT '0',
  `hp_max` int(11) DEFAULT '0',
  `mp` int(11) NOT NULL,
  `mp_max` int(11) NOT NULL,
  `stamina` int(11) DEFAULT '50',
  `stamina_max` int(11) DEFAULT '50',
  `time_stamina_lastrefill` datetime NOT NULL,
  `fight_win` int(11) DEFAULT '0',
  `fight_total` int(11) DEFAULT '0',
  `fight_rating` int(11) DEFAULT '0',
  `item_equip` text,
  `data_serialized` text,
  `stat_serialized` text COMMENT 'str,int,dex,vit,agi,luk, + base',
  `food_slot` text NOT NULL COMMENT 'class food_slot',
  `skill_slot` text NOT NULL COMMENT 'class skill',
  `time_potion_delay` datetime NOT NULL,
  `food_id_evolution` int(11) NOT NULL,
  `time_mp_delay` datetime NOT NULL,
  `time_stamina_delay` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `party_id` (`party_id`,`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=104437 DEFAULT CHARSET=utf8 PACK_KEYS=0 |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE gc_log_action;
+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table         | Create Table                                                                                                                                                                                                                                                                                                                                                                                                 |
+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| gc_log_action | CREATE TABLE `gc_log_action` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `time_id` int(11) NOT NULL,
  `action` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `param` int(11) NOT NULL,
  `time` datetime DEFAULT NULL,
  `count` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `time_id` (`time_id`,`action`,`param`)
) ENGINE=InnoDB AUTO_INCREMENT=1316766 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW TABLE STATUS LIKE 'gc_monster';
+------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| 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 |
+------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| gc_monster | InnoDB |      10 | Compact    | 127365 |           1800 |   229376000 |               0 |      9355264 | 4396679168 |         104433 | 2011-04-11 04:08:08 | NULL        | NULL       | utf8_general_ci |     NULL | pack_keys=0    |         |
+------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)

mysql> SHOW TABLE STATUS LIKE 'gc_log_action';
+---------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| 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 |
+---------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| gc_log_action | InnoDB |      10 | Compact    | 1311997 |             69 |    90816512 |               0 |    109887488 | 4394582016 |        1316745 | 2011-04-11 04:04:15 | NULL        | NULL       | utf8_unicode_ci |     NULL |                |         |
+---------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.57 sec)

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: COMMIT function take too much time to respond
900
April 10, 2011 08:09PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.