Re: INSERT-only table gets locked randomly (MySQL 5.0)
While looking for a solution I found this thread. I'm experiencing same problems with INSERTs taking forever and locking tables. It happens with different databases. My configuration is very similar: AMD 3000+ 64 bit, Linux Debian Sarge, kernel 2.6.16.1, MySQL standard-5.0.24a-linux-x86_64-glibc23.
mysqladmin processlist:
-----+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+--------------+-----------+---------------+---------+-------+----------------+------------------------------------------------------------------------------------------------------+
| 987 | dogocom_user | localhost | dogocom_phpbb | Query | 12375 | update | INSERT INTO groups (group_name, group_description, group_single_user, group_moderator)
VALUES (' |
| 2455 | nethit_user | localhost | nethit | Query | 6170 | update | INSERT INTO vote (login,ip) VALUES ('ukskorona','87.239.57.225') |
| 3997 | nethit_user | localhost | nethit | Query | 1691 | update | INSERT INTO messageboard_ban (login,ip) VALUES ('dvbworld','201.28.123.90') |
| 4003 | nethit_user | localhost | nethit | Query | 1672 | Locked | INSERT INTO messageboard_ban (login,ip) VALUES ('dvbworld','201.28.123.90') |
| 4005 | nethit_user | localhost | nethit | Query | 1663 | Locked | INSERT INTO messageboard_ban (login,ip) VALUES ('dvbworld','201.28.123.90') |
| 4009 | nethit_user | localhost | nethit | Query | 1661 | Locked | INSERT INTO messageboard_ban (login,ip) VALUES ('dvbworld','201.28.123.90') |
| 4010 | nethit_user | localhost | nethit | Query | 1661 | Locked | INSERT INTO messageboard_ban (login,ip) VALUES ('dvbworld','201.28.123.90') |
| 4012 | nethit_user | localhost | nethit | Query | 1661 | Locked | INSERT INTO messageboard_ban (login,ip) VALUES ('dvbworld','201.28.123.90') |
| 4014 | nethit_user | localhost | nethit | Query | 1661 | Locked | INSERT INTO messageboard_ban (login,ip) VALUES ('dvbworld','201.28.123.90') |
| 4617 | nethit_user | localhost | nethit_portal | Query | 0 | Writing to net | SELECT nuke_hooks.pn_tarea,
nuke_hooks.pn_tmodule,
nuke_hooks. |
| 4618 | root | localhost | | Query | 0 | | show processlist |
+------+--------------+-----------+---------------+---------+-------+----------------+------------------------------------------------------------------------------------------------------+
Load is very low: 0.10, 0.08, 0.09. MySQL process just sits and is doing nothing.
There is only one mysqld process running and the result of running strace is:
--- CUT ---
Process 18084 attached - interrupt to quit
select(11, [10], NULL, NULL, NULL) = 1 (in [10])
fcntl(10, F_SETFL, O_RDWR|O_NONBLOCK) = 0
accept(10, {sa_family=AF_FILE, path=@}, [8516025424675864578]) = 129
fcntl(10, F_SETFL, O_RDWR) = 0
getsockname(129, {sa_family=AF_FILE, path="/var/run/mysql"}, [8589934622]) = 0
fcntl(129, F_SETFL, O_RDONLY) = 0
fcntl(129, F_GETFL) = 0x2 (flags O_RDWR|O_LARGEFILE)
fcntl(129, F_SETFL, O_RDWR|O_NONBLOCK) = 0
setsockopt(129, SOL_IP, IP_TOS, [87102211640786952], 4) = -1 EOPNOTSUPP (Operation not supported)
futex(0xb76d30, FUTEX_WAKE, 1) = 1
futex(0xb76d20, FUTEX_WAKE, 1) = 1
futex(0xb76420, FUTEX_WAKE, 1) = 1
select(11, [10], NULL, NULL, NULL) = 1 (in [10])
fcntl(10, F_SETFL, O_RDWR|O_NONBLOCK) = 0
accept(10, {sa_family=AF_FILE, path=@}, [8516025424675864578]) = 129
fcntl(10, F_SETFL, O_RDWR) = 0
getsockname(129, {sa_family=AF_FILE, path="/var/run/mysql"}, [8589934622]) = 0
fcntl(129, F_SETFL, O_RDONLY) = 0
fcntl(129, F_GETFL) = 0x2 (flags O_RDWR|O_LARGEFILE)
fcntl(129, F_SETFL, O_RDWR|O_NONBLOCK) = 0
setsockopt(129, SOL_IP, IP_TOS, [87102211640786952], 4) = -1 EOPNOTSUPP (Operation not supported)
futex(0xb76d30, FUTEX_WAKE, 1) = 1
futex(0xb76d20, FUTEX_WAKE, 1) = 1
futex(0xb76420, FUTEX_WAKE, 1) = 1
select(11, [10], NULL, NULL, NULL) = 1 (in [10])
fcntl(10, F_SETFL, O_RDWR|O_NONBLOCK) = 0
accept(10, {sa_family=AF_FILE, path=@}, [8516025424675864578]) = 133
fcntl(10, F_SETFL, O_RDWR) = 0
getsockname(133, {sa_family=AF_FILE, path="/var/run/mysql"}, [8589934622]) = 0
fcntl(133, F_SETFL, O_RDONLY) = 0
fcntl(133, F_GETFL) = 0x2 (flags O_RDWR|O_LARGEFILE)
fcntl(133, F_SETFL, O_RDWR|O_NONBLOCK) = 0
setsockopt(133, SOL_IP, IP_TOS, [86697866239672328], 4) = -1 EOPNOTSUPP (Operation not supported)
futex(0xb76d30, FUTEX_WAKE, 1) = 1
futex(0xb76d20, FUTEX_WAKE, 1) = 1
futex(0xb76420, FUTEX_WAKE, 1) = 1
select(11, [10], NULL, NULL, NULL) = 1 (in [10])
fcntl(10, F_SETFL, O_RDWR|O_NONBLOCK) = 0
accept(10, {sa_family=AF_FILE, path=@}, [8516025424675864578]) = 133
fcntl(10, F_SETFL, O_RDWR) = 0
getsockname(133, {sa_family=AF_FILE, path="/var/run/mysql"}, [8589934622]) = 0
fcntl(133, F_SETFL, O_RDONLY) = 0
fcntl(133, F_GETFL) = 0x2 (flags O_RDWR|O_LARGEFILE)
fcntl(133, F_SETFL, O_RDWR|O_NONBLOCK) = 0
setsockopt(133, SOL_IP, IP_TOS, [86697866239672328], 4) = -1 EOPNOTSUPP (Operation not supported)
futex(0xb76d30, FUTEX_WAKE, 1) = 1
futex(0xb76d20, FUTEX_WAKE, 1) = 1
futex(0xb76420, FUTEX_WAKE, 1) = 1
select(11, [10], NULL, NULL, NULL) = 1 (in [10])
fcntl(10, F_SETFL, O_RDWR|O_NONBLOCK) = 0
accept(10, {sa_family=AF_FILE, path=@}, [8516025424675864578]) = 133
fcntl(10, F_SETFL, O_RDWR) = 0
getsockname(133, {sa_family=AF_FILE, path="/var/run/mysql"}, [8589934622]) = 0
fcntl(133, F_SETFL, O_RDONLY) = 0
fcntl(133, F_GETFL) = 0x2 (flags O_RDWR|O_LARGEFILE)
fcntl(133, F_SETFL, O_RDWR|O_NONBLOCK) = 0
setsockopt(133, SOL_IP, IP_TOS, [86697866239672328], 4) = -1 EOPNOTSUPP (Operation not supported)
futex(0xb76d30, FUTEX_WAKE, 1) = 1
futex(0xb76d20, FUTEX_WAKE, 1) = 1
futex(0xb76420, FUTEX_WAKE, 1) = 1
select(11, [10], NULL, NULL, NULL) = 1 (in [10])
fcntl(10, F_SETFL, O_RDWR|O_NONBLOCK) = 0
accept(10, {sa_family=AF_FILE, path=@}, [8516025424675864578]) = 134
fcntl(10, F_SETFL, O_RDWR) = 0
getsockname(134, {sa_family=AF_FILE, path="/var/run/mysql"}, [8589934622]) = 0
fcntl(134, F_SETFL, O_RDONLY) = 0
fcntl(134, F_GETFL) = 0x2 (flags O_RDWR|O_LARGEFILE)
fcntl(134, F_SETFL, O_RDWR|O_NONBLOCK) = 0
setsockopt(134, SOL_IP, IP_TOS, [87366025711976456], 4) = -1 EOPNOTSUPP (Operation not supported)
mmap(NULL, 262144, PROT_READ|PROT_WRITE|PROT_EXEC, MAP_PRIVATE|MAP_ANONYMOUS|0x40, -1, 0) = 0x441c0000
mprotect(0x441c0000, 4096, PROT_NONE) = 0
clone(child_stack=0x441ff8d0, flags=CLONE_VM|CLONE_FS|CLONE_FILES|CLONE_SIGHAND|CLONE_THREAD|CLONE_SYSVSEM|CLONE_SETTLS|CLONE_PARENT_SETTID|CLONE_CHILD_CLEARTID|CLONE_DETACHED, parent_tidptr=0x441ffa00, tls=0x441ff970, child_tidptr=0x441ffa00) = 18191
select(11, [10], NULL, NULL, NULL
--- CUT---
I've tried to install MySQL 5 on other server with similar specs: AMD 3700+ 64bit, Debian Sarge and MySQL standard-5.0.24a-linux-x86_64-glibc23 and it was the same: INSERTs or REPLACEs were locking tables. After downgrading to MySQL 4.1.x everything was back to normal.