I just killed 100+ 3-day unindexed research queries on dbstore1002. All replication streams were lagging by nearly 1 day, and /tmp was hundreds of GB.
This seems similar to the problem from ~2 weeks ago, when old /tmp did fill up. The queries were of the following form (but with some variation). We need some indexing scheme for MobileWebEditing* tables, or to come up with a new approach.
SELECT Month.Date, COALESCE(Web.Web, 0) AS Web
-- http://stackoverflow.com/a/6871220/365238 -- ... using MariaDB 10 SEQUENCE engine instead of information_schema.columns FROM ( SELECT DATE_FORMAT( ADDDATE(CURDATE() - INTERVAL 30 - 1 DAY, @num:=@num+1), '%Y-%m-%d' ) AS Date FROM seq_1_to_100, (SELECT @num:=-1) num LIMIT 30 ) AS Month LEFT JOIN ( SELECT DATE(timestamp) AS Date, SUM(1) AS Web
FROM (SELECT timestamp, wiki, event_username, event_action, event_namespace, event_userEditCount FROM MobileWebEditing_5644223 UNION SELECT timestamp, wiki, event_username, event_action, event_namespace, event_userEditCount FROM MobileWebEditing_6077315 UNION SELECT timestamp, wiki, event_username, event_action, event_namespace, event_userEditCount from MobileWebEditing_6637866 UNION SELECT timestamp, wiki, event_username, event_action, event_namespace, event_userEditCount from MobileWebEditing_7675117 UNION SELECT timestamp, wiki, event_username, event_action, event_namespace, event_userEditCount from MobileWebEditing_8599025) as MobileWebEditing WHERE event_action = 'error' AND wiki != 'testwiki' GROUP BY Date ) AS Web ON Month.Date = Web.Date;
EXPLAIN:
+------+--------------+--------------------------+--------+---------------+---------+---------+------------+----------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------+--------------------------+--------+---------------+---------+---------+------------+----------+----------------------------------------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 30 | | | 1 | PRIMARY | <derived4> | ref | key0 | key0 | 4 | Month.Date | 563154 | Using where | | 4 | DERIVED | <derived5> | ALL | NULL | NULL | NULL | NULL | 56315405 | Using where; Using temporary; Using filesort | | 5 | DERIVED | MobileWebEditing_5644223 | ALL | NULL | NULL | NULL | NULL | 1152600 | | | 6 | UNION | MobileWebEditing_6077315 | ALL | NULL | NULL | NULL | NULL | 685212 | | | 7 | UNION | MobileWebEditing_6637866 | ALL | NULL | NULL | NULL | NULL | 1528269 | | | 8 | UNION | MobileWebEditing_7675117 | ALL | NULL | NULL | NULL | NULL | 1663281 | | | 9 | UNION | MobileWebEditing_8599025 | ALL | NULL | NULL | NULL | NULL | 51286043 | | | NULL | UNION RESULT | <union5,6,7,8,9> | ALL | NULL | NULL | NULL | NULL | NULL | | | 2 | DERIVED | <derived3> | system | NULL | NULL | NULL | NULL | 1 | | | 2 | DERIVED | seq_1_to_100 | index | NULL | PRIMARY | 8 | NULL | 100 | Using index | | 3 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +------+--------------+--------------------------+--------+---------------+---------+---------+------------+----------+----------------------------------------------+
--- DBA @ WMF