Migrating From 1.1.12 To 1.1.14 Causes Sql Pileup


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


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.

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.

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.

settings issue. forget.