Sorry, that was me.

I would urge you to setup a per-user/per-service query resource limits,

otherwise poorly performant queries will affect all users (and in cases like this, create downtime). I have >set up query limits for all research/analytics users temporarily until 3rd January.

Could we make this limits permanent? I do not see a reason not to do that. Let me know if I am missing something.

Thanks,

Nuria

On Thu, Dec 29, 2016 at 12:56 PM, Dan Andreescu dandreescu@wikimedia.org wrote:

Thanks Jaime, we will use the Summit to show people how to use Hadoop / Druid for some of this analysis‎, and that should get these types of problems off your plate.

*From: *Jaime Crespo *Sent: *Sunday, December 25, 2016 04:57 *To: *A mailing list for the Analytics Team at WMF and everybody who has an interest in Wikipedia and analytics. *Reply To: *A mailing list for the Analytics Team at WMF and everybody who has an interest in Wikipedia and analytics. *Subject: *[Analytics] analytics-store was down due to excessive load

analytics-store was brought down at 6am, and then again at 9am UTC 25 Dec due to multiple executions of long running queries (some of them 2 days long) such as:

SELECT LEFT(timestamp, 8) AS yearmonthday, timestamp, userAgent, clientIp, webHost, COUNT(*) AS copies FROM log.PageContentSaveComplete ...

SELECT COUNT(*) AS count, term_entity_type, term_type, term_language FROM wikidatawiki.wb_terms ...

select date('20161218000000') as day, actions, count(*) as repeated from (select group_concat(event_action order by timestamp, action_order.ord separator '-') as actions from (select ...

I would urge you to setup a per-user/per-service query resource limits, otherwise poorly performant queries will affect all users (and in cases like this, create downtime). I have set up query limits for all research/analytics users temporarily until 3rd January.

-- Jaime Crespo http://wikimedia.org

---

Analytics mailing list Analytics@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/analytics

On Wed, Jan 4, 2017 at 1:21 PM, Addshore addshorewiki@gmail.com wrote:

The query listed selecting from wikidatawiki.wb_terms was me / wmde-analytics and runs daily.

I agree that some sort of query limits would make sense. What limits are currently in place?

The wikidata queries I have seen are probably the most concerning, they are full scan+very large sorts of the wb_terms table > 700 times a day (which means server query pileups), each one time taking over 2 hour (the former query limit), on what I assume is an append-mostly table. With a few easy tweaks (some triggers or a query rewrite), the same data could be obtained instantly instead of taking so many resources.

Please ask for a hand (you can do it privately)- it is actually faster and easier, and I am here to help, and you will actually make my life easier if servers continue being up :-).

I will talk about this at the Wikimedia developers conference https://phabricator.wikimedia.org/T149624 , a session I am inviting you all you to go to if you are attending the conference. I will talk about the new analytics labsdb service (with new servers!) and provide some feedback that applies to the analytics boxes, too.