… performed on the second table. This means many point queries, say for example if table1 yields 1000 rows then 1000 index lookups… more index range lookups. This means MySQL can employ many other optimizations like for example if columns other then the secondary key… Scale Factor of 2 (InnoDB dataset size ~5G). Note that query cache is disabled during these benchmark runs and that the disks…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take… condition is applied cannot be used for filtering records. For example, suppose you have a key defined as: KEY `i_l…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
…Table Cache Related Allocations There are cases when MySQL will allocate a lot of memory for table cache, …you can do better as you can query temporary tables too: mysql> select sum(data_length+index_length)…be connected to any known resource use. For example for global memory leaks you would see …
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… key values. Let me give you a simple example. Suppose you have the following query executed on the InnoDB table: SELECT non… case of MySQL 5.5 and ~5 hours in case of MySQL 5.6 and MariaDB 5.5. Note that query cache is disabled… when the correct query execution plan is not used, there is no difference in query times between MySQL 5.5 and MySQL 5.6…
Post: MariaDB 5.3 is released as GA!
March 5, 2012 By 17 Comments
… Faster queries through speedier joins, faster subqueries, and elimination of useless tables in joins Thread pooling and a segmented key cache for… upcoming MySQL 5.6 in the same or slightly different forms, so there is some subtlety to this list. For example, Percona Server has the same group commit fix, and MySQL 5.6 offers microsecond timestamp support…
Post: Should we give a MySQL Query Cache a second chance ?
April 10, 2011 By 63 Comments
… does for example). Query Cache is also one of the few cache implementations which I’d call an Active Cache meaning it takes …MySQL Query Cache is most useful for complex expensive queries, for which tracking exact rows used from query syntax may be hard. Query Cache Storage Engines MySQL Query Cache…
Post: Return of the Query Cache, win a Percona Live ticket
August 26, 2011 By 4 Comments
… Live London ticket! But first, what’s new with the MySQL query cache? You may know that it still has the same fundamental… improvements into the query cache and the interface between it and MySQL. It’s now possible to completely disable it, for example. This used… that’s gone. As of MySQL 5.5, the query cache mutex isn’t hit at all if query_cache_type is zero. We made…
Post: MySQL Query Cache
July 27, 2006 By 69 Comments
…MySQL Query Cache is – I’ve seen number of people being confused, thinking MySQL Query Cache is the same as Oracle Query Cache – meaning cache where execution plans are cached. MySQL Query Cache… dealing with small data sets. For example I’m using Query Cache on server which runs this blog…
Post: Cache Performance Comparison
August 9, 2006 By 73 Comments
…envinronment are: Cache Type Cache Gets/sec Array Cache 365000 APC Cache 98000 File Cache 27000 Memcached Cache (TCP/IP) 12200 MySQL Query Cache (TCP/IP) 9900 MySQL Query Cache (Unix Socket…may make sense. For example you might wish to use APC Cache as L1 cache and File cache as L2 cache if you have …
Post: MySQL caching methods and tips
April 4, 2011 By 4 Comments
…MySQL. Popular cache methods The MySQL query cache When the query cache is enabled, MySQL examines each query to see if the contents have been stored in the query cache…
