Benchmark 2 - MySQL Cache

Published on 2010-12-15.

This is a benchmark test of the performance of MySQL Cache. The test was performed on an Intel P4 1.7 Ghz with 1036028 kB of memory over a private LAN. The server runs MySQL 5.0.51a and PHP 5.3.3 on a standard Debian installation. Between each change of settings all related processes was restarted. Since Debian Squeeze the MySQL Cache is enabled by default.

I am testing using Apache HTTP server benchmarking tool using this command: $ ab -c5 -n3000

The test will create 5 concurrent connections that will each perform 3000 requests.

The first tests I did was on some PHP code using prepared statements, but I noticed no performance boost what so ever. I did some research and found out that the query cache is not used for prepared statements.

I created a new test using PHP where I fetch (using a regular query) a single column from a row in a MySQL table that contains 1.080 entries. The column is a varchar(200) field containing data of http_user_agent from PHP. The SELECT query is: SELECT http_user_agent FROM my_db

Test 1 With MySQL Cache Disabled.

Finished 3000 requests
Server Software:        Apache/2.2.9
Server Hostname:        webserver
Server Port:            83
Document Path:          /test.php
Document Length:        294 bytes
Concurrency Level:      5
Time taken for tests:   81.258 seconds
Complete requests:      3000
Failed requests:        0
Write errors:           0
Total transferred:      1644000 bytes
HTML transferred:       882000 bytes
Requests per second:    36.92 [#/sec] (mean)
Time per request:       135.429 [ms] (mean)
Time per request:       27.086 [ms] (mean, across all concurrent requests)
Transfer rate:          19.76 [Kbytes/sec] received
Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.0      0       1
Processing:    25  135  67.0    131     794
Waiting:       25  134  66.3    130     794
Total:         25  135  67.0    131     794
Percentage of the requests served within a certain time (ms)
  50%    131
  66%    154
  75%    172
  80%    181
  90%    217
  95%    255
  98%    294
  99%    333
 100%    794 (longest request)`

Test 2 With MySQL Cache Disabled.

Finished 3000 requests
Server Software:        Apache/2.2.9
Server Hostname:        webserver
Server Port:            83
Document Path:          /test.php
Document Length:        294 bytes
Concurrency Level:      5
Time taken for tests:   81.494 seconds
Complete requests:      3000
Failed requests:        0
Write errors:           0
Total transferred:      1644000 bytes
HTML transferred:       882000 bytes
Requests per second:    36.81 [#/sec] (mean)
Time per request:       135.823 [ms] (mean)
Time per request:       27.165 [ms] (mean, across all concurrent requests)
Transfer rate:          19.70 [Kbytes/sec] received
Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.0      0       1
Processing:    25  136  86.1    127     458
Waiting:       25  134  84.8    126     458
Total:         25  136  86.1    127     458
Percentage of the requests served within a certain time (ms)
  50%    127
  66%    154
  75%    175
  80%    191
  90%    246
  95%    314
  98%    374
  99%    414
 100%    458 (longest request)`

This time I perform the same test but with MySQL Cache enabled.

You can enable or disable the cache by commenting out the query_cache_ variables in my.cfn. My my.cnf looks like this:

query_cache_limit       = 32M
query_cache_size        = 256M

Test 1 With MySQL Cache Enabled.

Finished 3000 requests
Server Software:        Apache/2.2.9
Server Hostname:        webserver
Server Port:            83
Document Path:          /test.php
Document Length:        294 bytes
Concurrency Level:      5
Time taken for tests:   54.225 seconds
Complete requests:      3000
Failed requests:        0
Write errors:           0
Total transferred:      1644000 bytes
HTML transferred:       882000 bytes
Requests per second:    55.32 [#/sec] (mean)
Time per request:       90.375 [ms] (mean)
Time per request:       18.075 [ms] (mean, across all concurrent requests)
Transfer rate:          29.61 [Kbytes/sec] received
Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.0      0       0
Processing:    16   90  41.0     88     356
Waiting:       16   89  40.2     87     356
Total:         16   90  41.0     88     356
Percentage of the requests served within a certain time (ms)
  50%     88
  66%    102
  75%    110
  80%    118
  90%    140
  95%    166
  98%    198
  99%    217
 100%    356 (longest request)`

Test 2 With MySQL Cache Enabled.

Finished 3000 requests
Server Software:        Apache/2.2.9
Server Hostname:        webserver
Server Port:            83
Document Path:          /test.php
Document Length:        294 bytes
Concurrency Level:      5
Time taken for tests:   54.546 seconds
Complete requests:      3000
Failed requests:        0
Write errors:           0
Total transferred:      1644000 bytes
HTML transferred:       882000 bytes
Requests per second:    55.00 [#/sec] (mean)
Time per request:       90.910 [ms] (mean)
Time per request:       18.182 [ms] (mean, across all concurrent requests)
Transfer rate:          29.43 [Kbytes/sec] received
Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.0      0       1
Processing:    16   91  41.5     89     955
Waiting:       16   89  40.9     88     955
Total:         16   91  41.5     90     955
Percentage of the requests served within a certain time (ms)
  50%     90
  66%    101
  75%    110
  80%    117
  90%    139
  95%    158
  98%    183
  99%    205
 100%    955 (longest request)`

Conclusion

As expected the test shows a significant performance boost when the MySQL Cache is enabled.

The MySQL Cache expires automatically when the table is modified (inserts, updates, delete's, etc) which means that there is no risk of running non up-to-date data.

The MySQL Cache is a very nice piece of technology, but as with all performance issues it should be used in connection with other tools as well.