…_io_threads = 4 innodb_file_per_table = 1 ## Disabling query cache query_cache_size = 0 query_cache_type = 0 You can see that the buffer pool… caused by query cache on write heavy workload. OK, so that was all about the configuration of the EC2 instance and MySQL. Now as far as the benchmark itself is concerned, I made no code changes to iiBench…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… on the surface becomes challenge with MySQL because there are no clear resource usage metrics available,… Cache Related Allocations There are cases when MySQL will allocate a lot of memory for table cache,… can do better as you can query temporary tables too: mysql> select sum(data_length+index_…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…join_cache_incremental=on’ optimizer_switch=’join_cache_hashed=on’ optimizer_switch=’join_cache_bka=on’ join_cache_level=…to 6M, the query time for MySQL 5.6 becomes approximately equal to that of MySQL 5.5. …can see that with appropriately sized buffers less no. of Innodb_buffer_pool_reads are done, …
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… case of MySQL 5.5 and ~5 hours in case of MySQL 5.6 and MariaDB 5.5. Note that query cache is disabled… query times remain under a minute. So when the correct query execution plan is not used, there is no difference in query times between MySQL 5.5 and MySQL 5.6/MariaDB…
Post: Query Profiling with MySQL: Bypassing caches
September 12, 2007 By 6 Comments
… run into question like this “I’m using SQL_NO_CACHE but my query is still much faster second time I run it… question is simple – because SQL_NO_CACHE only bypasses query cache but it has no change on other caches, which are MySQL Caches – Innodb Buffer Pool and Key… is also table_cache both MySQL side and Internal Innodb one which can affect query execution speed. OS Caches Operation Systems typically cache file IO…
Post: MySQL Query Cache
July 27, 2006 By 69 Comments
…also use SQL_NO_CACHE to block caching for certain queries, which you know do not need to be cached. Counting query cache efficiency …by query cache. Now lets speak a bit about Query Cache configuration and mainance. MySQL Manual is pretty good on this: Query Cache Query Cache Status Query Cache …
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…MySQL ….commanual no_defaultsblogpercona…query_alloc_block_sizeblogpercona.commanual query_cache_limitblogpercona.commanual query_cache_min_res_unitblogpercona.commanual query_cache_sizeblogpercona.commanual query_cache_typeblogpercona.commanual query_cache_wlock_invalidateblogpercona.commanual query…
Post: MySQL caching methods and tips
April 4, 2011 By 4 Comments
… MySQL. Popular cache methods The MySQL query cache When the query cache is enabled, MySQL examines each query to see if the contents have been stored in the query cache… is that they are persistent unlike the query cache or Memcached. There is no risk of unexpected invalidation, either. The summary …
Post: mk-query-digest, query comments and the query cache
… this test. You can enable query cache stripping in Percona Server at runtime: mysql> set global query_cache_strip_comments=’ON’; Query OK, 0 rows affected (0.00 sec) QueryHitsMissesHitsMisses StrippingNo Stripping no comment1000 1000 1000…