Wednesday, April 26, 2006

Bugzilla SQL rocket science

I was monitoring MySQL database activity this morning (our master DB has been acting up lately) and I happened to see this query fly by, courtesy of Bugzilla:

SELECT bug_id FROM bugs WHERE bug_id = 73050

Um. Okay, I'll bite. What is bug_id 73050's bug_id?

mysql> SELECT bug_id FROM bugs WHERE bug_id = 73050;
| bug_id |
| 73050 |
1 row in set (0.00 sec)

Wow. What a shocker. Maybe I'm missing something, but sometimes I stop wondering why Bugzilla is so slow.


Anonymous Denis Roy said...

It seems strange that the validation SQL statement would go to the slave, knowing the slave can (potentially) be out-of-sync for a short period because of locked tables.

For instance,

- User runs massive search. bugs table on slave gets locked for 30 seconds
- Meanwhile, another user adds bug # 10000. Write succeeds on master, slave insert is queued
- User then updates the longdescs for bug 10000. Validation SQL goes to slave, which is lagged by a lock, gets error because bug doesn't exist, or needs to wait for several seconds because the bugs table is locked on slave

The validation SQL has no joins and it's searching on the PK, which is an integer - the result would return instantaneously even if there were 100 million rows in the table. No reason this query could not go to the master to avoid potentially lagging users for a long time.

8:12 AM  

Post a Comment

<< Home