I have problem with table lockings scince i upgraded the MySQL version, and i have
tried with
both RedHat 7.1 rpm version 2.23.36 and compile myself with 2.23.39. The result is the
same.
As can be seen in the "screenshot" below there is one process running, and a lot
sleeping. In
this example it happens to be an update running, but it might as well be an INSERT or
SELECT.
When the lock is released the server starts working on all locked querries at the same
time, the
result is a slow server with a load average around 60 where it can be run for long
times with load
between 1.00 and 2.00 on a dual cpu machine.
Any ideas on how to solve this? Is this standard behaivour? I hope not...
| 6899 | root | localhost | NULL | Query | 0 | NULL |
|show processlist
| 7697 | smurfa | smurfa-www | dbSmurfa | Sleep | 788 | |
|NULL
| 15079 | smurfa | smurfa-www | dbSmurfa | Query | 2 | Locked |
|SELECT Users.ID, Users.Alias
| 15083 | smurfa | smurfa-www | dbSmurfa | Sleep | 1 | |
|NULL
| 15090 | smurfa | smurfa-www | dbSmurfa | Query | 2 | Locked |
|SELECT Users.ID, Users.Alias
| 15091 | smurfa | smurfa-www | dbSmurfa | Query | 2 | Locked |
|SELECT Users.ID, Users.Alias
| 15094 | smurfa | smurfa-www | dbSmurfa | Query | 1 | end |
|UPDATE Users SET Logons=Logons+1,
| 15095 | smurfa | smurfa-www | dbSmurfa | Sleep | 1 | |
|NULL
| 15096 | smurfa | smurfa-www | dbSmurfa | Query | 2 | Locked |
|SELECT Users.ID, Users.Alias
| 15097 | smurfa | smurfa-www | dbSmurfa | Query | 2 | Locked |
|SELECT Users.ID, Users.Alias
| 15098 | smurfa | smurfa-www | dbSmurfa | Query | 2 | Locked |
|SELECT Users.ID, Users.Alias
| 15100 | smurfa | smurfa-www | dbSmurfa | Query | 1 | Locked |
|SELECT Users.ID, Users.Alias
| 15101 | smurfa | smurfa-www | dbSmurfa | Query | 2 | Locked |
|SELECT Alias FROM Users WHERE
| 15102 | smurfa | smurfa-www | dbSmurfa | Query | 2 | Locked |
|SELECT ID, Alias, Name, Email,
| 15103 | smurfa | smurfa-www | dbSmurfa | Query | 2 | Locked |
|SELECT ID, Alias, Password,
| 15104 | smurfa | smurfa-www | dbSmurfa | Query | 2 | Locked |
|SELECT ID, Alias, Name, Email,
| 15105 | smurfa | smurfa-www | dbSmurfa | Query | 2 | Locked |
|SELECT ID, Alias, Name, Email,
| 15106 | smurfa | smurfa-www | dbSmurfa | Query | 1 | Locked |
|SELECT Users.ID, Users.Alias,
| 15107 | smurfa | smurfa-www | dbSmurfa | Query | 2 | Locked |
|SELECT MezzIndex.ID, MezzIndex.
| 15108 | smurfa | smurfa-www | dbSmurfa | Query | 1 | Locked |
|SELECT Users.ID, Users.Alias,
| 15109 | smurfa | smurfa-www | dbSmurfa | Query | 1 | Locked |
|SELECT Users.ID, Users.Alias,
| 15111 | smurfa | smurfa-www | dbSmurfa | Query | 2 | Locked |
|SELECT MezzIndex.ID, MezzIndex.
| 15114 | smurfa | smurfa-www | dbSmurfa | Query | 1 | Locked |
|SELECT Alias FROM Users WHERE
| 15113 | smurfa | smurfa-www | dbSmurfa | Query | 1 | Locked |
|SELECT ID, Alias, Name, Email,
| 15115 | smurfa | smurfa-www | dbSmurfa | Query | 1 | Locked |
|SELECT Alias FROM Users WHERE
| 15118 | smurfa | smurfa-www | dbSmurfa | Query | 1 | Locked |
|SELECT MezzIndex.ID, MezzIndex
| 15117 | smurfa | smurfa-www | dbSmurfa | Query | 1 | Locked |
|SELECT MezzIndex.ID, MezzIndex
| 15124 | smurfa | smurfa-www | dbSmurfa | Query | 1 | Locked |
|SELECT ID, Alias, Name, Email