618256 - alter get_collection_list_with_timestamps to avoid mysql crash

Status: RESOLVED FIXED

Description

[Tarek Ziadé (:tarek)]

sync 1.5.101116 includes a collection timestamp query that reliably and
simultaneously crashes the mysql servers in production.  it appears to stem
from the subquery i suggested using here.  we have monkeypatched in production
a revised query based on the original sync 1.3 query with one single change,
specifically "GROUP BY username, collection".  this has proven to be sufficient
to reap some performance reward of using the available indexes, without
crashing the mysql server.

Comment 1

[Atoll R. Soderberg [:atoll, :rsoderberg]]

old query still present, and we're having random crashes of mysql in staging when running sync tests, so it's very likely this is the root cause.

Comment 2

[Tarek Ziadé (:tarek)]

Created attachment 506418 [details] [diff] [review]
Back to the simpler query

Comment 3

[Tarek Ziadé (:tarek)]

Created attachment 506421 [details]
Back to the simpler query

Comment 4

[Tarek Ziadé (:tarek)]

Created attachment 506422 [details] [diff] [review]
Back to the simpler query

added "username" to the group by /

Comment 5

[Atoll R. Soderberg [:atoll, :rsoderberg]]

Comment on attachment 506422 [details] [diff] [review]
Back to the simpler query

Can you provide the resulting SQL query as sent to MySQL by the select() function?  r+ otherwise, pending that

Comment 6

[Tarek Ziadé (:tarek)]

SELECT wbo.collection, max(wbo.modified) AS max_1 FROM wbo WHERE wbo.username = %s AND wbo.ttl > %s GROUP BY wbo.username, wbo.collection

Comment 7

[Tarek Ziadé (:tarek)]

commited in http://hg.mozilla.org/services/server-storage/rev/a24268073994