…_write_io_threads = 4 innodb_file_per_table = 1 ## Disabling query cache query_cache_size = 0 query_cache_type = 0 You can see that… every partition would hold 100 million rows. Table Structure The table structure of the table with no secondary indexes is as follows: CREATE… growth in data-set on the insert rate. We can see that adding the secondary indexes to the table has decreased the…
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… instead is flushing tables regularly or reducing table cache reduces memory consumption to be withing the reason. Connection Related Allocations Another set of buffers…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…-H dataset (InnoDB tables) with a Scale Factor of 2 (InnoDB dataset size ~5G). Note that query cache is disabled during these…’ optimizer_switch=’join_cache_incremental=on’ optimizer_switch=’join_cache_hashed=on’ optimizer_switch=’join_cache_bka=on’ join_cache_level=8 join_buffer… benchmarking in-memory workload, the InnoDB buffer pool size is set to 6G and the buffer pool was warmed up, so…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… you have the following query executed on the InnoDB table: SELECT non_key_column FROM tbl WHERE key_column…5.6 and MariaDB 5.5. Note that query cache is disabled during these benchmark runs and that the…in-memory workload, the InnoDB buffer pool size is set to 6G and the buffer pool was warmed up,…
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 …
Comment: Should you move from MyISAM to Innodb ?
March 9, 2012 By 53 Comments
… overhead as compare to MyIsam. I change 90% of my tables to innodb and currently going into queries and db optimization. Could someone really confirm where should i use Innodb. Big table ie. users? comments? etc… i just compared MyIsam to Innodb… MyIsam. Will this be better once i tuned InnoDb settings? and add query cache? Thanks a lot for all the great info…
Post: SHOW OPEN TABLES - what is in your table cache
December 14, 2008 By 3 Comments
… in set (0.00 sec) This command lists all non-temporary tables in the table-cache, showing each of them only once (even if table… this table ? I would much rather see all entries in the table_cache used, not grouped by the table, with thread_id using the table set (0 if table is not in use…
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… 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… has a very similar task of managing its own cache of file descriptors (set by innodb_open_files) As the time allows…
Comment: More on table_cache
December 6, 2009 By 7 Comments
….000 tables. 2) Table_Cache set to 2.000 brings 16400 selects/sec. 3) Table_Cache set to 20.000 brings 13500 selects/sec. 4) Table_Cache set to 5 brings 8000 selects/sec. What if you set Table_Cache to 100… (from performance point of view) the best option is to set same number of buckets as you will have number of…
Post: Query Profiling with MySQL: Bypassing caches
September 12, 2007 By 6 Comments
… is also table_cache both MySQL side and Internal Innodb one which can affect query execution speed. OS Caches Operation Systems typically cache file… of the caches. You can do FLUSH TABLES to clean MySQL table cache (but not Innodb table meta data) or you can do “set global key_buffer_size=0; set global…
