小草博客地址: http://homeway.me/ =>回家的路
The artile is about php cache and mysql speed.
(Today's all code is here http://xiaocao.u.qiniudn.com/work%2F2014-09-06-mysql-vs-cache.zip)
Tool:
Apache Benchmark And Siege (about 'ab' and 'siege'
model:
insert、select、update x100loop x100get
insert x200loop x200get
select x200loop x200get
update x200loop x200get
Ox01.System Environment
- system
MAC os
Software OS X 10.9.4 (13E28)
Processor 2.4 GHz Intel Core i5
Memory 8 GB 1600 MHz DDR3
250GB SSD
- environment
xampp 1.8.3
Apache/2.4.9
PHP Version 5.5.11
mysqlnd 5.0.11-dev - 20120503 - (mysql is empty)
Ox02.Code:
cache.php
<?php
require 'src/cache.class.php';
$cache = new SimpleCache();
$cache->cache_path = 'cache/';
$cache->cache_time = 3600;
for ($i=1; $i < 100 ; $i++) {
$key = md5( uniqid().time() ) ;
$date = time();
//insert
$save = $cache->set_cache( $key, $data);
//get
$get = $cache->get_cache( $key );
//update
$update = $cache->set_cache( $key, $data);
}
exit();
?>
cache.class.php is sample cache class, every key create a 'key.data' file, nothing special。
mysql.php
<?php
$con = mysql_connect("localhost",$user,$pwd);
if (!$con) {
die('Could not connect: ' . mysql_error());
}
mysql_select_db('phpcache',$con);
for ($i=1; $i < 100 ; $i++) {
$time = time();
$insert = "INSERT INTO `cache`( `time` ) VALUES ( '$time' )";
$select = "SELECT * FROM `cache` WHERE `time` = '$time'";
$times=$time+1;
$update = "UPDATE `cache` SET `cache`='$times' WHERE `time`='$time'";
$insert_result = mysql_query($insert);
$select_result = mysql_query($select);
$update_result = mysql_query($update);
}
?>
all ready. fire!!
Ox03. TEST1 => insert、select、update x100 Loop x100 GET
first test
ab -n 100 -c 10 http://127.0.0.1/phpcache/cache.php
result:
This is ApacheBench, Version 2.3 <$Revision: 655654 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/
Benchmarking 127.0.0.1 (be patient).....done
Server Software: Apache/2.4.9
Server Hostname: 127.0.0.1
Server Port: 80
Document Path: /phpcache/cache.php
Document Length: 27522 bytes
Concurrency Level: 10
Time taken for tests: 3.543 seconds
Complete requests: 100
Failed requests: 0
Write errors: 0
Total transferred: 2773500 bytes
HTML transferred: 2752200 bytes
Requests per second: 28.22 [#/sec] (mean)
Time per request: 354.349 [ms] (mean)
Time per request: 35.435 [ms] (mean, across all concurrent requests)
Transfer rate: 764.36 [Kbytes/sec] received
Connection Times (ms)
min mean[+/-sd] median max
Connect: 0 0 1.1 0 11
Processing: 135 347 138.3 339 730
Waiting: 36 101 44.8 80 204
Total: 136 347 138.5 339 730
Percentage of the requests served within a certain time (ms)
50% 339
66% 420
75% 434
80% 473
90% 530
95% 583
98% 624
99% 730
100% 730 (longest request)
all 10198 records
ab -n 100 -c 10 http://127.0.0.1/phpcache/mysql.php
####result:
This is ApacheBench, Version 2.3 <$Revision: 655654 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/
Benchmarking 127.0.0.1 (be patient).....done
Server Software: Apache/2.4.9
Server Hostname: 127.0.0.1
Server Port: 80
Document Path: /phpcache/mysql.php
Document Length: 0 bytes
Concurrency Level: 10
Time taken for tests: 37.934 seconds
Complete requests: 100
Failed requests: 0
Write errors: 0
Total transferred: 23200 bytes
HTML transferred: 0 bytes
Requests per second: 2.64 [#/sec] (mean)
Time per request: 3793.356 [ms] (mean)
Time per request: 379.336 [ms] (mean, across all concurrent requests)
Transfer rate: 0.60 [Kbytes/sec] received
Connection Times (ms)
min mean[+/-sd] median max
Connect: 0 0 0.1 0 0
Processing: 1229 3790 1623.9 4030 6359
Waiting: 1229 3790 1623.9 4030 6359
Total: 1230 3790 1623.9 4030 6359
Percentage of the requests served within a certain time (ms)
50% 4030
66% 4650
75% 5187
80% 5793
90% 6293
95% 6348
98% 6356
99% 6359
100% 6359 (longest request)
all 10198 records
second test(Without clean created file)
cache.php
result:
Concurrency Level: 10
Time taken for tests: 7.518 seconds
Complete requests: 100
Failed requests: 0
Write errors: 0
Total transferred: 23200 bytes
HTML transferred: 0 bytes
Requests per second: 13.30 [#/sec] (mean)
Time per request: 751.756 [ms] (mean)
Time per request: 75.176 [ms] (mean, across all concurrent requests)
Transfer rate: 3.01 [Kbytes/sec] received
Percentage of the requests served within a certain time (ms)
50% 591
66% 636
75% 682
80% 695
90% 752
95% 795
98% 859
99% 882
100% 882 (longest request)
all 19800 records
mysql.php
result:
Concurrency Level: 10
Time taken for tests: 89.448 seconds
Complete requests: 100
Failed requests: 0
Write errors: 0
Total transferred: 23200 bytes
HTML transferred: 0 bytes
Requests per second: 1.12 [#/sec] (mean)
Time per request: 8944.776 [ms] (mean)
Time per request: 894.478 [ms] (mean, across all concurrent requests)
Transfer rate: 0.25 [Kbytes/sec] received
Connection Times (ms)
min mean[+/-sd] median max
Connect: 0 0 0.1 0 0
Processing: 6474 8939 1506.3 9314 11180
Waiting: 6474 8938 1506.3 9314 11179
Total: 6474 8939 1506.2 9314 11180
Percentage of the requests served within a certain time (ms)
50% 9314
66% 9722
75% 10388
80% 10701
90% 11058
95% 11148
98% 11167
99% 11180
100% 11180 (longest request)
all 20010 records
third test(Without clean created file)
cache.php
result:
Concurrency Level: 10
Time taken for tests: 8.563 seconds
Complete requests: 100
Failed requests: 0
Write errors: 0
Total transferred: 23200 bytes
HTML transferred: 0 bytes
Requests per second: 11.68 [#/sec] (mean)
Time per request: 856.292 [ms] (mean)
Time per request: 85.629 [ms] (mean, across all concurrent requests)
Transfer rate: 2.65 [Kbytes/sec] received
Percentage of the requests served within a certain time (ms)
50% 838
66% 875
75% 899
80% 907
90% 968
95% 1007
98% 1034
99% 1034
100% 1034 (longest request)
all 29362 records(->_-> when I get folder info, it`s a litte slow)
mysql.php
result:
Concurrency Level: 10
Time taken for tests: 146.047 seconds
Complete requests: 100
Failed requests: 0
Write errors: 0
Total transferred: 23200 bytes
HTML transferred: 0 bytes
Requests per second: 0.68 [#/sec] (mean)
Time per request: 14604.688 [ms] (mean)
Time per request: 1460.469 [ms] (mean, across all concurrent requests)
Transfer rate: 0.16 [Kbytes/sec] received
Percentage of the requests served within a certain time (ms)
50% 14514
66% 14853
75% 15347
80% 16788
90% 18805
95% 18832
98% 18841
99% 18843
100% 18843 (longest request)
all 30141 records
Ox04. TEST2 => insert x100 Loop x200 GET
cache.php (ab -n 200 -c 10 http://127.0.0.1/phpcache/cache.php)
result:
Concurrency Level: 10
Time taken for tests: 6.775 seconds
Complete requests: 200
Failed requests: 0
Write errors: 0
Total transferred: 46400 bytes
HTML transferred: 0 bytes
Requests per second: 29.52 [#/sec] (mean)
Time per request: 338.728 [ms] (mean)
Time per request: 33.873 [ms] (mean, across all concurrent requests)
Transfer rate: 6.69 [Kbytes/sec] received
Connection Times (ms)
min mean[+/-sd] median max
Connect: 0 0 0.0 0 0
Processing: 87 335 96.7 357 530
Waiting: 87 335 96.4 355 530
Total: 87 335 96.7 357 531
Percentage of the requests served within a certain time (ms)
50% 357
66% 385
75% 401
80% 409
90% 436
95% 456
98% 478
99% 523
100% 531 (longest request)
mysql.php (ab -n 200 -c 10 http://127.0.0.1/phpcache/mysql.php)
Concurrency Level: 10
Time taken for tests: 1.179 seconds
Complete requests: 200
Failed requests: 0
Write errors: 0
Total transferred: 46400 bytes
HTML transferred: 0 bytes
Requests per second: 169.57 [#/sec] (mean)
Time per request: 58.972 [ms] (mean)
Time per request: 5.897 [ms] (mean, across all concurrent requests)
Transfer rate: 38.42 [Kbytes/sec] received
Connection Times (ms)
min mean[+/-sd] median max
Connect: 0 0 0.1 0 1
Processing: 27 58 36.7 42 226
Waiting: 27 58 36.7 42 226
Total: 28 58 36.7 42 226
Percentage of the requests served within a certain time (ms)
50% 42
66% 55
75% 63
80% 72
90% 116
95% 141
98% 173
99% 208
100% 226 (longest request)
=> From the result, we can see that, insert data into msyql or writer into disk is quickly. And mysql is more quick than disk. Than we can find a way to save time, save cache in a file not create a file for each data.
Ox04. TEST3 => select x100 Loop x200 GET(select a does not existed data)
cache.php
result:
Concurrency Level: 10
Time taken for tests: 0.201 seconds
Complete requests: 200
Failed requests: 0
Write errors: 0
Total transferred: 46400 bytes
HTML transferred: 0 bytes
Requests per second: 993.31 [#/sec] (mean)
Time per request: 10.067 [ms] (mean)
Time per request: 1.007 [ms] (mean, across all concurrent requests)
Transfer rate: 225.05 [Kbytes/sec] received
Connection Times (ms)
min mean[+/-sd] median max
Connect: 0 0 0.1 0 1
Processing: 4 10 1.8 10 14
Waiting: 4 10 1.8 10 14
Total: 4 10 1.8 10 14
Percentage of the requests served within a certain time (ms)
50% 10
66% 11
75% 11
80% 11
90% 12
95% 13
98% 14
99% 14
100% 14 (longest request)
so quickly!!
mysql.php
result:
Concurrency Level: 10
Time taken for tests: 70.006 seconds
Complete requests: 200
Failed requests: 0
Write errors: 0
Total transferred: 46400 bytes
HTML transferred: 0 bytes
Requests per second: 2.86 [#/sec] (mean)
Time per request: 3500.306 [ms] (mean)
Time per request: 350.031 [ms] (mean, across all concurrent requests)
Transfer rate: 0.65 [Kbytes/sec] received
Connection Times (ms)
min mean[+/-sd] median max
Connect: 0 0 0.1 0 0
Processing: 3000 3492 230.4 3438 4047
Waiting: 3000 3491 230.4 3438 4047
Total: 3000 3492 230.4 3438 4047
Percentage of the requests served within a certain time (ms)
50% 3438
66% 3628
75% 3679
80% 3701
90% 3804
95% 3898
98% 4005
99% 4033
100% 4047 (longest request)
so slow!!
The results are very clear, using cache to search data is qulcikly than mysql select a lot. So use cache to save some data reselected many times will be more quickly so much.
Ox05. TEST4 => update x100 Loop x200 GET
cache.php
result:
Concurrency Level: 10
Time taken for tests: 0.973 seconds
Complete requests: 200
Failed requests: 0
Write errors: 0
Total transferred: 46400 bytes
HTML transferred: 0 bytes
Requests per second: 205.51 [#/sec] (mean)
Time per request: 48.660 [ms] (mean)
Time per request: 4.866 [ms] (mean, across all concurrent requests)
Transfer rate: 46.56 [Kbytes/sec] received
Connection Times (ms)
min mean[+/-sd] median max
Connect: 0 0 0.1 0 1
Processing: 9 47 54.9 23 267
Waiting: 9 47 54.9 23 267
Total: 9 47 54.9 23 267
Percentage of the requests served within a certain time (ms)
50% 23
66% 42
75% 53
80% 59
90% 142
95% 175
98% 255
99% 265
100% 267 (longest request)
mysql.php
result:
Concurrency Level: 10
Time taken for tests: 1.411 seconds
Complete requests: 200
Failed requests: 0
Write errors: 0
Total transferred: 46400 bytes
HTML transferred: 0 bytes
Requests per second: 141.78 [#/sec] (mean)
Time per request: 70.532 [ms] (mean)
Time per request: 7.053 [ms] (mean, across all concurrent requests)
Transfer rate: 32.12 [Kbytes/sec] received
Connection Times (ms)
min mean[+/-sd] median max
Connect: 0 0 0.1 0 0
Processing: 39 70 7.9 68 97
Waiting: 39 70 7.9 68 97
Total: 39 70 8.0 68 97
Percentage of the requests served within a certain time (ms)
50% 68
66% 70
75% 71
80% 73
90% 82
95% 90
98% 93
99% 94
100% 97 (longest request)
From it, so clear. Because when mysql update a data, it must seach id in all data, so it include select.
Ox06.Find a solution
As we know, speed is always the most important element in developing a website especially for those high traffic database driven website. You can try to turn on query cache to speed up query.
To speed up query, enable the MySQL query cache, before that you need to set few variables in mysql configuration file (usually is my.cnf or my.ini)
first, set query_cache_type to 1. (There are 3 possible settings: 0 (disable / off), 1 (enable / on) and 2 (on demand).
query-cache-type = 1
second, set query_cache_size to your expected size. I’d prefer to set it at 20MB.
query-cache-size = 20M
So use cache in right time is a batter way to speed your website.
some useful link to make your mysql qulickly.
http://coolshell.cn/articles/1846.html
http://soft.chinabyte.com/database/55/12710055.shtml
http://planetcassandra.org/nosql-performance-benchmarks/
-By xiaocao
2014-09-05 03:41:13
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。