965984 - High load spike caused by excessive middleware queries coming from socorro.external.postgresql.search.Search

Status: RESOLVED FIXED

(Socorro Graveyard :: Middleware, defect)

Description

[Selena Deckelmann :selenamarie :selena]

Here's an example: 

/* socorro.exte
rnal.postgresql.search.Search search.count */ SELECT count(DISTINCT r.signature) FROM reports r 
            WHERE r.date_processed BETWEEN '2014-01-02T20:00:00+00:00'::timestamptz AND '2014-01-30T20:00:00+00:00'::timestamptz
         AND (r.product='Firefox')

and: 

/* socorro.external.postgresql.search.Search search.count */ SELECT count(DISTINCT r.signature) FROM reports r 
            WHERE r.date_processed BETWEEN '2014-01-02T20:00:00+00:00'::timestamptz AND '2014-01-30T20:00:00+00:00'::timestamptz
         AND (r.product='Firefox') AND ((r.release_channel NOT IN ('beta', 'aurora', 'nightly') AND r.product='Firefox' AND r.version='26.0'))


I saw 164 of these at the same time, with the count rising as I looked at the problem.


Trouble seemed to start here: 

2014-01-30 20:39:23.365 GMT,"breakpad_rw","breakpad",13262,"[local]",52eab6c1.33ce,1,"SELECT",2014-01-30 20:32:01 GMT,16/757945,0,LOG,00000,"temporary file: path ""base/pgsql_tmp/pgsql_tmp13262.0"", size
 509878272",,,,,,"/* socorro.external.postgresql.search.Search search.count */ SELECT count(DISTINCT r.signature) FROM reports r 
            WHERE r.date_processed BETWEEN '2014-01-02T20:00:00+00:00'::timestamptz AND '2014-01-30T20:00:00+00:00'::timestamptz
         AND (r.product='Firefox')",,,""


That's an unpleasant query!

Comment 1

[[DEACTIVATED] Adrian Gaudebert]

I think we want to switch Advanced Search back to elasticsearch. It's much better at handling this kind of queries. 

Filed bug 966212 for this.

Comment 2

[[DEACTIVATED] Adrian Gaudebert]

We are now using elasticsearch as a back-end for Advanced Search. This should not happen anymore.