Hello All.
I need your help with Mysql.
I'am using version 5.1.22 compiled with libthread and MyISAM engine on
FreeBSD 6.2-STABLE and some times getting lock (deadlock?) on parallel
update queries from some hosts.
Very long time the query is keep state "Updating" and no disk, cpu and
other activity. Kill command only change status to "killed" :(
After manipulations impossible shutdown server normally. Used kill -9.
Also partitioning used, but in other table.
Tkanks.
Table format and output show full process list attached.
CREATE TABLE `tbl_test` (
`uid` varchar(64) NOT NULL DEFAULT '',
`domain` varchar(64) NOT NULL DEFAULT '',
`ip` int(10) unsigned DEFAULT NULL,
`tt` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
UNIQUE KEY `idx_domainuid` (`domain`,`uid`),
KEY `idx_tt` (`tt`)
) ENGINE=MyISAM DEFAULT CHARSET=ascii
+------+----------+--------------------+---------+---------+-------+----------+-----------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------+
| Id | User | Host | db | Command | Time | State |
Info
|
+------+----------+--------------------+---------+---------+-------+----------+-----------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------+
| 1 | user_test | 10.10.10.33:57296 | db_mail | Query | 1408 | Locked
| UPDATE tbl_test SET tt = IF((@t := STR_TO
_DATE('Fri Feb 1 17:06:16 2008', "%a %b %e %T %Y")) > tt, @t, tt), ip = IF(@t
>= tt, INET_ATON('x.x.x.x'), ip) WHERE uid = 'lknan' AND domain = 'example.ru'
|
| 2 | user_test | 10.10.10.93:58937 | db_mail | Query | 1408 | Locked
| UPDATE tbl_test SET tt = IF((@t := STR_TO_DATE('Fri Feb 1 17:06:16 2008',
"%a %b %e %T %Y")) > tt, @t, tt), ip = IF(@t >= tt, INET_ATON('x.x.x.x'), ip)
WHERE uid = 'galin' AND domain = 'example.ru' |
| 3 | user_test | 10.10.10.94:52218 | db_mail | Query | 1408 | Updating
| UPDATE tbl_test SET tt = IF((@t := STR_TO_DATE('Fri Feb 1 17:06:16 2008',
"%a %b %e %T %Y")) > tt, @t, tt), ip = IF(@t >= tt, INET_ATON('x.x.x.x'), ip)
WHERE uid = 'igv' AND domain = 'example.ru' |
| 4 | user_test | 10.10.10.222:50197 | db_mail | Query | 1408 | Locked
| UPDATE tbl_test SET tt = IF((@t := STR_TO_DATE('Fri Feb 1 17:06:16 2008',
"%a %b %e %T %Y")) > tt, @t, tt), ip = IF(@t >= tt, INET_ATON('x.x.x.x'), ip)
WHERE uid = 'sky' AND domain = 'example.ru' |
+------+----------+--------------------+---------+---------+-------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State
| Info
|
+------+----------+--------------------+---------+---------+-------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | user_test | 10.10.10.33:57296 | db_test | Query | 1458 | Locked
| UPDATE tbl_test SET tt = IF((@t := STR_TO_DATE('Fri Feb 1 17:06:16 2008',
"%a %b %e %T %Y")) > tt, @t, tt), ip = IF(@t >= tt, INET_ATON('x.x.x.x'), ip)
WHERE uid = 'lknan' AND domain = 'example.ru' |
| 2 | user_test | 10.10.10.93:58937 | db_test | Query | 1458 | Locked
| UPDATE tbl_test SET tt = IF((@t := STR_TO_DATE('Fri Feb 1 17:06:16 2008',
"%a %b %e %T %Y")) > tt, @t, tt), ip = IF(@t >= tt, INET_ATON('x.x.x.x'), ip)
WHERE uid = 'galin' AND domain = 'example.ru' |
| 3 | user_test | 10.10.10.94:52218 | db_test | Query | 1458 | Updating
| UPDATE tbl_test SET tt = IF((@t := STR_TO_DATE('Fri Feb 1 17:06:16 2008',
"%a %b %e %T %Y")) > tt, @t, tt), ip = IF(@t >= tt, INET_ATON('x.x.x.x'), ip)
WHERE uid = 'igv' AND domain = 'example.ru' |
| 4 | user_test | 10.10.10.222:50197 | db_test | Query | 1458 | Locked
| UPDATE tbl_test SET tt = IF((@t := STR_TO_DATE('Fri Feb 1 17:06:16 2008',
"%a %b %e %T %Y")) > tt, @t, tt), ip = IF(@t >= tt, INET_ATON('x.x.x.x'), ip)
WHERE uid = 'sky' AND domain = 'example.ru' |
+------+----------+--------------------+---------+---------+-------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State |
Info
|
+------+----------+--------------------+---------+---------+-------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | user_test | 10.10.10.33:57296 | db_test | Query | 1594 | Locked
| UPDATE tbl_test SET tt = IF((@t := STR_TO_DATE('Fri Feb 1 17:06:16 2008',
"%a %b %e %T %Y")) > tt, @t, tt), ip = IF(@t >= tt, INET_ATON('x.x.x.x'), ip)
WHERE uid = 'lknan' AND domain = 'example.ru' |
| 2 | user_test | 10.10.10.93:58937 | db_test | Query | 1594 | Locked
| UPDATE tbl_test SET tt = IF((@t := STR_TO_DATE('Fri Feb 1 17:06:16 2008',
"%a %b %e %T %Y")) > tt, @t, tt), ip = IF(@t >= tt, INET_ATON('x.x.x.x'), ip)
WHERE uid = 'galin' AND domain = 'example.ru' |
| 3 | user_test | 10.10.10.94:52218 | db_test | Query | 1594 | Updating
| UPDATE tbl_test SET tt = IF((@t := STR_TO_DATE('Fri Feb 1 17:06:16 2008',
"%a %b %e %T %Y")) > tt, @t, tt), ip = IF(@t >= tt, INET_ATON('x.x.x.x'), ip)
WHERE uid = 'igv' AND domain = 'example.ru' |
| 4 | user_test | 10.10.10.222:50197 | db_test | Query | 1594 | Locked
| UPDATE tbl_test SET tt = IF((@t := STR_TO_DATE('Fri Feb 1 17:06:16 2008',
"%a %b %e %T %Y")) > tt, @t, tt), ip = IF(@t >= tt, INET_ATON('x.x.x.x'), ip)
WHERE uid = 'sky' AND domain = 'example.ru' |
+------+----------+--------------------+---------+---------+-------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State |
Info
|
+------+----------+--------------------+---------+---------+-------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | user_test | 10.10.10.33:57296 | db_test | Query | 1781 | Locked
| UPDATE tbl_test SET tt = IF((@t := STR_TO_DATE('Fri Feb 1 17:06:16 2008',
"%a %b %e %T %Y")) > tt, @t, tt), ip = IF(@t >= tt, INET_ATON('x.x.x.x'), ip)
WHERE uid = 'lknan' AND domain = 'example.ru' |
| 2 | user_test | 10.10.10.93:58937 | db_test | Query | 1781 | Locked
| UPDATE tbl_test SET tt = IF((@t := STR_TO_DATE('Fri Feb 1 17:06:16 2008',
"%a %b %e %T %Y")) > tt, @t, tt), ip = IF(@t >= tt, INET_ATON('x.x.x.x'), ip)
WHERE uid = 'galin' AND domain = 'example.ru' |
| 3 | user_test | 10.10.10.94:52218 | db_test | Killed | 1781 | Updating
| UPDATE tbl_test SET tt = IF((@t := STR_TO_DATE('Fri Feb 1 17:06:16 2008',
"%a %b %e %T %Y")) > tt, @t, tt), ip = IF(@t >= tt, INET_ATON('x.x.x.x'), ip)
WHERE uid = 'igv' AND domain = 'example.ru' |
| 4 | user_test | 10.10.10.222:50197 | db_test | Query | 1781 | Locked
| UPDATE tbl_test SET tt = IF((@t := STR_TO_DATE('Fri Feb 1 17:06:16 2008',
"%a %b %e %T %Y")) > tt, @t, tt), ip = IF(@t >= tt, INET_ATON('x.x.x.x'), ip)
WHERE uid = 'sky' AND domain = 'example.ru' |
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
