Memcached + MySQL

Brian Aker brian at tangent.org
Thu Nov 1 17:36:36 UTC 2007


Hi!

On Nov 1, 2007, at 3:41 AM, Alexander Zaitsev wrote:

> 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/

Look what Peter did in his test:
$mysqli=new mysqli('127.0.0.1','root');

If MySQL gets that, it will automatically switch away from using TCP/ 
IP (which I dislike). I am a little skeptical of his results. If he  
had wanted to get better performance he would have used prepared  
statements with MySQL (notice, he used dynamic SQL).

As PeterZ points out though he did not perform a memcached to mysql  
using UNIX Socket test. If you compare both systems using TCP/IP you  
will see that Memcached did output perform MySQL.

Keep in mind that a number of A Zone providers are using MySQL now (I  
believe all but one at this point), so MySQL can certainly be used.  
In Europe the largest DNS provider is actually using NDB raw skipping  
MySQL all together.

Cheers,
	-Brian


> 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

--
_______________________________________________________
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.




More information about the memcached mailing list