…_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…
Post: MySQL Query Cache
July 27, 2006 By 69 Comments
… data sets. For example I’m using Query Cache on server which runs this blog. Updates are rather rare so per-table granularity… set minimal TTL or anything like it which is allowed by other caching systems. Also note – all queries are removed from cache on table… have tables which are never modified which would flood query cahe but it unusual. So you might want to set query cache to…
Post: Cache Performance Comparison
August 9, 2006 By 73 Comments
…Cache 98000 File Cache 27000 Memcached Cache (TCP/IP) 12200 MySQL Query Cache (TCP/IP) 9900 MySQL Query Cache (Unix Socket) 13500 Selecting from table… result sets are cached from query cache. Pretty impressive. So what my recommendations would be about using these caches for your application ? Cache…
