… Extra Large … MySQL configuration. MySQL …table = 1 ## Disabling query cache query_cache_size = 0 query_cache_type = 0 You can see that the buffer pool is sized…KEY (`transactionid`), KEY `marketsegment` (`price`,`customerid`), KEY `registersegment` (`cashregisterid`,`price`,`customerid`), KEY…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… configured MySQL to use reasonable global buffers, such as innodb_buffer_size, key_buffer_size etc, you have reasonable amount of connections but yet MySQL takes…Table Cache Related Allocations There are cases when MySQL will allocate a lot of memory for table cache, especially if you’re using large…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…MySQL 5.6 MySQL 5.6 w/ join_buffer_size=6M & read_rnd_buffer_size=6M MariaDB 5.5 MariaDB 5.5 w/ join_buffer_size=6M & mrr_buffer_size=6M MariaDB 5.5 Hash Join Disabled w/ join_buffer_size=4M & mrr_buffer_size=4M Created_tmp_disk_tables…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…of the size of the buffer used for sorting the secondary key tuples. If the buffer size is large …size of the secondary key tuples fetched, and N is the buffer size. In MySQL 5.6 the buffer size …MySQL 5.6 w/ read_rnd_bufer_size=4M MariaDB 5.5 MariaDB 5.5 w/ mrr_buffer_size=4M Created_tmp_disk_tables…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…key_buffer_sizeblogpercona.commanual key_cache_age_thresholdblogpercona.commanual key_cache_block_sizeblogpercona.commanual key_cache_division_limitblogpercona.commanual Key_read_requestsblogpercona.commanual Key_readsblogpercona.commanual Key…
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
…tables. So first MySQL versions did not bother naming variables with storage engines prefix. This is why we have key_buffer, not “myisam_key_buffer…key_buffer_size Key Buffer used to buffer Index blocks (row data is not cached). Used only for MyISAM tables. If you do not use MyISAM tables…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…11 with XtraDB. Requires m1.large or larger. ami-f65ea19f shard…sys-stats-table innodb-write-io-threads=4 join-buffer-size=16M key-buffer-size=64M…table-definition-cache=16384 table-open-cache=128 thread-cache-size=32 thread-stack=256K tmp-table-size=64M transaction-isolation=READ-COMMITTED user=mysql…
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
… with default key_buffer_size which is not large enough to fit all key blocks from temporary table index, so we get a lot of key writes… MEMORY tables for the same queries: mysql> set global key_buffer_size=8000000; Query OK, 0 rows affected (0.03 sec) mysql> set global max_heap_table_size…
Post: What to tune in MySQL Server after installation
September 29, 2006 By 116 Comments
… tables. Innodb tables are much more sensitive to buffer size compared to MyISAM. MyISAM may work kind of OK with default key_buffer_size even with large…MySQL Server crashes. Value 2 only cause data loss with full OS crash. table_cache – Opening tables can be expensive. For example MyISAM tables…
Post: MySQL Server Memory Usage
May 17, 2006 By 18 Comments
…buffers which are allocated at start and always where – these are key_buffer_size, innodb_buffer_pool_size, innodb_additional_memory_pool_size, innodb_log_buffer_size, query_cache_size…
