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