Yii Framework Forum: Migrating From 1.1.12 To 1.1.14 Causes Sql Pileup - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Migrating From 1.1.12 To 1.1.14 Causes Sql Pileup

#1 User is offline   duukkis 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 4
  • Joined: 16-October 13

Posted 16 October 2013 - 03:37 AM

Hi,

we have a site that runs on Yii 1.1.12 and we have tried to migrate it to 1.1.14. On production it causes massive amount of database queries. 900 query pileup on heavy load when normal on heavy load is around 80 (this seen with SHOW PROCESSLIST) and the MySQL dies as a result of that. 95% of our users are registered, the anonymous get Nginx-cached version. The heavy load is about 4000 simultanious users (based on Google analytics and our own online status).

We have checked through the changes from 1.1.12 to 1.1.14 and can't just pinpoint the cause of this. In xhprof we see that .14 is faster on a single pageload in all the pages, but in production the application just fails.

One thing that might be the issue is our ActiveRecords:
class Message extends CActiveRecord
and
class MessageHistory extends Message
and the latter has one method extra than Message and tablename is different dbchat_message_history instead of dbchat_message. The history contains 7 million rows and message table 35 million, but the application runs smoothly on 1.1.12.

Any ideas?

Thanks in advance.
0

#2 User is offline   samdark 

  • Having fun
  • Yii
  • Group: Yii Dev Team
  • Posts: 3,385
  • Joined: 17-January 09
  • Location:Russia

Posted 16 October 2013 - 04:33 AM

Sounds like caching issue and not something connected to AR. Can you enable slow query log to collect these repeating 820 queries and post these here?
Yii 1.1 Application Development Cookbook

Enjoying Yii? Star us at github: 1.1 and 2.0.
0

#3 User is offline   duukkis 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 4
  • Joined: 16-October 13

Posted 16 October 2013 - 06:58 AM

View Postsamdark, on 16 October 2013 - 04:33 AM, said:

Sounds like caching issue and not something connected to AR. Can you enable slow query log to collect these repeating 820 queries and post these here?


Sounds like, but is not. On dev, there are no slow queries. And in prod or dev no un-indexed joins.

The repeating queries load profiles in a rather big left join (const, const, const, const, PRIMARY, PRIMARY) and the other one loading the profile is using index and using where. Then the profiles are stored to memcache and expired on profile update. They use keys and are very fast even though they are called often. The fetching of messages is pretty slow, but that is not seen at all on the repeating queries.

What we are seeing in SHOW PROCESSLIST is lots of "Opening tables" in connections which fetch profiles.

AND the application code and database setup stays exactly the same, but if we change Yii to 1.1.14 from 1.1.12, the application just dies because of these queries piling up.
0

#4 User is offline   samdark 

  • Having fun
  • Yii
  • Group: Yii Dev Team
  • Posts: 3,385
  • Joined: 17-January 09
  • Location:Russia

Posted 16 October 2013 - 07:17 AM

That's very strange and I unfortunitely I have no idea why it's like that for your project.
If you can reproduce it w/o production environment or by replicating it somehow you can probably try 1.1.13 and then do git bisect to find out which change in the framework causes it.
Yii 1.1 Application Development Cookbook

Enjoying Yii? Star us at github: 1.1 and 2.0.
0

#5 User is offline   duukkis 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 4
  • Joined: 16-October 13

Posted 16 October 2013 - 07:21 AM

View Postsamdark, on 16 October 2013 - 07:17 AM, said:

That's very strange and I unfortunitely I have no idea why it's like that for your project.
If you can reproduce it w/o production environment or by replicating it somehow you can probably try 1.1.13 and then do git bisect to find out which change in the framework causes it.


Yeah. The 1.1.13 was the next idea, so we could narrow the problem. Thanks anyway.
0

#6 User is offline   duukkis 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 4
  • Joined: 16-October 13

Posted 04 December 2013 - 05:00 AM

View Postduukkis, on 16 October 2013 - 07:21 AM, said:

Yeah. The 1.1.13 was the next idea, so we could narrow the problem. Thanks anyway.



settings issue. forget.
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users