I'm having a look at the performance of the front page. It just took 5,331ms to render, 4,556 of which was "SQL time." (It probably got faster once memcached was primed for my personal fragments).
Almost all of the time went into counting branches ("WHERE private = %s"—I assume %s is false; 2,005ms) and orphaned specifications (1,770ms).
The database can service the public-branches count in half a second once warmed up. (I'm not sure if we really need the branch count to exclude branches not visible to the user. Does that make sense for a global count?) In any case, there's probably no need for the global branch count to update interactively. We can get old but otherwise accurate numbers of public branches instantly from the postgres statistics.
The orphaned-specifications list went down to about 100ms on a warm database. Perhaps this information should be cached more aggressively in memcached. There doesn't seem to be anything personal about the information, so this may just be a matter of moving it from a personally-cached page fragment to a smaller, publicly-cached one.
Then, a lot of database queries for the featured-project listing could be avoided by prejoining Product, Product.icon (LibraryFileAlias), Product.icon.content (LibraryFileContent), and the licensing information (by rendering ProductWithLicenses objects instead of Product objects—see the ProductWithLicenses class). It would only save about 670ms, but if the other two queries can be elided then suddenly that's a big savings.
Finally, all of this should be coming from the slave store!
On Sun, Sep 12, 2010 at 3:45 PM, Jeroen T. Vermeulen
<email address hidden> wrote:
> Public bug reported:
>
> I'm having a look at the performance of the front page. It just took
> 5,331ms to render, 4,556 of which was "SQL time." (It probably got
> faster once memcached was primed for my personal fragments).
Do you mean 'launchpad.net', or one of the site specific root pages?
> Then, a lot of database queries for the featured-project listing could
> be avoided by prejoining Product, Product.icon (LibraryFileAlias),
> Product.
LibraryFileContent should never be needed by the appserver in GET requests.
If you saw requests for Product.
looking at lpnet; have a look at edge - it shouldn't be doing that
lookup anymore.
-Rob