Is this insane?

[Paul T]

--- Allen Gilliland <allen.gilliland at sun.com> wrote:
> 
> Paul T wrote:
> > In reality fetching from, say, mysql (even by
> primary
> > key) is orders of magnitude slower than fetching
> from
> > memcached. Networking part is different, price for
> > opening (and keeping) the connection is different
> e
> > t.c. 
> 
> I have never run any raw benchmarks between
> memcached and a db, but I 
> would be *very* surprised if the difference between
> memcached.get(key) 
> and "select column from table" was orders of
> magnitude different.

I did the benchmarks. 5 times difference was easy to
get. On a trivial select * from table where id=N. 

Perhaps you should try some benchmarks on your
hardware/OS before we continue this thread? 

> > 
> > For example, memcached makes no calls to
> filesystem.
> > Basically, it is mostly OS that gets in the way.
> 
> Why would your db be going to the filesystem?  

To store the data. To swap out few pages of a binary
tree (index) e t.c.

> Sure,
> for write 
> operations it is going to disk, but not for reads,
> as long as you have 
> it configured properly.  

Would you try defining what do you mean by 'properly
configured' ... say ...  mysql ?

> A properly tuned db should
> be able to have all 
> the data cached in memory.

Says who?

Database engine has arrays and indices and caches. 
(Mysql, for example, has two different caches one for 
prepared statements and another for non-prepared ones)

All that stuff is fat - in consumes memory. The more
you pit into an index - the faster you can be on some
selects (but the more RAM you begin to consume)

At some point all those structures begin cosuming 'too
much' of a RAM comparing to lean and mean structures
used by memcached.

Also, in the real world, there are no databases that
are for reading only. In the real world the database 
engine has to handle both reads and writes in the same
time (google for 'insert delayed mysql'). Every write
implies re-building indices and doing some other heavy
stuff. 

Add threads (and the price associated with context
switching) to the game (memcached has none).

Or just do a simple benchmark maybe?

Rgds.Paul.

> -- Allen
> 
> 
> > 
> > Rgds.Paul.
> > 
> > PS. BTW, was there any mainstream relational
> database
> > designed to run on a cluster? I doubt.
> > 
> > --- Allen Gilliland <allen.gilliland at sun.com>
> wrote:
> > 
> >>
> >> mike wrote:
> >>> I'm looking at strategies to cache some data,
> and
> >> I just want to run
> >>> this by people to see how they use it or if what
> >> I'm thinking is just
> >>> insanity.
> >>>
> >>> Does it sound crazy to have 30-50 gets per page
> >> load? I'm thinking of
> >>> caching each row in a database basically
> treating
> >> it as an object
> >>> based on the primary key. Then fetching the
> cached
> >> information based
> >>> on a list of IDs (basically saving a query of
> >> SELECT allthedata FROM
> >>> allthetables WHERE PK IN(key1,key2,key3))
> >> I don't really think that 30-50 gets per page
> load
> >> is a problem, 
> >> particularly if they are gets from a cache, but I
> >> would be more worried 
> >> about the application infrastructure you are
> talking
> >> about in order to 
> >> design that solution.
> >>
> >> Perhaps I don't understand the problem well
> enough,
> >> but I don't see why 
> >> you want to replicate your database in your
> cache. 
> >> Your database should 
> >> be offering you excellent performance when doing
> >> single row lookups by a 
> >> primary key, that's what the database is designed
> >> for.
> >>
> >> -- Allen
> >>
> >>
> >>> I mean, not all pages will have this - but pages
> >> with lists of data
> >>> would; and each item in the list would basically
> >> be a row of precached
> >>> data in the database; it would have to be
> >> sorted/ordered and such
> >>> differently, so it wouldn't necessarily make
> sense
> >> to just cache the
> >>> entire result set as-is, I'm thinking row-based
> >> would be the most
> >>> effective way.
> >>>
> >>> Thoughts?
> >>>
> >>> Thanks,
> >>> mike
> > 
> > 
> > 
> >  
> >
>
____________________________________________________________________________________
> > Do you Yahoo!?
> > Everyone is raving about the all-new Yahoo! Mail
> beta.
> > http://new.mail.yahoo.com
> 



 
____________________________________________________________________________________
Want to start your own business?
Learn how on Yahoo! Small Business.
http://smallbusiness.yahoo.com/r-index