Is this insane?

[Andreas Vierengel]

Marcus Bointon wrote:
> On 3 Dec 2006, at 22:30, Fi Dot wrote:
> 
>> Mmm.. maybe im missing the point here, but wouldn't it be better to do
>> all the lookups, construct one big query for all the data that's
>> missing, and get it all at once? Then you will have 1 DB query at
>> most.
> 
> Well, yes, but that could be quite a big, expensive query - how many 
> memcache lookups could you do in the same time? Having gone to the 
> expense of getting it, it makes sense to put all the resulting rows into 
> memcache. The only problem is that if you do this enough, you end up 
> doing more writes than reads. If you did a query just for the IDs, how 
> much of a saving would that be over getting everything, and how many 
> memcache queries would you be able to do in the difference?
> 
> Marcus
> --Marcus Bointon
> Synchromedia Limited: Creators of http://www.smartmessages.net/
> marcus at synchromedia.co.uk | http://www.synchromedia.co.uk/
> 
> 
> 

Hi, i did this in our environment.
I do one or more db-queries (with caching) to fetch the id's. Afterwards 
I fetch all id's in one multi_get, and fetch those which were misses in 
(mostly) one db-query ala WHERE id IN ( x,y,z, ... );
I wrote a whole framework FOR MY specific needs ( not applicable to the 
world :) which do this kind of stuff automagically for me (kind of 
ORM-System). Further I wrote my own memcached-client which gives me 
several other "benefits".
By now we have 2 customers in productive environment for this.
the worst case I have is about 250 objects on one page using perl.
my profiling with DProf and strace showed me that the most cpu- and 
time-intensive work is thawing the objects back to life (Storable). But 
I still get this worst case-page completely with all other stuff in 
about 0.2 seconds send out of my webservers (did this benchmarking 6 
month ago...)

The drawback of using completely mysql-db for this kind of work is about 
4-5 times slower access to our objects.
So my conclusion is, that this is not insane ( it works at least for us :)

We will push this technique further in the next year and relaunch a 
webportal on this basis with about 4-6 million page-requests per day.


--Andy