代码性能-php(二)


The artile is about php cache and mysql speed.
Test the speed of mysql and cache in the same conditions.
First I test insert,select,update 3 times and then, test each a time.


(Today’s all code is here http://77l5jp.com1.z0.glb.clouddn.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

0x01.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)

0x02.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!!

0x03. 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


Obviously, mysql is slower then cache so much.

See here:

mysql & cache speed


0x04. 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.





0x04. 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.





0x05. 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.





0x06.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/





Please indicate the source:http://homeway.me/2014/07/05/code-performance-php-2


-By xiaocao

2014-07-05 03:41:13

Fork me on GitHub