… 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… Caches State of CPU cache may affect query execution speed but only lightly, the hardware IO cache may however cause dramatic difference. Hardware RAID cache… way to clean all of the caches. You can do FLUSH TABLES to clean MySQL table cache (but not Innodb table meta data…
Comment: MySQL Query Cache
December 23, 2008 By 70 Comments
Thanks for the nice post, Peter. You mentioned that FLUSH QUERY CACHE needs to be run to defragment the free blocks. The … to 3. No one ran FLUSH QUERY CACHE in the server. I wonder what triggered the defragmentation? Is the query_cache completely reset (so that… (pruning)? I was planning to have a cron to run FLUSH QUERY CACHE on weekly basis but may be it isn’t required…
Comment: Using MyISAM in production
September 7, 2006 By 36 Comments
… qury cache uses a sequential search through a list of free blocks when a single query is being removed from the cache. Regularly consolidating the free space with FLUSH QUERY CACHE may or may not… the time it takes to remove queries it can be faster to have a small cache and anyone going above 100M should…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…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: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…_files_in_group=2 innodb_flush_log_at_trx_commit=2 innodb_flush_method=O_DIRECT query_cache_size=0 query_cache_type=0 MySQL 5…_files_in_group=2 innodb_flush_log_at_trx_commit=2 innodb_flush_method=O_DIRECT query_cache_size=0 query_cache_type=0 optimizer_switch…_files_in_group=2 innodb_flush_log_at_trx_commit=2 innodb_flush_method=O_DIRECT query_cache_size=0 query_cache_type=0 optimizer_switch…
Post: Benchmarking single-row insert performance on Amazon EC2
May 16, 2012 By 17 Comments
… log flushing, which can cause downward spikes in performance. Other then that, I have also disabled query cache to avoid contention caused by query cache… mutexes, using “estimate” checkpoint method to reduce chances of log flush storms and disabling the query cache.
Comment: New Forum Categories: Help Wanted, For Hire
March 5, 2013 By 1 Comment
…_size = 8M table_open_cache = 1024 thread_cache_size = 8 query_cache_size = 128M join_buffer_size = 8M long_query_time=300 key_buffer_size… innodb_log_files_in_group=2 innodb_flush_log_at_trx_commit=1 innodb_flush_method=O_DIRECT innodb_thread_concurrency=16…_size = 8M table_open_cache = 1024 thread_cache_size = 8 query_cache_size = 128M join_buffer_size = 8M long_query_time=300 key_buffer_size…
Comment: MySQL Server Memory Usage
September 1, 2006 By 21 Comments
…’ ‘Delayed_errors’, ’0′ ‘Delayed_insert_threads’, ’0′ ‘Delayed_writes’, ’0′ ‘Flush_commands’, ’5′ ‘Handler_commit’, ’19092941′ ‘Handler_delete’, ’0′ ‘Handler_discover… isnt used. sort_buffer_size=2M query_cache_size=10M # Might be increased thread_cache_size=40 table_cache=512 # Default 64 was used…
Post: What to tune in MySQL Server after installation
September 29, 2006 By 118 Comments
… backed up cache. Many applications, especially those moved from MyISAM tables are OK with value 2 which means do not flush log to the disk but only flush it to OS cache. The log is still flushed to the disk… in normal operation. query_cache_size If your application is read intensive and you do not have application level caches this can be…
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
… engines transaction logs, slow query log or general log. flush, flush_time Flushing (closing) of tables after query is complete or every flush_time seconds. This… general. Currently MyISAM and Innodb. Some SQL level items as Query Cache also could use this feature. low_priority_updates If enabled…
