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.