[Tilesathome] Status 'new' vs. 'pending'?

[Christopher Schmidt]

On Thu, Nov 15, 2007 at 11:51:55AM +0000, Robert (Jamie) Munro wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> 
> Christopher Schmidt wrote:
> > Earlier today, the tiles_queue database grew a bit too big for its
> > britches, and as a result, it started resorting to a filesort for
> > finding the next tile to request. (ouch.) As a result, I changed the
> > code, but I'm realizing now that I changed it incorrectly, and it iwll
> > need to go back to the way it was.
> > 
> > Specifically, the problem I'm having is that the test of finding 'high
> > priority tiles' where status <= 1 is expensive (but finding all the
> > tiles in status = 1 is easy and status = 0 is easy). 
> > 
> > Can someone explain to me what the difference between 'pending' and
> > 'new' is supposed to be? It seems like rendering from 'new' will let
> > clients sit without being rendered for a time, but always rendering from
> > pending will cause requests which don't get picked up in the first hour
> > to get missed as more come in behind them.
> > 
> > Why are these queues seperate? What does this help?
> 
> I think it was supposed to be a way of prioritising before someone added
> the priority
> 
> > And how should we
> > accomodate for the fact that this really is not cutting it for
> > performance? (Should we drop the low priority request count to get the
> > table back to being tiny and fast again? Is there a way to index a
> > column such that 'status <= 1' actually hits a key?) 
> 
> It won't hit the key unless the results are sorted by status, not priority.

In my 'explain' experiments, I wasn't sorting at all, and it still
wasn't using the key for status.

> I wondered about merging status and priority, so levels of priority
> would just be statuses higher than 10 or something.

That seems like the wrong way to go to me. Status is "Is it done yet?",
and priority is "When do you want it to be done?" (Now, Soon, Never.) I
don't see that merging those is the right path: instead, getting rid of
the two different 'is it *not* done yet?' queues seems like the right
way to go.

> Looking at when I changed the file, I tried doing to queries for the
> status, and merging them with a UNION statement in order to make maximum
> use of the keys. I don't know if this fully worked, but it lasted from:
> http://trac.openstreetmap.org/changeset/2937
> to:
> http://trac.openstreetmap.org/changeset/4508
> 
> Which is quite a long time.

Okay, I'll see if that matches up with a better index match while
skipping the current (two seperate sql queries) I've hacked in to
prevent the server from falling over ;)

Regards,
-- 
Christopher Schmidt
MetaCarta