So sánh Cache Performance giữa MySQL Query Cache và File Cache

Jay Pipes continues cache experiements and has compared performance of MySQL Query Cache and File Cache.

Jay uses Apache Benchmark to compare full full stack, cached or not which is realistic but could draw missleading picture as contribution of different components may be different depending on your unique applications. For example for application containing a lot of clode but having only couple of queries to MySQL parsing may be performance bottleneck, assuming PHP opcode cache is not used. Also different applications may have different cache hit ratios which also needs to be factored in estimating improvement for real application.

So instead of following his route, especially as Jay is going to publish his comparison of all caches anyway, I decided to check peak performance of all caches compared to MySQL Server, by measuring just the time it takes cache to return the data. In the real life applications performance is likely to be lower due to less CPU cache usage efficiency larger object size and other reason.

So what my test does ? Simply we perform 10.000 of get requests from cache, which was previously populated to contain value and measuring how long does it take.

Test was done on my home test box (2Ghz AMD Sempron CPU) using MySQL 4.1 and PHP 5.0. For memcached access memcache extension from pecl was used. All applications were running on same system.

I used two baselines for comparison. First is speed of PHP Associative array. This is to show kind of peak speed possible at all. Furthermore this type of caching is rather helpful for some of applications, which tend to access same data read from database multiple times. Examining MySQL full query logs from many applications seeing several exactly same queries executed during page load is not an exception. For caching these associative array can be considered.

Second baseline was selecting from MySQL table. Query vas very simple – lookup by primary cache, so this is kind of peak performance MySQL can provide. Of course for your real queries cost of database access will normally be larger.

Results I got from my envinronment are:

Note: The test measures peak performance so I did not do much of error control or other precausions like string escaping which you will need in real application. Also due to the same reason you should make sure all caches are working as expected while testing it, for example you may set apc.enable_cli=1 if you’re running script from command line otherwise APC cache will not work and results will be wrong.

So what is about results and how we can use them for MySQL Performance tuning ? Not surpising associative array cache performs the best, almost 4 times faster than APC shared memory cache – closest competitor. In real life performance difference can be evel larger as there will be some syncronization contention while accessing shared memory cache which does not happen in this case.

File cache really does great, even though it is over 3 times slower than APC. The catch with file cache is – there are actually two very different cases – when cached data set fits in memory well and so served from OS cache, and when it does not. In case it does – APC Cache perhaps will give you better performance. If it does not fit in cache well – you will get disk IO which is very compared to performance of all in memory caches and so you might be better off storing your data on network with memcached.

Memcache performs worse than file cache (even though it is run on localhost in this case) – of course copying data from OS cache is going to be faster than retriving it via TCP/IP socket, It is however very interesting when it comes to compare it to MySQL Query Cache – It performs faster than Query Cache if TCP/IP socket is used, but if Unix Socket is used to connect to MySQL MySQL Query Cache will be faster. The explanation for this is also pretty simple – in both cases logic of the process is rather simple – to get result from the cache and ship it back, so large overhead of TCP/IP protocol compared to Unix Socket plays critical role here. Thinking which cache to use I would not however forget about other benefits of Memcache – distributed caching, support of time to live (so you do not get cache invalidated with each update) and ability to cache composed objects which may correspond to multiple MySQL queries.

On Selecting from the table I should note MySQL is rather fast selecting from the table as well – on this pretty low end box we’re getting over 7000 queries/sec and it is almost doubled if result sets are cached from query cache. Pretty impressive.

So what my recommendations would be about using these caches for your application ?

Cache per script data in associative array. If you have some data read from database which you need several time during page creation cache it locally do not depend on any other types of caches.

use APC Cache for single node applications If you have just one web server or your data set is small so it fits in memory in each of the servers APC Cache may be the most efficient way for you to cache the data.

Use Memcached for large scale applications If local node cache is not large enough to cache good amount of data caching on network by using memcached is very good way to go.

File Cache is good for long term storage If you need something stored long term or something which needs to be cached but does not fit even in distributed memory you can use file cache (ie on shared storage).

Query Cache is good when there is no other cache If you do not do any other caching for certain object or if you cache on different level (ie single object constructed from multiple query results) MySQL Query Cache may improve performance of your application.

Multiple layers of caching may do well Same as CPUs have multiple layer of caching and then same data may be stored in OS file cache, than SAN Cache you may implement multiple levels of caching for your application. In different circumstances different layering may make sense. For example you might wish to use APC Cache as L1 cache and File cache as L2 cache if you have large amount of data in long term cache. If you need something like this you might take a look at Eaccelerator which is APC alternative which supports caching user data both on disk and in shared memory.

Appendinx:

If you would like to repeat my benchmark or experiment with more caches here are source files and other requirements:

1) for file cache to work you need file named “test” containing “MyTestString”
2) You need to create table test.test for MySQL Cache to work

CREATE TABLE `test` (
  `k` varchar(60) NOT NULL default '',
  `val` varchar(255) default NULL,
  PRIMARY KEY  (`k`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `test` VALUES ('test','MyTestString');

3) You need all caches to work, ie query cache enabled, memcached running, apc enabled for cli mode etc.

Main PHP File:

<?php
require_once "global.php";
/* Array Implementation */
$arr=array();
$arr[$key]=$data;
function cache_array()
{
    global $arr;
    global $key;
    return $arr[$key];
}
echo ("Array Cache  ");
benchmark("cache_array");
/* APC Implementation */
$rc=apc_store($key,$data,3600);
function cache_apc()
{
    global $key;
    return apc_fetch($key);
}
echo ("APC Cache  ");
benchmark("cache_apc");
/* File Cache benchmark */
/* IT assumes file is already created which contains data we need */
function cache_file()
{
    global $key;
    return file_get_contents($key);
}
echo ("File Cache  ");
benchmark("cache_file");
/* MemCacheD Implementation */
$memcache=new Memcache;
$memcache->pconnect('localhost',11211);
$memcache->set($key,$data,0,3600);
function cache_memcached()
{
    global $key;
    global $memcache;
    return $memcache->get($key);
}
echo ("Memcached Cache  ");
benchmark("cache_memcached");
/* MySQL QC Implementation */
function cache_mysql_qc()
{
    global $key;
    global $mysqli;
    $r=$mysqli->query("select val from test.test where k='$key'");
    $row=$r->fetch_row();
    if ($row)
      $ret=$row[0];
    $r->close();
    return $ret;
}
$mysqli=new mysqli('127.0.0.1','root');
echo ("MySQL Query Cache (TCP/IP) ");
benchmark("cache_mysql_qc");
$mysqli->close();
$mysqli=new mysqli('localhost','root');
echo ("MySQL Query Cache (Unix Socket) ");
benchmark("cache_mysql_qc");
$mysqli->close();
/* MySQL Direct Table Implementation */
function cache_mysql_table()
{
    global $key;
    global $mysqli;
    $r=$mysqli->query("select sql_no_cache val from test.test where k='$key'");
    $row=$r->fetch_row();
    if ($row)
      $ret=$row[0];
    $r->close();
    return $ret;
}
$mysqli=new mysqli('127.0.0.1','root');
echo ("MySQL Table (TCP/IP) ");
benchmark("cache_mysql_table");
$mysqli->close();
$mysqli=new mysqli('localhost','root');
echo ("MySQL Table (Unix Socket) ");
benchmark("cache_mysql_table");
$mysqli->close();
?>

global.php file with benchmark function

<?php
        $key="test";
        $data="MyTestString";
        $rounds=100000;
        function microtime_float()
        {
          list($usec, $sec) = explode(" ", microtime());
          return ( ((double)$usec + (double)$sec)*1000000.0 );
        }
        function benchmark($func)
        {
            global $rounds;
            $t1=microtime_float();
            for($i=0;$i<$rounds;$i++)
                $func();
            $t2=microtime_float();
            $t=$t2-$t1;
            $persec=($rounds/$t)*1000000;
            echo("Time: $t Gets/Sec: $persec\n");
        }
?>