… comparable variables of how many user variables are allocated (and how much memory they use). Memory Tables MEMORY tables can take memory. There are implicit MEMORY tables which… see how much memory is being used by current MEMORY tables: mysql> select sum(data_length+index_length) from information_schema.tables where engine=’memory…
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: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 22, 2012 By 10 Comments
Oystein, As benchmark show using MRR makes sense when data is not in memory. Do you have any plans to store information about how much of the table fits in memory ? It can be helpful for other choices too, for example full table scan vs index scan depends a lot whenever table is in memory or not.
Comment: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 22, 2012 By 10 Comments
… it would be great if the cost calculation would include how much of the data fits in memory for a particular query.
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… only works with equi-joins. Now let me briefly explain how hash join algorithm works. Suppose you have two tables t1… 10; In-memory workload Now let’s see how effective are the join optimizations when the workload fits entirely in memory. For the… for IO bound workload but does not make much of a difference to in-memory workload. Also BKA relies on both the…
Post: Joining many tables in MySQL - optimizer_search_depth
April 20, 2012 By 5 Comments
…less than 1000 rows and doing it completely in memory. The plan optimizer picked was very good one, … search. From the same discussion we can learn how automatic selection works – it picks value of min(…of many tables as optimizer heuristics are improved so much higher search depths are feasible now…
Post: How much memory Innodb locks really take ?
July 13, 2006 By 11 Comments
… less than 3 bits per locked row; Now lets see how much exclusinve locks take: mysql> begin; Query OK, 0 rows affected… millions per second on modern systems. So we have tested how much memory is using locking all the rows – so all rows are… of locked rows should take half GB of memory, which is small fraction of memory used on serious systems. Furtermore you would…
Post: How much memory Innodb Dictionary can take ?
May 6, 2010 By 3 Comments
… XtraDB we have an option to restrict that limit. So how much memory can it really take ? Here is some production stats from….75 sec) In this case The memory stats from SHOW INNODB STATUS look like: Total memory allocated 1101004800; in additional pool allocated…) Recovery system 0 (0 + 0) Threads 408536 (406936 + 1600) Dictionary memory allocated 698012720 So considering about 700M were allocated for dictionary…
Post: MySQL for Hosting Providers - how do they manage ?
November 28, 2008 By 15 Comments
… always wonder how do they manage to keep things up given MySQL gives you so little ways to really restrict how much resources… in the PROCESS LIST and you can’t really track how much memory was allocated for given session (or restrict this number) Now…_mask=0x201d2, order=0, oomkilladj=0 Call Trace: [] out_of_memory+0x8e/0x2f5 [] __alloc_pages+0×245/0x2ce [] __do_page_cache_readahead…
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
… with MEMORY storage engine so must use on disk MyISAM temporary table. What i decided to do is to see how much overhead do on disk temporary tables cause compared to MEMORY tables. To have things comparable… proportionally with table size, even when all data fits in memory MEMORY temporary tables can be 10-100 times faster than disk…
