Thanks Aaron, I don't have access to rt itself, I've been sending requests by email before. Should this go to ops-requests(a)rt.wikimedia.org or to another rt address? I was unable to find a list of those rt email addresses that I presume send the request to the right team/project in rt. On Mon, May 12, 2014 at 11:40 AM, Aaron Halfaker <ahalfaker(a)wikimedia.org>wrote;wrote:

The ones I'm trying to improve are querying MediaViewer_8245578 (just grep for that on the page you've linked to, there will be some things I ran manually today, but the big queries are the problematic ones). The existing queries are full table scans and seem to take around 15 minutes. We run 27 of these every day one after the other. The index I want to add will actually help me break those queries down into several smaller queries. There's no point trying to break them down right now, because I expect that without indexes the simpler queries will take just as long because they'll be full table scans as well. On Mon, May 12, 2014 at 12:32 PM, Sean Pringle <springle(a)wikimedia.org>wrote;wrote: > Hi Giles, Aaron > > Actually, I've started collecting data so we an analyze the analysts... or > at least your SQL ;-) > > https://tendril.wikimedia.org/report/slow_queries?host= > ^db1047&user=&schema=&qmode=eq&query=&hours=12 > > (only accessible to WMF staff) > As you no doubt know, adding indexes to an RDBMS arbitrarily will > eventually cause the system to struggle as overhead for writes increases. > Also, while indexing is necessary and definitely acceptable, it shouldn't > be the only port of call when performance tuning. So, I'd like to approach > this carefully: > > 1. For the day-to-day queries run by your tools, like most of those > queries on the URL above I expect, I'll begin adding some appropriate > indexes, and also follow up with you guys if queries can be redesigned to > be more efficient. > > First example awaiting feedback: > https://gerrit.wikimedia.org/r/#/c/131929/ > > 2. For the ad-hoc or massive reporting queries like those Aaron runs from > time to time, we give the halfak user DDL permissions for the "log" schema > just as Aaron suggested, and let him spearhead the effort because I won't > have a hope of predicting what you'll need. > > However, regarding #2: We need to tread carefully because DDL for index > operations is not necessarily something that can run any old time. Plain > old ALTER TABLE will cause replication lag and table metadata locks, and I > suggest whoever handles it should become familiar with percona toolkit. > (Aaron? still keen?) > > 3. For queries that need indexes on wiki schemas, I think they will always > need to be checked by Ops or a MW core dev. Obviously you guys could only > affect yourselves, but I bet you wouldn't appreciate having db1047 broken > by an incompatible upstream change. > > What do you think? > > BR > Sean > > _______________________________________________ > Analytics mailing list > Analytics(a)lists.wikimedia.org > https://lists.wikimedia.org/mailman/listinfo/analytics > >

Thanks for adding that index, that's exactly what I needed. I'll update the queries to what you suggested now. I actually looked into doing exactly that change earlier today, but couldn't get it to hit the timestamp index. I wasn't specifying a value for "wiki" (it's one of the queries we run, the "global" counts), and it wasn't hitting the index, probably because of NULL values. Now I can force it hit that new index, so everything should be back to sane execution time for those tables. On Mon, May 12, 2014 at 2:33 PM, Sean Pringle <springle(a)wikimedia.org>wrote;wrote:

On May 12, 2014 11:36 PM, "Gilles Dubuc" <gilles(a)wikimedia.org> wrote: the queries to what you suggested now. I actually looked into doing exactly that change earlier today, but couldn't get it to hit the timestamp index. I wasn't specifying a value for "wiki" (it's one of the queries we run, the "global" counts), and it wasn't hitting the index, probably because of NULL values. Now I can force it hit that new index, so everything should be back to sane execution time for those tables. I noticed the IS NOT NULL version appear. Forcing the index in that case may not help; I suspect not-null will just cause an index scan and double the overhead compared to the table scan (because the secondary index isn't clustered). However the log tables are using TokuDB, instead of InnoDB, now, so it should be possible to have multiple clustered indexes. Will experiment with it more tomorrow and report back.

So what's the best thing to do for now? Not adding "IS NOT NULL" and letting it do a full table scan? That global query is indeed the last one that remains noticeably slow (it's running over 120+ million rows). On Mon, May 12, 2014 at 4:46 PM, Sean Pringle <springle(a)wikimedia.org>wrote;wrote: > > On May 12, 2014 11:36 PM, "Gilles Dubuc" <gilles(a)wikimedia.org> wrote: > > > > Thanks for adding that index, that's exactly what I needed. I'll update > the queries to what you suggested now. I actually looked into doing exactly > that change earlier today, but couldn't get it to hit the timestamp index. > I wasn't specifying a value for "wiki" (it's one of the queries we run, the > "global" counts), and it wasn't hitting the index, probably because of NULL > values. Now I can force it hit that new index, so everything should be back > to sane execution time for those tables. > However the log tables are using TokuDB, instead of InnoDB, now, so it > should be possible to have multiple clustered indexes. Will experiment with > it more tomorrow and report back. > > _______________________________________________ > Analytics mailing list > Analytics(a)lists.wikimedia.org > https://lists.wikimedia.org/mailman/listinfo/analytics > >

On Tue, May 13, 2014 at 2:16 AM, Gilles Dubuc <gilles(a)wikimedia.org> wrote: There are zero NULL values for "wiki" in those tables, so forcing the secondary index won't help and will be slightly slower than a table scan on the clustered primary key (extra level of indirection for each row touched). I suggest removing the FORCE INDEX clauses entirely from both forms of the query. Just keep the conditions pushed down to the unioned tables for now. When filtering by a wiki name, the ix_*_wiki_timestamp indexes are chosen regardless. When not filtering by wiki, the optimizer should be free to choose the is_*_timestamp index if possible (ie, if the tables ever hold a lot of data older than the 30 day range).

On Tue, May 13, 2014 at 7:02 AM, Aaron Halfaker <ahalfaker(a)wikimedia.org>wrote;wrote: Great! I therefore added you to a pending gerrit review ;-) Yes, that would work. Also need to consider the analytics-store box. Its indexes probably should stay in sync with s1-analytics-slave, especially once it gets scratch space.