… 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 Buffer are best example… global key_buffer_size=DEFAULT” to zero out key buffer but there is no way to clean Innodb Buffer Pool without restart. For OS Caches on…
Post: How expensive is a WHERE clause in MySQL?
October 31, 2008 By 17 Comments
… MySQL query? To find out, I set my InnoDB buffer pool to 256MB and created a table that’s large enough…’s see how long a table scan with no WHERE clause takes: mysql> select sql_no_cache count(*) from t; +———-+ | count(*) | +———-+ | 8388608 | +———-+ 1 row… t where a = current_date; (Pop quiz: do I need SQL_NO_CACHE on this query?) The average execution time for this query…
Post: Getting real life query speeds with MySQL
January 10, 2007 By 10 Comments
… it is often using SELECT SQL_NO_CACHE or disabling QueryCache for the test. This works by blocking query cache but still repeated runs… other caches and QueryCache is only first in line. There is also all kinds of page caches inside MySQL (key_buffer and innodb_buffer_pool… is significant chance data will still remain in Operation System cache speeding up query run. So what can you do ? First…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…buffer_pool_instancesblogpercona.commanual Innodb_buffer_pool_pages_datablogpercona.commanual Innodb_buffer_pool_pages_dirtyblogpercona.commanual Innodb_buffer_pool_pages_flushedblogpercona.commanual Innodb_buffer_pool_pages_freeblogpercona.commanual Innodb_buffer_pool…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…Sending data select sql_calc_found_rows… there are no running transactions …cache, and being sequential writes these logs writes are pretty fast. ———————- BUFFER POOL AND MEMORY ———————- Total memory allocated 4648979546; in additional pool allocated 16773888 Buffer pool…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…buffer pool size of 1G, while benchmark on in-memory workload was performed with a buffer pool…cache_incremental=on’ optimizer_switch=’join_cache_hashed=on’ optimizer_switch=’join_cache_bka=on’ join_cache_level=4 join_buffer_size=32M join_buffer…a large table with no WHERE clause The SQL used for this…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
… | | TABLE_OPEN_CACHE | 400 | 2000 | | INNODB_VERSION | 5.5.30 | 1.2.10 | | INNODB_BUFFER_POOL_INSTANCES | 1 | 8 | | QUERY_CACHE_SIZE | 0 | 1048576… to be practical if someone tries to enable it. sql_mode has NO_ENGINE_SUBSTITUTION value by default which is good change… now set to 1000 making Innodb Buffer Pool Size scan resistant by default. Very welcome change ! thread_cache_size is enabled by default…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…0.161528 | | end | 0.000007 | | Waiting for query cache lock | 0.000002 | | end | 0.000007 | | query … buffer pool had a huge impact on ALTER TABLE with the optimization disabled, and absolutely no …-optimize-keys > dump_optimized.sql $ time mysql -uroot test < dump_unoptimized.sql real 2m52.785s …
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
….637 ms … Note: There is no need to put that hosts …-storage-engine=INNODB innodb-buffer-pool-instances=2 innodb-buffer-pool-size=5600M innodb-…cache skip-name-resolve sort-buffer-size=512K sql-mode=STRICT_TRANS_TABLES symbolic-links table-definition-cache=16384 table-open-cache=128 thread-cache…
Post: New SpecJAppServer results at MySQL and Sun.
November 12, 2008 By Leave a Comment
…benchmarks there is no direct comparison available…sql-mode = IGNORE_SPACE transaction-isolation = READ-COMMITTED max_allowed_packet = 1M max_connections=300 max_connect_errors=100 table_cache…buffer_pool_size = 5000m innodb_additional_mem_pool_size = 20M innodb_log_file_size = 1600M innodb_log_buffer…
