Now the problem was completely solved. Sorry for your inconvenience, and thank you for your patience.
Feb 25, 08:40 PST
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.
Feb 25, 08:09 PST
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.
Feb 25, 07:30 PST
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.
Feb 25, 07:03 PST
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.
Feb 25, 06:49 PST
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.
Feb 25, 01:33 PST