Members Members   Search Search   Help Help   Register Register   Login Login   Home Home


Home » Performance » MySQL » too many locked tables
too many locked tables [message #3030] Thu, 01 May 2008 16:30 Go to next message
zedude is currently offline zedude
Messages: 4
Registered: May 2008
Junior Member
MySQL ver: 5.0.51a
Mem: 16G/32G
CPU: 2 x Quad Core Xeon
OS: RHEL 5

Occasionally we see a locked-up scenario on our servers. It starts with a few locked tables, then the number keeps growing, until it reaches the max_connections.

Looking into the queries, it does not seem to have any deadlock. The queries are either inserts or updates, to tables from different databases, without competing for resources.

Any idea what could be the problem? This has annoyed us for quite a while. All tables are using InnoDB.

Thanks!

PS: here is a sample result from "show processlist" when the lockup happens.

| 10311326 | app | web1:39980 | app | Query | 95 | Locked | UPDATE D328.activity SET `action`= 'true' WHERE id='178731' |
| 10311328 | app | web1:39982 | app | Query | 95 | Locked | INSERT INTO D197.activity (`userID`,`deptID`,`salesID`,`nextContact`,`action`,`message `,`ca |
| 10311343 | app | web1:39998 | app | Query | 94 | Locked | INSERT INTO D101.activity (`userID`,`deptID`,`salesID`,`nextContact`,`action`,`message `,`ca |
| 10311352 | app | web1:40016 | app | Query | 93 | Locked | UPDATE D863.activity SET message=CONCAT_WS('',message,'\ |
| 10311362 | app | web1:40030 | app | Query | 92 | Locked | INSERT INTO D734.activity (`userID`,`deptID`,`salesID`,`nextContact`,`action`,`message `,`ca |
| 10311368 | app | web2:52174 | app | Query | 91 | Locked | UPDATE D399.activity SET `action`= 'true' WHERE id='110776' |
| 10311369 | app | web1:40040 | app | Query | 91 | Locked | UPDATE D287.user SET `Date`= '1209670895' WHERE id='11875' AND responseDate=0 |
| 10311384 | app | web1:40060 | app | Query | 87 | Locked | INSERT INTO D863.activity (`userID`,`deptID`,`salesID`,`nextContact`,`action`,`message `,`ca |
| 10311418 | app | web1:40114 | app | Query | 84 | Locked | INSERT INTO D925.activity (`salesID`,`userID`,`deptID`,`type`,`day`,`message`,`reason` ,`date`) V |
| 10311427 | app | web2:52202 | app | Query | 83 | Locked | UPDATE D917.activity SET `action`= '1209670903' WHERE id='110472' |
| 10311431 | app | web1:40136 | app | Query | 81 | Locked | INSERT INTO D197.activity (`salesID`,`userID`,`deptID`,`type`,`day`,`message`,`reason` ,`date`) V |
| 10311433 | app | web1:40140 | app | Query | 81 | Locked | INSERT INTO D734.activity (`userID`,`deptID`,`salesID`,`nextContact`,`action`,`message `,`ca |
| 10311435 | app | web1:40142 | app | Query | 80 | Locked | INSERT INTO D970.activity (`deptID`,`userID`,`salesID`,`folder`,`date`,`type`,`message `) VALUES |
| 10311437 | app | web1:40146 | app | Query | 80 | Locked | INSERT INTO D951.activity (`userID`,`deptID`,`salesID`,`nextContact`,`action`,`message `,`ca |
| 10311446 | app | web1:40163 | app | Query | 78 | Locked | INSERT INTO D567.activity (`salesID`,`userID`,`deptID`,`type`,`day`,`message`,`reason` ,`date`) V |
| 10311453 | app | web1:40167 | app | Query | 77 | Locked | INSERT INTO D919.activity (`userID`,`deptID`,`salesID`,`nextContact`,`action`,`message `,`ca |
| 10311454 | app | web2:52218 | app | Query | 77 | Locked | UPDATE D98.activity SET `action`= 'true' WHERE id='1018641' |
| 10311493 | app | web1:40223 | app | Query | 67 | Locked | UPDATE D101.activity SET `message`= |
| 10311511 | app | web1:40242 | app | Query | 65 | Locked | UPDATE D744.activity SET `action`= 'true' WHERE id='186131' |
| 10311514 | app | web2:44165 | app | Query | 63 | Locked | INSERT INTO D197.user (`first`,`last`,`email`,`email1`,`dPhone`,`city`,`state`,`zi p`,`country`,`pur |
| 10311516 | app | web1:40248 | app | Query | 65 | Locked | INSERT INTO D444.user (`status`,`first`,`middle`,`last`,`address`,`city`,`state`,` zip`,`nickname`,` |
| 10311534 | app | web1:40276 | app | Query | 64 | Locked | INSERT INTO D970.activity (`deptID`,`salesID`,`userID`,`type`,`day`,`date`,`folder`,`e mailTo`,`e |
| 10311560 | app | web1:40314 | app | Query | 62 | Locked | INSERT INTO D824.activity (`userID`,`deptID`,`salesID`,`nextContact`,`action`,`message `,`ca |
| 10311565 | app | web1:40324 | app | Query | 61 | Locked | INSERT INTO D101.activity (`userID`,`deptID`,`salesID`,`nextContact`,`action`,`message `,`ca |
| 10311605 | app | web2:52298 | app | Query | 55 | Locked | UPDATE D270.activity SET `action`= 'true' WHERE id='475931' |
| 10311670 | app | web1:40490 | app | Query | 45 | Locked | UPDATE D962.activity SET `action`= 'true' WHERE id='57391' |
| 10311683 | app | web2:52330 | app | Query | 44 | Locked | UPDATE D308.activity SET `action`= 'true' WHERE id='200661' |
| 10311686 | app | web1:40518 | app | Query | 43 | Locked | INSERT INTO D718.activity (`testDrive`,`userID`,`deptID`,`salesID`,`nextContact`,`acti on`,` |
| 10311687 | app | web2:52332 | app | Query | 42 | Locked | UPDATE D716.activity SET `action`= 'true' WHERE id='40591' |
| 10311695 | app | web1:40532 | app | Query | 40 | Locked | INSERT INTO D566.activity (`testDrive`,`userID`,`deptID`,`salesID`,`nextContact`,`acti on`,` |
| 10311712 | app | web1:40548 | app | Query | 38 | Locked | INSERT INTO D734.activity (`deptID`,`salesID`,`userID`,`type`,`day`,`date`,`folder`,`e mailTo`,`e |
| 10311742 | app | web1:40604 | app | Query | 34 | Locked | UPDATE D744.activity SET `action`= 'true' WHERE id='186131' |
| 10311746 | app | web1:40608 | app | Query | 33 | Locked | UPDATE D970.activity SET `action`= 'true' WHERE id='738911' |
| 10311771 | app | web1:40646 | app | Query | 28 | Locked | INSERT INTO D101.activity (`deptID`,`salesID`,`userID`,`type`,`day`,`date`,`folder`,`e mailTo`,`e |
| 10311821 | app | web1:40726 | app | Query | 24 | Locked | INSERT INTO D1101.activity (`vehicleID`,`userID`,`deptID`,`salesID`,`type`,`message`,`d ay`,`date |
| 10311825 | app | web1:40732 | app | Query | 24 | Locked | INSERT INTO D419.user |
| 10311856 | app | web1:40792 | app | Query | 20 | Locked | INSERT INTO D970.activity (`deptID`,`salesID`,`userID`,`type`,`day`,`date`,`folder`,`e mailTo`,`e |
| 10311859 | app | web1:40798 | app | Query | 19 | Locked | INSERT INTO D956.activity (`testDrive`,`userID`,`deptID`,`salesID`,`nextContact`,`acti on`,` |
| 10311888 | app | web1:40846 | app | Query | 17 | Locked | UPDATE D882.activity SET `action`= 'true' WHERE id='543171' |
| 10311889 | app | web1:40848 | app | Query | 17 | Locked | INSERT INTO D905.activity (`userID`,`deptID`,`salesID`,`nextContact`,`action`,`message `,`ca |
| 10311944 | app | web1:40928 | app | Query | 12 | Locked | INSERT INTO D591.activity (`day`,`date`,`userID`,`salesID`,`type`,`deptID`,`subjec |
| 10311946 | app | web1:40932 | app | Query | 12 | Locked | INSERT INTO D487.activity (`userID`,`deptID`,`salesID`,`nextContact`,`action`,`message `,`ca |
| 10311973 | app | web1:40983 | app | Query | 7 | Locked | UPDATE D101.activity SET `read`= 'true' WHERE id='1864971' |
| 10311978 | app | web1:40995 | app | Query | 6 | Locked | INSERT INTO D566.activity (`deptID`,`salesID`,`userID`,`type`,`day`,`date`,`folder`,`e |
| 10311993 | app | web1:41017 | app | Query | 5 | Locked | UPDATE D744.activity SET `action`= 'true' WHERE id='186131' |
| 10312008 | app | web1:41041 | app | Query | 2 | Locked | UPDATE D938.activity SET `action`= 'true' WHERE id='990651' |
| 10312012 | app | web1:41047 | app | Query | 1 | Locked | UPDATE D525.activity SET `action`= 'true' WHERE id='269341' |
Report message to a moderator
Re: too many locked tables [message #3031 is a reply to message #3030 ] Thu, 01 May 2008 17:52 Go to previous messageGo to next message
erkules is currently offline erkules
Messages: 58
Registered: December 2007
Member
Hello Zedude,

have not a look at the processes in the state locked. show the others.
Report message to a moderator
Re: too many locked tables [message #3033 is a reply to message #3031 ] Thu, 01 May 2008 18:15 Go to previous messageGo to next message
zedude is currently offline zedude
Messages: 4
Registered: May 2008
Junior Member
There are a couple more processes... for replication. Nothing else.
Report message to a moderator
Re: too many locked tables [message #3034 is a reply to message #3033 ] Fri, 02 May 2008 06:49 Go to previous messageGo to next message
erkules is currently offline erkules
Messages: 58
Registered: December 2007
Member
The processes are locked by another process. It should be one shown in the processlist.
Is this a slave?
Report message to a moderator
Re: too many locked tables [message #3035 is a reply to message #3030 ] Fri, 02 May 2008 12:16 Go to previous message
zedude is currently offline zedude
Messages: 4
Registered: May 2008
Junior Member
The mysql slave process is updating different tables other than locked ones.

The question is, why so many unrelated queries (they are inserts and/or updates to different tables in different databases) are locked? And the number of locked queries keeps increasing.

Hopefully I've made myself clear...

Thanks!
Report message to a moderator
Previous Topic:1.1GB database not faster on SAS raid than single SATA drive!
Next Topic:multiple database performance
Goto Forum:
  

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [ ]


Current Time: Fri Nov 21 00:16:54 EST 2008

Total time taken to generate the page: 0.02970 seconds
.:: Contact :: Home :: MySQL Support by Percona.com ::.

Powered by: FUDforum 2.7.5.
Copyright ©2001-2006 FUD Forum Bulletin Board Software

MySQL Performance | Forum authority Badge