You are viewing a plain text version of this content. The canonical link for it is here.
Posted to olio-dev@incubator.apache.org by Xuekun Hu <xu...@gmail.com> on 2009/09/03 13:18:44 UTC

Re: AddEventResult.php question?

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<Ak...@sun.com> 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
>
>

Re: AddEventResult.php question?

Posted by Xuekun Hu <xu...@gmail.com>.
Yes, you ae right, should use memcached. :-) Currently I didn't use
memcached. :-)

On Fri, Sep 4, 2009 at 12:50 AM, Akara
Sucharitakul<Ak...@sun.com> wrote:
> Please see below...
>
> Xuekun Hu wrote:
>>
>> 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.
>
> Unless you turn off the cache, homepage and login are cached so this query
> is only run once in a fairly long while.
>
>>
>> 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.
>
> Again, that's true if cache is off. We know the tag cloud generation query
> is rather heavy. Also, there is no sensitivity to whether the tag cloud is
> actually up-to-date or not. With caching, the query is run only every 5
> minutes. You should not even notice it.
>>
>> 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.
>
> We use memcached to cache that info instead of a separate table.
>
> -Akara
>

Re: AddEventResult.php question?

Posted by Akara Sucharitakul <Ak...@Sun.COM>.
Please see below...

Xuekun Hu wrote:
> 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.

Unless you turn off the cache, homepage and login are cached so this 
query is only run once in a fairly long while.

> 
> 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.

Again, that's true if cache is off. We know the tag cloud generation 
query is rather heavy. Also, there is no sensitivity to whether the tag 
cloud is actually up-to-date or not. With caching, the query is run only 
every 5 minutes. You should not even notice it.
> 
> 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.

We use memcached to cache that info instead of a separate table.

-Akara