…_file_per_table = 1 ## Disabling query cache query_cache_size = 0 query_cache_type = 0 You can see that the buffer pool is sized at 55G… the table purchases_index which has 3 secondary indexes. Another thing I would like to share is that, the size of the table without secondary indexes is 56G while the size of the table with secondary indexes is 181G…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… done Check for Table Cache Related Allocations There are cases when MySQL will allocate a lot of memory for table cache, especially if you… Tables MEMORY tables can take memory. There are implicit MEMORY tables which are allocated for query execution, which size can be controlled by tmp_table_size…
Comment: Too many connections? No problem!
March 15, 2012 By 28 Comments
…-not-using-indexes query_cache_type=1 query_cache_size=32M thread_cache_size=30 table_cache=4096 join_buffer_size=6M key_buffer_size=12M server-id = 1…-bin=/data/mysql/drbd expire_logs_days = 5 max_binlog_size = 100M [client] socket=/data/mysql/mysql.sock When I start…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… TPC-H dataset (InnoDB tables) with a Scale Factor of 2 (InnoDB dataset size ~5G). Note that query cache is disabled during these…_cache_incremental=on’ optimizer_switch=’join_cache_hashed=on’ optimizer_switch=’join_cache_bka=on’ join_cache_level=8 join_buffer_size=6M mrr_buffer_size…_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: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So in the same way this blog post is aimed at a new optimizer enhancement Index Condition …
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… MySQL 5.6 and MariaDB 5.5. Note that query cache is disabled during these benchmark runs and that the disks…/ read_rnd_bufer_size=4M MariaDB 5.5 MariaDB 5.5 w/ mrr_buffer_size=4M Created_tmp_disk_tables 1 1 1 1 1 Created_tmp_tables 1 1 1 1 1…
Post: table_cache negative scalability
November 16, 2009 By 23 Comments
… table headers) is well cached so the cost of table cache miss is not very high, you may be better of with significantly reduced table cache size. The next step for me was to see if the problem was fixed in MySQL 5.1 – in this version table_cache was significantly redone and split in table_open_cache and table_definition_cache and I assumed the behavior may…
Post: More on table_cache
November 26, 2009 By 7 Comments
… off with small table cache. What I have not checked though is how does table_cache (or table_open_cache in newer version) size affects the hit… ? If you can fit your tables in table cache and eliminate (or almost eliminate) table cache misses it is best to size table_cache large enough – even in…
Post: Beware large Query_Cache sizes
March 23, 2007 By 3 Comments
… with MySQL Query Cache being source of serious problems. One would see queries both for MyISAM and Innodb tables mysteriously stalling for… you set query_cache_size relatively high at 256MB or more. It can be seen worse if your query cache size is in Gigabytes. At the same time check how many queries do you have in cache – Qcache_queries_in_cache – if…
Post: Using Multiple Key Caches for MyISAM Scalability
November 26, 2008 By 8 Comments
… separate key cache for all actively accessed tables (assuming there are only few of them), allocating key_cache proportional to their size and load, but no more than the index size (assuming table sizes are relatively static) To get accurate information about table…
