…is a max_heap_table_size variable which allows you to limit size of MEMORY tables (the limit… can query temporary tables too: mysql> …away memory allocation is Dictionary cache though …MySQL, MariaDB or Drizzle bring improvements in this space allowing us to see directly for what purpose memory is…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… this blog post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take a look briefly at what this enhancement actually is, and what is it aimed at. Index Condition Pushdown Traditional B-Tree index lookups have some limitations in…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…Traditionally, MySQL always uses Nested Loop Join to join two or more tables. What this means is that, …of 2 (InnoDB dataset size ~5G). Note that query cache is disabled during these benchmark runs and that the…, o_shippriority order by revenue desc, o_orderdate LIMIT 10; In-memory workload Now let’s …
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…both MySQL 5.6 and MariaDB 5.5 Now let’s take a look at what this optimization actually is and what… of MySQL 5.5 and ~5 hours in case of MySQL 5.6 and MariaDB 5.5. Note that query cache is disabled…order by revenue desc LIMIT 20; In-memory workload Now let’s see how effective is MRR when the workload…
Post: MySQL Query Cache
July 27, 2006 By 69 Comments
… clarify what 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 is not… MySQL level caching but it is other story. Lets talk a bit about features and limitations of Query Cache: Transparent Caching – Caching is fully transparent to the application, and what is…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…MySQL manual, … to you what is now my …query_alloc_block_sizeblogpercona.commanual query_cache_limitblogpercona.commanual query_cache_min_res_unitblogpercona.commanual query_cache_sizeblogpercona.commanual query_cache_typeblogpercona.commanual query_cache_wlock_invalidateblogpercona.commanual query…
Post: Should we give a MySQL Query Cache a second chance ?
April 10, 2011 By 63 Comments
…query syntax may be hard. Query Cache Storage Engines MySQL Query Cache now stores queries in local memory, which is great however memory amount might be limited…. This is especially the problem for very expensive queries. The good thing about MySQL Query Cache is it knows exactly what is needed to …
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 27 Comments
… making MySQL to discard first 10000 records. 1) Results for ENUM: select SQL_NO_CACHE city from cities_enum WHERE state=’Minnesota’ limit… in fact it is quite expected – This is MyISAM table which is accessed via index, which means to retrieve each row MySQL will have… rows to make query times longer. 1) Results for ENUM: select SQL_NO_CACHE city, state from cities_enum limit 10000, 5; Result…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…queries‘ execution plan (which includes things like whether Query cache…is 0, which means it accesses these pages in memory. What it says though is that, if this query would run on a cold MySQL…
Post: MySQL Limitations Part 4: One thread per connection
October 27, 2010 By 14 Comments
…what‘s seriously limiting MySQL in core use cases (links: part 1, 2, 3). This post is about the way MySQL…connection to MySQL, because it just requires creating a thread (or taking one from a cache). This is … things like the lower startup cost for the query). But MySQL isn’t a data warehouse DBMS …
