Optimizing MySQL, part 3, mysql cache

mysql_queries-week-web14In 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 with SELECT 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.

  1. First I selected 10 random records from the big table;
  2. Executed 10 queries using “select like” statement;
  3. Executed 10 queries using indexed join;
  4. 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

Leave a Reply

Your email address will not be published. Required fields are marked *