launchpad-dev team mailing list archive

[Ian Booth <ian.booth@xxxxxxxxxxxxx>]

Hi Robert,

Thanks for the information.

> 
> I don't think we've ever had Launchpad tuned to the point that this
> would be a significant win: we're looking at multi second queries
> needing improving, not 10's of ms ones.
> 

It's not so much the 10's of ms for each query, but the cumulative
effect of the resources (mainly CPU load) required on the database
server to parse the SQL over and over again. There's two main multiplier
effects at work: SQL volume and concurrent access load ie number of
concurrent users. I know there's tuning occurring right now, but some
views seem to result in 1000's of SQL queries and even once these are
eliminated, there will be other similar inefficiencies introduced later
for sure. Any complex system with potential bottlenecks like CPU
contention or even seemingly innocuous db throughput issues can very
quickly become unstable since effectively a positive feedback loop is
created. This can be exacerbated also by the effects of external factors
such as other applications on the same hardware "stealing" core
resources like I/O bandwidth etc.

> I agree that they are a no brainer win, but I suspect we'd need some
> infrastructure work in storm to bring them in.
> 
> If you, or someone else wants to work on them in the near term, brilliant.
>

It's on my todo list to look at the storm codebase and persistence layer
in lp in more detail :-) I'm happy to get stuck in once I've got a
better understanding of this aspect of the system architecture.


> In the medium to long term, there are a few considerations to bear in mind.
> 
> Firstly, once we have some breathing space on performance, we're going
> to be reevaluating our DB stack : we need things like automatic
> failover and write scaling that are absent today. One possible outcome
> there is a move away from postgresql (or even SQL per se) in which
> case investment in storm & stored procedures may be a dead end. I
> don't have any feeling for how likely that is yet: it would be a huge
> effort to move and theres lots of things to consider in evaluations in
> that area.
>

One of the mandatory things IMHO is getting persistence infrastructure
out of the core domain model :-D

Thanks,
Ian