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
_______________________________________________
Analytics mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/analytics
