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:
Cache Type | Cache Gets/sec |
Array Cache | 365000 |
APC Cache | 98000 |
File Cache | 27000 |
Memcached Cache (TCP/IP) | 12200 |
MySQL Query Cache (TCP/IP) | 9900 |
MySQL Query Cache (Unix Socket) | 13500 |
Selecting from table (TCP/IP) | 5100 |
Selecting from table (Unix Socket) | 7400 |
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[$key]=$data;
-
function cache_array()
-
{
-
global $arr;
-
global $key;
-
return $arr[$key];
-
}
-
benchmark("cache_array");
-
/* APC Implementation */
-
$rc=apc_store($key,$data,3600);
-
function cache_apc()
-
{
-
global $key;
-
return apc_fetch($key);
-
}
-
benchmark("cache_apc");
-
/* File Cache benchmark */
-
/* IT assumes file is already created which contains data we need */
-
function cache_file()
-
{
-
global $key;
-
}
-
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);
-
}
-
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');
-
benchmark("cache_mysql_qc");
-
$mysqli->close();
-
$mysqli=new mysqli('localhost','root');
-
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');
-
benchmark("cache_mysql_table");
-
$mysqli->close();
-
$mysqli=new mysqli('localhost','root');
-
benchmark("cache_mysql_table");
-
$mysqli->close();
-
?>
global.php file with benchmark function:
-
<?php
-
$key="test";
-
$data="MyTestString";
-
$rounds=100000;
-
function microtime_float()
-
{
-
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;
-
}
-
?>
Enjoy
相关推荐
A performance comparison of main stream network simulators, including NS2, NS3, OPNet, OMNet++.
This article provides a high-level performance comparison between Windows Communication Foundation (WCF) and existing Microsoft .NET distributed communication technologies.
Algorithm-sorting-algorithms-performance-comparison.zip,一组排序算法的性能比较它计算每个算法用于排序列表的时间:已排序的数字和未排序的数字,算法是为计算机程序高效、彻底地完成任务而创建的一组详细的准则...
genetic algorithm performance compression
We compare the performance of two prominent on-demand routing protocols for mobile ad hoc networks: Dynamic Source Routing (DSR) and Ad Hoc On-Demand Distance Vector Routing (AODV). A detailed ...
缺点: 未实现Map接口性能问题:所有方法(读/写)都已同步过于简化的API:仅获取/放入难以测试对Java 8不友好##番石榴缓存来自Google的应用缓存( ) 优点: 战斗测试功能齐全(自加载缓存,刷新策略,统计信息......
近期, 知名独立基准测评机构bankmark,针对SequoiaDB、MongoDB以及Cassandra三款NoSQL数据库产品做了性能对比测试。在所有的测试中,三款产品的表现各有千秋。 报道详情:http://code.csdn.net/news/2823026
java hotspot源码比较JVM实现的性能 这是我的文章的源代码。 怎么跑 Java 8基准run-benchmark-java-8.sh Java 11基准run-benchmark-java-11.sh
Performance_comparison_with_BF_plus_201008
重点分析了单用户MIMO和多用户MIMO的性能,并用图形的方式从多方面分析了他们的吞吐量问题
Applications of VANETs (Vehicular Ad hoc Networks) have their own requirements and challenges in wireless communication technology. Although regarded as the first standard for VANETs, IEEE 802....
PHP服务器性能测量。 字符串,数组,输出(缓冲)和基于数学运算的测量。 仅评估内部性能,不测量磁盘或网络性能,仅返回服务器负载相关的样本,请平均服务器运行时进行比较。
英文名《An Updated Performance Comparison of Virtual Machines and Linux Containers》,IBM公司进行的关于使用虚拟机和docker的情况下,相关性能指标和在linux测试数据的对比
S.J(2009)___Non-Data-Aided Symbol Timing-Error Detection比较the Raised-Cosine and Better Than Raised-Cosine两种的性能
BOC信号的捕获 包括各种无模糊捕获方法以及其性能比较 BOC Signal acquisition includes various fuzziness acquisition methods and their performance comparison
In this paper, the performance of voltage-source converter-based shunt and series compensators used for load voltage control in electrical power distribution systems has been analyzed and compared, ...
Hex Comparison 是一款二进制文件比较与十六进制编辑工具。它可以帮助你使用二进制格式来比较两个文件。主要功能: 1.允许快速比较两个文件。 2.快速查找每一处不匹配的数据。 3.使用自定义颜色来显示不匹配数据。 4...
ndvi land use /Comparison of Landscape Pattern–Normalized Difference Vegetation
The main technical contributions of this paper are (1) a review of VMware Workstation’s software VMM, focusing on ... (3) a quantitative performance comparison of a software and a hardware VMM.