launchpad-dev team mailing list archive

[Robert Collins <robert.collins@xxxxxxxxxxxxx>]

On Tue, Sep 21, 2010 at 10:00 AM, Ian Booth <ian.booth@xxxxxxxxxxxxx> wrote:
> Hi
>
> In keeping with the theme of Performance Tuesday, I thought I'd ask a
> question about a particular implementation aspect since it potentially
> relates to performance.
>
> My question is: we don't appear to be using SQL prepared statements when
> accessing the database; why is this? Given the amount of SQL being
> executed, there's potentially a significant performance gain to be had,
> not to mention the reduced potential for SQL injection type attacks on
> the system. Having come from an Oracle background, using prepared
> statements is a no brainer for me. Perhaps there's a Python/Postgres
> issue I'm not aware of?
>
> Thanks for any insight.

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.

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.

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.

If we stay on SQL, then we're going to get performance down to the
point that optimising a few ms here and there starts to matter a great
deal: and in those cases working on prepared statements is going to go
into the regular kanban queues at such a time.

Of course, we probably need to be on cython / pypy / jython / or some
such at that point too.

-Rob