Loading '/' is too expensive

[Daniel Holbach]

Loading '/' is too expensive. The machine is getting more and more unhappy and this causes problems for summit, loco directory and other pages.

[Matthias Arnason (tiaz)]

More specifically, in the past day of recording execution time for pageloads, #2 and #3 slots for most expensive were fetching '/' and a number of #10+ were fetching 'robots.txt' which is handled directly out of urls.py, suggesting that the data for which urls are expensive or not has been corrupted by the system getting wedged.
I can also tell you that postgres spends its life basically idle and unless the system is wedged all queries are <500ms, and that the apache/python processes gradually grow memory-wise until they hit swap and run away entirely and the system starts not handling connections properly.

[Daniel Holbach (dholbach)]

A quick check with django debug toolbar tells me:

 - for '/': Total CPU time 264.016 msec (Elapsed time 399.378 msec) and 5 SQL queries using 30.74ms
 - for '/opportunities': Total CPU time 132.009 msec (Elapsed time: 156.254 msec) and 4 SQL queries using 30.74ms

The most expensive query on the mainpage takes 23.48ms and is:
SELECT COUNT(*) FROM "opportunities_opportunity" INNER JOIN "opportunities_opportunitylist" ON ("opportunities_opportunity"."opportunitylist_id" = "opportunities_opportunitylist"."id") WHERE ("opportunities_opportunity"."valid" = True AND "opportunities_opportunity"."last_updated" = "opportunities_opportunitylist"."last_updated")

As there's no quick fix for making the database schema easier, I suggest removing this query for now. Is there a clever way for us to cache the data, so we don't need to query for every single page view?

[Daniel Holbach (dholbach)]

Oops, for "/opportunities" it should have been: Total CPU time 132.009 msec (Elapsed time: 156.254 msec) and 4 SQL queries using *7.03ms* (so a lot less than for the mainpage).

[James Westby (james-w)]

On Wed, 17 Aug 2011 09:09:08 -0000, Daniel Holbach <email address hidden> wrote:
> A quick check with django debug toolbar tells me:
>
> - for '/': Total CPU time 264.016 msec (Elapsed time 399.378 msec) and 5 SQL queries using 30.74ms
> - for '/opportunities': Total CPU time 132.009 msec (Elapsed time: 156.254 msec) and 4 SQL queries using 30.74ms
>
> The most expensive query on the mainpage takes 23.48ms and is:
> SELECT COUNT(*) FROM "opportunities_opportunity" INNER JOIN "opportunities_opportunitylist" ON ("opportunities_opportunity"."opportunitylist_id" = "opportunities_opportunitylist"."id") WHERE ("opportunities_opportunity"."valid" = True AND "opportunities_opportunity"."last_updated" = "opportunities_opportunitylist"."last_updated")

Perhaps there's a DB index that would make this much quicker if it was
added.

I don't know how to work that out, or what index to test to see if it
helps though.

I know that Robert Collins and Clint Byrum would definitely know
though. I'm sure there are others too.

Thanks,

James

[Daniel Holbach (dholbach)]

Seems like Harvest was not the problem after all. The two branches should make '/' a lot quicker to load anyway. Marking as 'fix committed'.