1330728 - We're making 20,000-120,000 requests/min to MySQL, mainly during data ingestion

Status: RESOLVED INCOMPLETE

(Tree Management :: Treeherder: Infrastructure, defect, P2)

Description

[Ed Morley [:emorley]]

<emorley> I'm really puzzled, read IOPS on the stage RDS has been much higher (like 2-4x) the equivalent on prod's RDS, even though the load should be lower, and even though almost the same revisions on both. it's been causing backlogs of log cross-reference jobs
<camd> any api submit traffic or anything?
<emorley> stage RDS is an m4.xlarge vs prod's m4.2xlarge, so does have half the RAM
<emorley> the API traffic looks low on stage
<emorley> unless we're now borderline things not fitting in RAM on stage
<emorley> We're peaking at 80,000-120,000 requests per minute to MySQL on both stage and prod, which would imply that it's mostly our own ingestion that's making not just the inserts but SELECTs too
<emorley> I'm presuming there are a few ORM cases that we'll want to optimise now things have settled post-migration
<wlach> that 80,000->120,000 seems very very high, we're probably doing something dumb
<wlach> it is possible I broke some caching logic when doing the migration, but it should be easy to fix
<emorley> I mean that's the peak, the "quiet" spells are 20,000
<wlach> 20,000 is still quite high

Comment 1

[Ed Morley [:emorley]]

A starting point is probably:
https://rpm.newrelic.com/accounts/677903/applications/14179757/datastores
-> sort by throughput
-> flick through query types, looking at the "time consumption by caller" panel
-> for offending transactions, switch to the "transactions" view then select "non-web transactions" then drill down into the profiles for that transaction and see if each transaction is doing anything crazy wrt number of DB calls.

At first glance, fetch-buildapi-running and log-parser are pretty high up the list. buildapi-running being quite surprising, given it's not actually that large a file iirc.