267853 - some SELECT queries block others from running

Status: NEW

(Bugzilla :: Database, defect)

Description

[Myk Melez [:myk] [@mykmelez]]

Most SELECT queries can run simultaneously, but some prevent others from
running, and other queries stall waiting for the blocking queries to complete. 
It would be good to know why those queries are blocking others and find some way
to fix that, if possible to improve performance, especially on Bugzilla
installations that don't use a shadow database.

Here are two queries known to cause that problem (replace [ USER_ID ], [
USER_ID1 ], [ USER_ID2 ], and [ LOGIN_NAME ] with some valid values):

SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status,
bugs.resolution, bugs.bug_severity, bugs.priority, bugs.rep_platform,
map_assigned_to.login_name, bugs.bug_status, bugs.resolution, bugs.short_desc
FROM bugs, profiles AS map_assigned_to, bugs_activity act_0, fielddefs
fielddefs_0 LEFT JOIN cc cc_0 ON bugs.bug_id = cc_0.bug_id AND cc_0.who IN([
USER_ID ]) LEFT JOIN bug_group_map  ON bug_group_map.bug_id = bugs.bug_id  AND
bug_group_map.group_id NOT IN (9,10)  LEFT JOIN cc ON cc.bug_id = bugs.bug_id
AND cc.who = [ USER_ID ] WHERE bugs.assigned_to = map_assigned_to.userid AND
act_0.bug_id = bugs.bug_id AND act_0.fieldid = fielddefs_0.fieldid AND
act_0.bug_id = bugs.bug_id AND act_0.fieldid = fielddefs_0.fieldid AND
act_0.bug_id = bugs.bug_id AND act_0.fieldid = fielddefs_0.fieldid AND
act_0.bug_id = bugs.bug_id AND act_0.fieldid = fielddefs_0.fieldid AND
act_0.bug_id = bugs.bug_id AND act_0.fieldid = fielddefs_0.fieldid AND
act_0.bug_id = bugs.bug_id AND act_0.fieldid = fielddefs_0.fieldid AND
act_0.bug_id = bugs.bug_id AND act_0.fieldid = fielddefs_0.fieldid AND
(((fielddefs_0.name = 'rep_platform' AND act_0.who = [ USER_ID ])) OR
((fielddefs_0.name = 'component' AND act_0.who = [ USER_ID ])) OR
((fielddefs_0.name = 'short_desc' AND act_0.who = [ USER_ID ])) OR
((fielddefs_0.name = 'product' AND act_0.who = [ USER_ID ])) OR
((fielddefs_0.name = 'bug_severity' AND act_0.who = [ USER_ID ])) OR
((fielddefs_0.name = 'op_sys' AND act_0.who = [ USER_ID ])) OR
((fielddefs_0.name = 'bug_status' AND act_0.who = [ USER_ID ])) OR (cc_0.who IS
NOT NULL)) AND ((bug_group_map.group_id IS NULL)    OR (bugs.reporter_accessible
= 1 AND bugs.reporter = [ USER_ID ])     OR (bugs.cclist_accessible = 1 AND
cc.who IS NOT NULL)     OR (bugs.assigned_to = [ USER_ID ]) OR (bugs.qa_contact
=[ USER_ID ]) ) GROUP BY bugs.bug_id ORDER BY bugs.bug_status,bugs.bug_id


SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status,
bugs.resolution, bugs.bug_severity, bugs.priority, bugs.rep_platform,
map_assigned_to.login_name, bugs.bug_status, bugs.resolution, bugs.short_desc
FROM bugs, profiles AS map_assigned_to, profiles AS map_reporter LEFT JOIN cc
cc_CC0 ON bugs.bug_id = cc_CC0.bug_id AND cc_CC0.who IN([ USER_ID1 ]) LEFT JOIN
bug_group_map  ON bug_group_map.bug_id = bugs.bug_id  AND bug_group_map.group_id
NOT IN (9,10)  LEFT JOIN cc ON cc.bug_id = bugs.bug_id AND cc.who = [ USER_ID2 ]
WHERE bugs.assigned_to = map_assigned_to.userid AND bugs.assigned_to =
map_assigned_to.userid AND bugs.reporter = map_reporter.userid AND
(bugs.bug_status IN ('NEW','ASSIGNED','REOPENED')) AND
((map_assigned_to.login_name IN ('[ LOGIN_NAME ]')) OR (map_reporter.login_name
IN ('[ LOGIN_NAME ]')) OR (cc_CC0.who IS NOT NULL)) AND ((bug_group_map.group_id
IS NULL)    OR (bugs.reporter_accessible = 1 AND bugs.reporter = [ USER_ID2 ]) 
   OR (bugs.cclist_accessible =1 AND cc.who IS NOT NULL)     OR
(bugs.assigned_to = [ USER_ID2 ]) OR (bugs.qa_contact = [ USER_ID2 ]) ) GROUP BY
bugs.bug_id ORDER BY bugs.bug_id

Comment 1

[Dave Miller [:justdave]]

What those queries have in common is that they contain a GROUP BY clause, which
requires a temporary table to be created.  Does MySQL have a limit to the number
of temporary tables, or does the process of creating one block other queries?

In my watching of the process lists, when things were blocked up, the blocking
process was inevitably in the state 'Copying to temp table'.

Comment 2

[Myk Melez [:myk] [@mykmelez]]

Today I ran a slow query "select count(distinct(client)) from entries" against
the downloadstats database.  It created a temporary table.  While it was
"Sending data" queries against the "bugs" database would occasionally get
blocked for a couple minutes at a time before getting unblocked without any
apparent change to the status of the downloadstats query.

Comment 3

[Bradley Baetz (:bbaetz)]

show variables should show you this.

There is a limit, but its usually high enough to be ok.

What mysql version are you running?

Comment 4

[Myk Melez [:myk] [@mykmelez]]

4.0.14.  Here's what the variables say:

mysql> show variables like '%tmp%';
+----------------+----------+
| Variable_name  | Value    |
+----------------+----------+
| max_tmp_tables | 32       |
| tmp_table_size | 33554432 |
| tmpdir         | /tmp/    |
+----------------+----------+
3 rows in set (0.00 sec)

Comment 5

[Bradley Baetz (:bbaetz)]

You're not out of room on /tmp? /tmp isn't a ramfs, is it?

Can you replicate this on the latest 4.0? If so, may be worth filing a bug report.

Comment 6

[Dave Miller [:justdave]]

Reassigning bugs that I'm not actively working on to the default component owner
in order to try to make some sanity out of my personal buglist.  This doesn't
mean the bug isn't being dealt with, just that I'm not the one doing it.  If you
are dealing with this bug, please assign it to yourself.

Comment 7

[Max Kanat-Alexander]

Is this still a problem?

Comment 8

[David Faure]

Yes, this is happening all the time on bugs.kde.org -- openSUSE 11.0, bugzilla 3.0, mysql 14.12.

mysqladmin processlist shows "Copying to tmp table" for a few queries (usually 1 to 3) and "locked" for all other queries.

One example (using processlist -v)
| 6855 | bugs | localhost | bugs | Query   | 815  | Copying to tmp table | SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status, bugs.resolution, bugs.bug_severity, bugs.priority, bugs.op_sys, map_assigned_to.login_name, bugs.bug_status, bugs.resolution, bugs.short_desc, (SUM(MATCH(longdescs_.thetext) AGAINST('Javascript dos' )) + CASE WHEN bugs.short_desc LIKE '%javascript%' THEN 7 ELSE 0 END + CASE WHEN bugs.short_desc LIKE '%dos%' THEN 7 ELSE 0 END) AS relevance FROM bugs  INNERJOIN profiles AS map_assigned_to ON (bugs.assigned_to = map_assigned_to.userid) LEFT JOIN longdescs AS longdescs_ ON (bugs.bug_id = longdescs_.bug_id ) LEFT JOIN bug_group_map  ON bug_group_map.bug_id = bugs.bug_id  LEFT JOIN cc ON cc.bug_id = bugs.bug_id AND cc.who = 81866 WHERE ((bugs.product_id IN (92)) AND (MATCH(longdescs_.thetext) AGAINST('Javascript dos' ) > 0 OR (bugs.short_desc LIKE '%javascript%' OR bugs.short_desc LIKE '%dos%'))) AND bugs.creation_ts IS NOT NULL AND ((bug_group_map.group_id IS NULL)    OR (bugs.reporter_accessible = 1 AND bugs.reporter = 81866)     OR (bugs.cclist_accessible = 1 AND cc.who IS NOT NULL)     OR (bugs.assigned_to = 81866) ) GROUP BY bugs.bug_id ORDER BY relevance desc LIMIT 200   

Killing those queries makes the site work again, but it would be nice to have a less manual solution :)
I guess this is a mysql problem more than a bugzilla problem though (?).

Comment 9

[Max Kanat-Alexander]

(In reply to comment #8)
> Yes, this is happening all the time on bugs.kde.org -- openSUSE 11.0, bugzilla
> 3.0, mysql 14.12.

  Might be better in 3.2, since we don't lock tables anymore and we've moved to InnoDB.

> I guess this is a mysql problem more than a bugzilla problem though (?).

  Yeah, or a MyISAM problem in particular, or a filesystem problem, or a kernel problem. At least, those are my guesses.

Comment 10

[Max Kanat-Alexander]

  This link has some interesting information about optimizing MySQL's temporary table usage:

  http://www.mysqlperformanceblog.com/2007/08/16/how-much-overhead-is-caused-by-on-disk-temporary-tables/

Comment 11

[Bradley Baetz (:bbaetz)]

What are the blocking queries on recent buzgilla/myqsl versions? The KDE one looks like its using full text search, which will still be a problem.