All users were logged out of Bugzilla on October 13th, 2018

alter get_collection_list_with_timestamps to avoid mysql crash

RESOLVED FIXED

Status

--
blocker
RESOLVED FIXED
8 years ago
8 years ago

People

(Reporter: tarek, Assigned: tarek)

Tracking

Firefox Tracking Flags

(Not tracked)

Details

(Whiteboard: [php: 616435] [qa-])

Attachments

(1 attachment, 2 obsolete attachments)

(Assignee)

Description

8 years ago
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.
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.
Severity: normal → blocker
(Assignee)

Comment 2

8 years ago
Created attachment 506418 [details] [diff] [review]
Back to the simpler query
Attachment #506418 - Flags: review?(telliott)
(Assignee)

Updated

8 years ago
Attachment #506418 - Flags: review?(rsoderberg)
(Assignee)

Comment 3

8 years ago
Created attachment 506421 [details]
Back to the simpler query
(Assignee)

Comment 4

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

added "username" to the group by /
Attachment #506418 - Attachment is obsolete: true
Attachment #506421 - Attachment is obsolete: true
Attachment #506422 - Flags: review?(telliott)
Attachment #506418 - Flags: review?(telliott)
Attachment #506418 - Flags: review?(rsoderberg)
(Assignee)

Updated

8 years ago
Attachment #506422 - Flags: review?(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
(Assignee)

Comment 6

8 years ago
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
Attachment #506422 - Flags: review?(rsoderberg) → review+
Attachment #506422 - Flags: review?(telliott) → review+
(Assignee)

Comment 7

8 years ago
commited in http://hg.mozilla.org/services/server-storage/rev/a24268073994
Status: NEW → RESOLVED
Last Resolved: 8 years ago
Resolution: --- → FIXED

Updated

8 years ago
Whiteboard: [php: 616435] → [php: 616435] [qa-]
You need to log in before you can comment on or make changes to this bug.