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.
Posted April 26th, 2006 by Denis Roy in category: Uncategorized
You can skip to the end and leave a response. Pinging is currently not allowed.
4 Responses to “Bugzilla SQL rocket science”
Leave a Reply
You must be logged in using your Eclipse Bugzilla account to post a comment.
Test User Says:
April 26th, 2006 at 10:41 am
Just a thought.. may not apply to this very SQL usage.
The SQL “select id from table where id = :value”
could be a very efficient method to validate if the provided value is actually present in the table or not.
For eg, if the value is 100, this SQL will help check if there is a bug with id 100.
This SQL is definitely better than select count(*) from table where id = :value
Denis Roy Says:
April 26th, 2006 at 10:51 am
Yeah, I’m probably missing something. I thought the same, but if you want to test if a value is valid, isn’t it because you’re going to do something with it? For instance, if you don’t know whether to INSERT or UPDATE when someone hits “Save”, knowing that (with Bugzilla anyway) you UPDATE bugs more often than you insert them, wouldn’t it make sense to optimistically UPDATE, trap any possible error (because the bug doesn’t exist), then INSERT if an error occurred?
Saves a hit to the database… But I’m probably missing something…
SDiZ Says:
April 26th, 2006 at 11:17 am
If you have read the code, you will know what it tries to do:
< <<<<<<<<<<<
# make sure the bugs exists
my ($bugid);
foreach $bugid (@bugids) {
SendSQL(”SELECT bug_id FROM bugs WHERE bug_id = $bugid;”);
my $found_id = FetchOneColumn();
print “Did we find the bug? $found_id-\n”;
if (!defined($found_id)) {
if ($bugid == $bugids[$#bugids]) {
DealWithErrorFatal(”Bug $bugid does not exist”);
} else {
DealWithError(”Bug $bugid does not exist”);
}
} else {
# shove it in the table
my $long_desc_query = “INSERT INTO longdescs SET bug_id=$found_id, who=$userid, bug_when=NOW(), thetext=” . SqlQuote($Body) . “;”;
SendSQL($long_desc_query);
system(”./processmail”, $found_id, $SenderShort);
}
}
>>>>>>>>>>>>
Yes, bugzilla don’t use foreign keys.
Denis Roy Says:
April 26th, 2006 at 12:12 pm
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.