MySQL Forums
Forum List  »  InnoDB

mysql server hangs on update
Posted by: Boris Dolgov
Date: December 15, 2008 07:47AM

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
mysql server hangs on update
2393
December 15, 2008 07:47AM
1524
January 11, 2009 10:37PM


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.