mysql server hangs on update
Hello!
When moved tables to innodb, a problem had appeared.
There is a lot of concurrent queries to a table called peers.
Example of the query: UPDATE LOW_PRIORITY peers SET uploaded = 0, downloaded = 0, uploadoffset = 0, downloadoffset = 0, to_go = 0, last_action = NOW(), prev_action = '2008-12-15 12:32:16', seeder = 'yes', agent = 'uTorrent/1610' WHERE torrent = 15217 AND passkey = '4f4b486ab34adab83743eae6d855f47a'.
Table structure:
CREATE TABLE `peers` (
`id` int(10) unsigned NOT NULL auto_increment,
`torrent` int(10) unsigned NOT NULL default '0',
`peer_id` varchar(20) NOT NULL default '',
`ip` varchar(64) NOT NULL default '',
`port` smallint(5) unsigned NOT NULL default '0',
`uploaded` bigint(20) unsigned NOT NULL default '0',
`downloaded` bigint(20) unsigned NOT NULL default '0',
`uploadoffset` bigint(20) unsigned NOT NULL default '0',
`downloadoffset` bigint(20) unsigned NOT NULL default '0',
`to_go` bigint(20) unsigned NOT NULL default '0',
`seeder` enum('yes','no') NOT NULL default 'no',
`started` datetime NOT NULL default '0000-00-00 00:00:00',
`last_action` datetime NOT NULL default '0000-00-00 00:00:00',
`prev_action` datetime NOT NULL default '0000-00-00 00:00:00',
`connectable` enum('yes','no') NOT NULL default 'yes',
`userid` int(10) unsigned NOT NULL default '0',
`
`agent` varchar(60) NOT NULL default '',
`finishedat` int(10) unsigned NOT NULL default '0',
`passkey` varchar(32) NOT NULL default '',
PRIMARY KEY (`id`),
UNIQUE KEY `torrent_peer_id` (`torrent`,`peer_id`),
KEY `torrent` (`torrent`,`passkey`)
) ENGINE=InnoDB;
mysql> status
--------------
mysql Ver 14.12 Distrib 5.0.45, for redhat-linux-gnu (x86_64) using readline 5.0
Connection id: 34689
Current database: hi
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.0.45 Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: cp1251
Db characterset: utf8
Client characterset: cp1251
Conn. characterset: cp1251
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 11 min 35 sec
Threads: 2 Questions: 256543 Slow queries: 3 Opens: 225 Flush tables: 1 Open tables: 219 Queries per second avg: 369.127
--------------
Suddenly, a query comes. It's state changes to "Sending data", and it hangs. Then all mysql threads are hanging in "Updating" or "update" status. Only restart helps.
There weren't such problems on MyISAM, but table locks caused us to switch to innodb. The result is the same if using unix sockets or tcp sockets.
I don't know what to do. Could you help me?