Memcached + MySQL

[Alexander Zaitsev]

Hi Brian,

This is all true in general. But look at original problem posted by 
Angelo. As far as I understood the original problem, MySQL is the 
backend for DNS. In DNS, most of requests are very similar short queries 
that can be effectively cached. That's why Angelo thought about memcached.

This is very cheap and easy to try MySQL tuning. Results may be or may 
be not impressive, but there is no risk.

Peter Zaitsev in his mysqlperformanceblog published a test year ago that 
compared multiple cache implementations, including memcached and MySQL 
query cache.
http://www.mysqlperformanceblog.com/2006/08/09/cache-performance-comparison/

This is pretty interesting.

P.S. There was a typo in my post, normal value for key_buffer_size is 
512M, not K.

Brian Aker wrote:
> Hi!
>
> On Oct 31, 2007, at 3:57 AM, Alexander Zaitsev wrote:
>
>> MySQL itself has a bunch of optimization options that effectively 
>> turn disk reads into memory reads . In your case I would guess that 
>> following tuning would help:
>> 1. Increase MySQL query cache size
>> 2. increase key_buffer_size (normal value is 512K).
>
> This really isn't the right mailing list for this, but the above is 
> not quite right. The query cache is purged pretty constantly with 
> Innodb (and its very accurate except in the most recent versions).  
> For MyISAM it is better but I've rarely seen the query cache make any 
> difference outside of benchmarks.
>
> MyISAM relies on the OS'es ability to cache files. With Innodb it is a 
> bit better, but on current versions of MySQL the buffer pool, which 
> you need to really scale, is not really well threaded. This has been 
> fixed and there are additional fixes in the 5.1 tree around the 
> autoincrement issues that come up with multi-concurrency.
>
>> MyISAM is also faster than InnoDB, if your application logic permits 
>> non-transactional DB. MEMORY engine is probably not a good idea since 
>> you can loose all your data.
>
> Not really. MyISAM is at the mercy of the file system cache. It can 
> sometimes be faster, but a well tuned Innodb is normally faster for 
> reads (though not writes...). Write concurrency performance is very 
> dependent on tuning and the application design. If all you need is a 
> fast writer use Archive :)
>
> If the user has no need for durability, then memory for many 
> operations is quite fast. You can do backstore with triggers to 
> persistent engines if you need it.
>
> Cheers,
>     -Brian
>
> -- 
> _______________________________________________________
> Brian "Krow" Aker, brian at tangent.org
> Seattle, Washington
> http://krow.net/                     <-- Me
> http://tangent.org/                <-- Software
> http://exploitseattle.com/    <-- Fun
> _______________________________________________________
> You can't grep a dead tree.
>
>
>
>

-- 
Alexander Zaitsev
Engineering Manager
AMG Lab Sàrl