Well, it seems that tweaking the query cache was pretty fruitless. I tweaked query_cache_min_res_unit from 4096 (default) all the way down to 512 (minimum). The results… no change. :(
I flush the query cache and then used ab (apache bench) to run 2000 queries with 5 concurrent connections. The results for 4096 minimum query result size.
Apache Bench Results:
Concurrency Level: 5
Time taken for tests: 118.103257 seconds
Complete requests: 2000
Failed requests: 0
Write errors: 0
Total transferred: 31331284 bytes
HTML transferred: 30764718 bytes
Requests per second: 16.93 [#/sec] (mean)
Time per request: 295.258 [ms] (mean)
Time per request: 59.052 [ms] (mean, across all concurrent requests)
Transfer rate: 259.06 [Kbytes/sec] receivedAnd in MySQL:
mysql> SHOW STATUS LIKE 'Qcache%'; SHOW VARIABLES LIKE '%query_cache%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 10256016 | | Qcache_hits | 32037 | | Qcache_inserts | 148 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 2031 | | Qcache_queries_in_cache | 148 | | Qcache_total_blocks | 306 | +-------------------------+----------+ 8 rows in set (0.00 sec) +------------------------------+----------+ | Variable_name | Value | +------------------------------+----------+ | have_query_cache | YES | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 10485760 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | +------------------------------+----------+ 6 rows in set (0.00 sec)
Those numbers are our starting point. Testing with 2048, 1024 and 512 all essentially returned result similar to this.
Apache Bench:
Concurrency Level: 5
Time taken for tests: 118.449434 seconds
Complete requests: 2000
Failed requests: 0
Write errors: 0
Total transferred: 31319642 bytes
HTML transferred: 30753359 bytes
Requests per second: 16.88 [#/sec] (mean)
Time per request: 296.124 [ms] (mean)
Time per request: 59.225 [ms] (mean, across all concurrent requests)
Transfer rate: 258.21 [Kbytes/sec] receivedMySQL:
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 10101120 |
| Qcache_hits | 32129 |
| Qcache_inserts | 222 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2023 |
| Qcache_queries_in_cache | 213 |
| Qcache_total_blocks | 458 |
+-------------------------+----------+
8 rows in set (0.00 sec)
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 512 |
| query_cache_size | 10485760 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+----------+
6 rows in set (0.00 sec)So I guess something else is really slowing down the req/sec. Most likely it is within the PHP processing. The time required to run WordPress, generate the HTML and return it to the browser is likely way more than what is spent on the MySQL queries. That only means great job WordPress!
Looks like the next step is to implement Squid as a reverse proxy. Time to go for the higher hanging fruit.
