Almost all long-running queries are unnecesary:

* Some are done poorly and changing the query to use better indexing will help * Some are done because we still don't have a way to add "custom tables", so they are forced to do poor queries (scan full tables to do application-level joins)

There is, however, no easy fix, it has to be individual checks over every account. We do have, however, statistics about the acounts with more heavy querying.

On Wed, May 9, 2018 at 10:26 PM, Bryan Davis bd808@wikimedia.org wrote:

On Wed, May 9, 2018 at 1:07 PM, Jaime Crespo jcrespo@wikimedia.org wrote:

...

https://grafana.wikimedia.org/dashboard/file/server-board.

json?refresh=1m&panelId=12&fullscreen&orgId=1&var-server= labsdb1010&var-network=eth0&from=now-90d&to=now

...

What to do?

We don't have more hardware at this point, so I guess the two things that come to mind are:

• implement more query duration throttling
• find users making poorly written queries and try to help them improve

Throttling is not something that we love doing, but it is easy to scale on the implementation side. Helping people fix bad queries is more fun because it makes things nicer for everyone, but it takes a lot more time and that can be difficult to find.

Jaime, do you have any ideas that are better or a preference on which we try first?

Bryan

Bryan Davis Wikimedia Foundation bd808@wikimedia.org [[m:User:BDavis_(WMF)]] Manager, Cloud Services Boise, ID USA irc: bd808 v:415.839.6885 x6855

---

Cloud-admin mailing list Cloud-admin@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/cloud-admin