Now the problem was completely solved. Sorry for your inconvenience, and thank you for your patience.
Posted Feb 25, 2015 - 08:40 PST
Monitoring
We have created the patch, reviewed, tested, and deployed into production. The performance problem we've identified was solved, but we'll keep monitoring the system.
Posted Feb 25, 2015 - 08:09 PST
Update
As a next approach, we'll modify Web interface application to issue the different SQL query. This will allow MySQL optimizer to user appropriate index. We're testing the change in staging environment.
Posted Feb 25, 2015 - 07:30 PST
Investigating
We have tried to add index with online schema migration tool (pt-online-schema-change), but it locked the table for 2 minutes. We cancelled the operation at this point.
Posted Feb 25, 2015 - 07:03 PST
Update
We've identified what index we need to add to the database. We'll add the index to the table, which contains 20 million rows. This could cause read-lock for a while for API calls involving jobs as well. We'll keep you posted.
Posted Feb 25, 2015 - 06:49 PST
Identified
Starting from around 1AM PST, we started observing MySQL queries not using the index. These queries scan the entire table, which stores multiple gigabytes of entries. This started causing the performance degradation of our Web interface. We're working on resolving the issue. At this point, we do NOT have performance impacts on API side.