Optimizing MySQL, part 3, mysql cache
In this part we are targeting MySQL cache.
On most of our shared-hosting servers more than 50% of all queries are served by MySQL cache. That means that they are fast, use less CPU and disk IO – something you should never ignore regardless how fast your hardware is.
Example of MySQL query stats. Dark-blue is for cache-served queries.
MySQL cache configuration
There are 4 main configuration options, that are used to control MySQL query cache.
- query_cache_type – set query cache type. 0 – turn off, 1 – Cache all cacheable query results except for those that begin with
SELECT SQL_NO_CACHE; 2 – Cache results only for cacheable queries that begin withSELECT SQL_CACHE. - query_cache_limit – Do not cache results that are larger than this number of bytes. The default value is 1MB.
- query_cache_size – The amount of memory allocated for caching query results. The default value is 0, which disables the query cache. The permissible values are multiples of 1024; other values are rounded down to the nearest multiple.
- query_cache_min_res_unit – The minimum size (in bytes) for blocks allocated by the query cache. The default value is 4096 (4KB). Tuning information for this variable is given in Section 8.10.3.3, “Query Cache Configuration”. Minimum value is 512.
Test platform
For cache testing I have use same data, as I used in my previous tests. Several big tables, containing 1.7bn records. I have executed very similar queries – first forcing no-cache and then running same queries, but with cache and actually running 10 times more queries to show the speed and power of the MySQL cache.
- First I selected 10 random records from the big table;
- Executed 10 queries using “select like” statement;
- Executed 10 queries using indexed join;
- Executed 10 queries using non-indexed join.
All cached queries were repeated 10 times more. I have mostly played with query_cache_size and query_cache_min_res_unit settings.
Hardware
- 4 core AMD Opteron 6272 (2.1Ghz)
- 8 Gb RAM
- SAS disks
Software
- CloudLinux 6 (tweaked Centos6)
- MariaDB 10.0.21 (tweaked MySQL 5.6)
Default MySQL values
query_cache_type=1 query_cache_limit=1M query_cache_size=256M query_cache_min_res_unit=4096
Test queries
Single table query
select * from myisam_jstats_pages where page like ‘/categories/manufacturer/peter-eggink/new-products/results,71-80’
Query with indexed join
select myisam_jstats_pages.*, myisam_jstats_impressions_key.* from myisam_jstats_pages, myisam_jstats_impressions_key where myisam_jstats_pages.page_id=myisam_jstats_impressions_key.page_id and myisam_jstats_pages.page like ‘/categories/miscellaneous/results,361-360?error=404’
Query with non-indexed join
select myisam_jstats_pages.*, myisam_jstats_impressions.* from myisam_jstats_pages, myisam_jstats_impressions where myisam_jstats_pages.page_id=myisam_jstats_impressions.page_id and myisam_jstats_pages.page like ‘/categories/manufacturer/kreis-magic/comedy-magic/results,1-100?filter_product=’
Test results 1
query_cache_min_res_unit=4096
| start myisam NO CACHE tests: 19:34:07 | start myisam CACHE tests: 19:38:46 |
| start 10 no cache queries: 19:34:07 | start 10 cache queries: 19:38:46 |
| end 10 no cache queries: 19:34:19 | end 10 cache queries: 19:38:59 |
| total 10 no cache queries: 12s | total 10 cache queries: 13s |
| Queries per second: 0.83 | Queries per second: 7.69 |
| start 10 no cache + index join: 19:34:19 | start 10 cache + index join: 19:38:59 |
| end 10 no cache + index join: 19:36:36 | end 10 cache + index join: 19:41:14 |
| total 10 no cache + index join: 137s | total 10 cache + index join: 135s |
| Queries per second: 0.07 | Queries per second: 0.74 |
| start 10 no cache + no-index join: 19:36:36 | start 10 cache + no-index join: 19:41:14 |
| end 10 no cache + no-index join: 19:38:46 | end 10 cache + no-index join: 19:43:32 |
| total 10 no cache + no-index join: 130s | total 10 cache + no-index join: 138s |
| Queries per second: 0.08 | Queries per second: 0.72 |
| end myisam NO CACHE tests: 19:38:46 | end myisam CACHE tests: 19:43:32 |
| total myisam NO CACHE tests: 279s | total myisam CACHE tests: 286s |
Keep in mind, that total number of queries in second column is 10 times more.
Test results 2
query_cache_min_res_unit=512
| start myisam NO CACHE tests: 19:50:13 | start myisam CACHE tests: 19:54:39 |
| start 10 no cache queries: 19:50:13 | start 10 cache queries: 19:54:39 |
| end 10 no cache queries: 19:50:26 | end 10 cache queries: 19:54:51 |
| total 10 no cache queries: 13s | total 10 cache queries: 12s |
| Queries per second: 0.77 | Queries per second: 8.33 |
| start 10 no cache + index join: 19:50:26 | start 10 cache + index join: 19:54:51 |
| end 10 no cache + index join: 19:52:32 | end 10 cache + index join: 19:56:58 |
| total 10 no cache + index join: 126s | total 10 cache + index join: 127s |
| Queries per second: 0.08 | Queries per second: 0.79 |
| start 10 no cache + no-index join: 19:52:32 | start 10 cache + no-index join: 19:56:58 |
| end 10 no cache + no-index join: 19:54:39 | end 10 cache + no-index join: 19:59:05 |
| total 10 no cache + no-index join: 127s | total 10 cache + no-index join: 127s |
| Queries per second: 0.08 | Queries per second: 0.79 |
| end myisam NO CACHE tests: 19:54:39 | end myisam CACHE tests: 19:59:05 |
| total myisam NO CACHE tests: 266s | total myisam CACHE tests: 266s |
Keep in mind, that total number of queries in second column is 10 times more.
Test results 3
query_cache_min_res_unit=2048
| start myisam NO CACHE tests: 20:16:32 | start myisam CACHE tests: 20:21:10 |
| start 10 no cache queries: 20:16:32 | start 10 cache queries: 20:21:10 |
| end 10 no cache queries: 20:16:46 | end 10 cache queries: 20:21:23 |
| total 10 no cache queries: 14s | total 10 cache queries: 13s |
| Queries per second: 0.71 | Queries per second: 7.69 |
| start 10 no cache + index join: 20:16:46 | start 10 cache + index join: 20:21:23 |
| end 10 no cache + index join: 20:18:57 | end 10 cache + index join: 20:23:30 |
| total 10 no cache + index join: 131s | total 10 cache + index join: 127s |
| Queries per second: 0.08 | Queries per second: 0.79 |
| start 10 no cache + no-index join: 20:18:57 | start 10 cache + no-index join: 20:23:30 |
| end 10 no cache + no-index join: 20:21:10 | end 10 cache + no-index join: 20:25:38 |
| total 10 no cache + no-index join: 133s | total 10 cache + no-index join: 128s |
| Queries per second: 0.08 | Queries per second: 0.78 |
| end myisam NO CACHE tests: 20:21:10 | end myisam CACHE tests: 20:25:38 |
| total myisam NO CACHE tests: 278s | total myisam CACHE tests: 268s |
Keep in mind, that total number of queries in second column is 10 times more.
Conclusion 1
For our queries the most optimal value for query_cache_min_res_unit is 512.
Test results 4
query_cache_size=64M
| start myisam NO CACHE tests: 20:43:51 | start myisam CACHE tests: 20:48:31 |
| start 10 no cache queries: 20:43:51 | start 10 cache queries: 20:48:31 |
| end 10 no cache queries: 20:44:04 | end 10 cache queries: 20:48:43 |
| total 10 no cache queries: 13s | total 10 cache queries: 12s |
| Queries per second: 0.77 | Queries per second: 8.33 |
| start 10 no cache + index join: 20:44:04 | start 10 cache + index join: 20:48:43 |
| end 10 no cache + index join: 20:46:17 | end 10 cache + index join: 20:51:01 |
| total 10 no cache + index join: 133s | total 10 cache + index join: 138s |
| Queries per second: 0.08 | Queries per second: 0.72 |
| start 10 no cache + no-index join: 20:46:17 | start 10 cache + no-index join: 20:51:01 |
| end 10 no cache + no-index join: 20:48:31 | end 10 cache + no-index join: 20:53:23 |
| total 10 no cache + no-index join: 134s | total 10 cache + no-index join: 142s |
| Queries per second: 0.07 | Queries per second: 0.7 |
| end myisam NO CACHE tests: 20:48:31 | end myisam CACHE tests: 20:53:23 |
| total myisam NO CACHE tests: 280s | total myisam CACHE tests: 292s |
Keep in mind, that total number of queries in second column is 10 times more.
Test results 5
query_cache_size=512M
| start myisam NO CACHE tests: 21:01:00 | start myisam CACHE tests: 21:05:42 |
| start 10 no cache queries: 21:01:00 | start 10 cache queries: 21:05:42 |
| end 10 no cache queries: 21:01:15 | end 10 cache queries: 21:05:56 |
| total 10 no cache queries: 15s | total 10 cache queries: 14s |
| Queries per second: 0.67 | Queries per second: 7.14 |
| start 10 no cache + index join: 21:01:15 | start 10 cache + index join: 21:05:56 |
| end 10 no cache + index join: 21:03:27 | end 10 cache + index join: 21:08:07 |
| total 10 no cache + index join: 132s | total 10 cache + index join: 131s |
| Queries per second: 0.08 | Queries per second: 0.76 |
| start 10 no cache + no-index join: 21:03:27 | start 10 cache + no-index join: 21:08:07 |
| end 10 no cache + no-index join: 21:05:42 | end 10 cache + no-index join: 21:10:32 |
| total 10 no cache + no-index join: 135s | total 10 cache + no-index join: 145s |
| Queries per second: 0.07 | Queries per second: 0.69 |
| end myisam NO CACHE tests: 21:05:42 | end myisam CACHE tests: 21:10:32 |
| total myisam NO CACHE tests: 282s | total myisam CACHE tests: 290s |
Keep in mind, that total number of queries in second column is 10 times more.
Conclusion 2
On our system and with out queries most optimal value for query_cache_size is 256M.
InnoDB vs MyISAM
As you have probably noticed, I run all tests on MyISAM tables, as they are still quite popular. After figuring optimal values for MyISAM I have executed same tests on InnoDB tables and here are test results.
MySQL configuration
query_cache_type=1 query_cache_limit=1M query_cache_size=256M query_cache_min_res_unit=512
Test results, comparing MyISAM vs InnoDB
No-cache queries
| start myisam NO CACHE tests: 07:39:42 | start innodb NO CACHE tests: 07:49:47 |
| start 10 no cache queries: 07:39:42 | start 10 no cache queries: 07:49:47 |
| end 10 no cache queries: 07:39:55 | end 10 no cache queries: 07:49:58 |
| total 10 no cache queries: 13s | total 10 no cache queries: 11s |
| Queries per second: 0.77 | Queries per second: 0.91 |
| start 10 no cache + index join: 07:39:55 | start 10 no cache + index join: 07:49:58 |
| end 10 no cache + index join: 07:42:22 | end 10 no cache + index join: 07:50:08 |
| total 10 no cache + index join: 147s | total 10 no cache + index join: 10s |
| Queries per second: 0.07 | Queries per second: 1 |
| start 10 no cache + no-index join: 07:42:22 | start 10 no cache + no-index join: 07:50:08 |
| end 10 no cache + no-index join: 07:44:47 | end 10 no cache + no-index join: 07:51:42 |
| total 10 no cache + no-index join: 145s | total 10 no cache + no-index join: 94s |
| Queries per second: 0.07 | Queries per second: 0.11 |
| end myisam NO CACHE tests: 07:44:47 | end innodb NO CACHE tests: 07:51:42 |
| total myisam NO CACHE tests: 305s | total innodb NO CACHE tests: 115s |
Queries using MySQL cache
Please remember, that even though execution time is very close – we execute 10 times more queries.
| start myisam CACHE tests: 07:44:47 | start innodb CACHE tests: 07:51:42 |
| start 10 cache queries: 07:44:47 | start 10 cache queries: 07:51:42 |
| end 10 cache queries: 07:45:01 | end 10 cache queries: 07:51:50 |
| total 10 cache queries: 14s | total 10 cache queries: 8s |
| Queries per second: 7.14 | Queries per second: 12.5 |
| start 10 cache + index join: 07:45:01 | start 10 cache + index join: 07:51:50 |
| end 10 cache + index join: 07:47:27 | end 10 cache + index join: 07:51:58 |
| total 10 cache + index join: 146s | total 10 cache + index join: 8s |
| Queries per second: 0.68 | Queries per second: 12.5 |
| start 10 cache + no-index join: 07:47:27 | start 10 cache + no-index join: 07:51:58 |
| end 10 cache + no-index join: 07:49:47 | end 10 cache + no-index join: 07:53:23 |
| total 10 cache + no-index join: 140s | total 10 cache + no-index join: 85s |
| Queries per second: 0.71 | Queries per second: 1.18 |
| end myisam CACHE tests: 07:49:47 | end innodb CACHE tests: 07:53:23 |
| total myisam CACHE tests: 300s | total innodb CACHE tests: 101s |
Conclusion
It is very interesting to see, that same queries on InnoDB run in average 3 times faster, especially, if indexes are properly used.
Read more about MySQL cache configuration
https://dev.mysql.com/doc/refman/5.0/en/query-cache-configuration.html