I think I have some idea about why "lock wait timeout" happened.
I noticed that the executing time of the below query is continuing to
grow with the test running.
(SELECT st.tag as tag,st.refcount as count from
SOCIALEVENTTAG as st order by st.refcount desc limit 50) order by tag
asc;
The query will do table scan which is time comsuming. And the query
will be executed in every homepage and login operations, which are
executed at high frequency.
With testing continue to run, the SOCIALEVENTTAG table size is
increasing (though stop increasing at 5000), the upper query executing
time is increasing. Then it will cause the query "update
SOCIALEVENTTAG set refcount = refcount + 1 where tag='$tag'" waiting
the lock longer and longer. Then when "lock wait timeout", the
transaction will rollback. And the situation is becoming worse and
worse, the throughput will drop sharply, then the result will fail.
I am thinking is the "SELECT ..." query executed too freqently? For
some websites, as far as I know, they will create a seperate table to
hold the top reference data, like one table to hold the top 50
refcount in this case. The table will be updated regurally, like every
10 min, instead of every homepage and login operations. The top
refcount data is not real-time, but for websites, it don't matter. It
is only for users to view. It can decrease the response time of the
websites.
How do you think this idea? Your suggestions/comments are appreciated.
Thx, Xuekun
On Mon, Aug 24, 2009 at 11:26 PM, Akara
Sucharitakul<[email protected]> wrote:
> Yes, the query reported by mysql and the query in the source match. And this
> query is executed once for each tag. However, from the message, it is
> inconclusive that we're dealing with deadlocks here. While it is unlikely
> different calls to AddEventResult.php will have exactly the same tags, they
> all have multiple tags and a good amount of them may also be the same for
> the multiple concurrent calls.
>
> In any case, the sorting ensures there wont be any out-of-sequence locking
> and should avoid deadlocks. I'm not sure this is an application problem as
> deadlocks will be logged, separate from lock timeouts. And this is not what
> you have seen. You my need to tune your database server a bit more or find a
> way to increase the timeout. Thanks.
>
> -Akara
>
> Xuekun Hu wrote:
>>
>> Hi, Guys
>>
>> I am doing Olio-php-0.1 load testing. Currently with 10000 users, the
>> apache logs showed "Lock wait timeout" error as below. PS. 9500 users
>> was passed.
>>
>> [Mon Aug 24 15:31:00 2009] [error] [client 192.168.224.1] exception
>> 'PDOException' with message 'SQLSTATE[HY000]: General error: 1205 Lock
>> wait timeout exceeded; try restarting transaction' in
>> /var/www/oliophp/classes/PDOConnection.php:77\nStack trace:\n#0
>> /var/www/oliophp/classes/PDOConnection.php(77): PDO->exec('update
>> SOCIALEV...')\n#1
>> /var/www/oliophp/public_html/addEventResult.php(101):
>> PDOConnection->exec('update SOCIALEV...')\n#2 {main}
>> ... ...
>>
>> From mysql "show innodb status" and "show processlist", the query
>> statement of "update SOCIALEVENTTAG set refcount = refcount + 1 where
>> tag='$tag'" were executing long time and the executing time were
>> continuted to increase.
>>
>> In the AddEventResult.php code, I found the comment of "// We need to
>> sort the tags before insert/update. Different tag sequences can lead
>> to deadlocks." I checked the change log that coming from OLIO-45
>> issue. However I still can't fully understand why there are deadlocks
>> happnened? Will different transactions calling to AddEventResult.php
>> get the same tag record log sequence? Can anyone explicate a bit more?
>>
>> I just suspect the "lock wait timeout" maybe also be caused by the
>> same possible dead lock.
>>
>> Thx, Xuekun
>
>
