…_write_io_threads = 4 innodb_file_per_table = 1 ## Disabling query cache query_cache_size = 0 query_cache_type = 0 You can see that… downward spikes in performance. Other then that, I have also disabled query cache to avoid contention caused by query cache on write heavy…, since I was mostly concerned with write performance, so I went ahead to choose a table structure with no secondary indexes. Other…
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: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… further extended to improve join performance. As I told you above, when table t1 would be joined to table t2, then selected rows… in the join condition and then a hash table lookup is performed, on the hash table we created earlier. This step is known…’ optimizer_switch=’join_cache_incremental=on’ optimizer_switch=’join_cache_hashed=on’ optimizer_switch=’join_cache_bka=on’ join_cache_level=8 join_buffer…
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…_refills + 1 MRR range scans had to be performed. As in the table above you can with default buffer size of 256K… calls made to the storage engine to perform the index record read and then to perform the MRR range scan on the…
Post: MariaDB 5.3 is released as GA!
March 5, 2012 By 17 Comments
…, faster subqueries, and elimination of useless tables in joins Thread pooling and a segmented key cache for improved scalability That’s really…different needs and personalities, with different characteristics, and feature and performance improvements are moving rapidly forward for the MySQL world as…
Post: table_cache negative scalability
November 16, 2009 By 23 Comments
… even in Performance focused MySQL 5.4. As we can see large table_cache (or table_open_cache_ values indeed can cause significant performance problems. Interesting enough Innodb has a very similar task of managing its own cache…
Post: Cache Performance Comparison
August 9, 2006 By 73 Comments
…Performance tuning ? Not surpising associative array cache performs the best, almost 4 times faster than APC shared memory cache – closest competitor. In real life performance…
Post: More on table_cache
November 26, 2009 By 7 Comments
… looked into how large table_cache actually can decrease performance. The “miss” path is getting more expensive very quickly as table cache growths so 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: MySQL Query Cache
July 27, 2006 By 69 Comments
… for high performance is Query Cache performs query lookup in the cache before query parsing, so if result is served from query cache, query parsing… it will not be cached. Table level granularity in invalidation – If table gets modification all queries derived from this table are invalidated at once…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 27 Comments
…’ve used for this benchmark. We have 4 tables: 1) Table with ENUM: CREATE TABLE cities_enum ( id int(10) unsigned NOT NULL… and VARCHAR show close performance, while join performance degraded dramatically. Here is why: mysql> explain select SQL_NO_CACHE c.city from cities… join query performance is 30% lower. Also note the times themselves – traversing about same amount of rows full table scan performs about 25…
