MySQL cluster vs memcached

[Jed Reynolds]

Kevin Burton wrote:
> We really suffer from MySQL replication scalability issues.  Basically 
> you can scale reads but not writes.......
>
> I'm thinking of migrating some portions of our app to MySQL cluster 
> because basically it's an all memory DB and (in theory) should scale 
> linearly if we need to index more data.
>
> Has anyone compared the performance of memcached vs MySQL cluster?  
> I'm wondering if we shouldn't be buffering MySQL cluster with 
> memcached........  I'm willing to bet its fast enough that I don't 
> have to..... thoughts?

Get something on the bench to test first if you can. I suspect you'll 
notice a definite change as the load moves off your db cluster back to 
your web server.

I've just started caching some of the highly requested queries in 
memcache and when an agressive RSS agreggator or magpie client hits my 
site, I see my webserver increase in load, increase in httpd workers, 
but I don't see a noticable increase in database usage. I'm writing to 
two tables when each RSS url is pulled, and my mysql master barely 
notices. Mostly because I have nearly no table contention. I've done my 
best to design it so that no other apps are reading the tables that are 
recording download stats except to roll them over.

Ultimately your performance is governed by the speed of your disks, how 
many indicies you have on your tables and how long you buffer your 
writes before syncing to disk. My replication cluster was operating 
close to 1000 qps with round robin reads across two replication slaves. 
My ratios on the master are like 41/10/17/0 and the replicants are like 
68/14/10/0.

By converting about five of my most popular php scripts, I've easily cut 
it down 30%.I find that replication lag is reduced because I'm cutting 
down the number of queries on the replicants. If I keep spreading 
memcache to the rest of my php pages (as appropriate) I easily expect to 
cut down 60% of my selects. I think that 300qps with 30/30/30/0 ratios 
or better is easily possible for my mysql replication cluster. So my 
limit is roughly converging to "how many writes I can push" into a table.

Jed