… the very frequent cases with performance problems with MySQL is what they happen every so often or certain times. Investigating them we find out what the cause is some batch jobs, reports and other non… times will increase and user experience will suffer. So what you can do to prevent this problem from happening ? The answer is…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… briefly explain these optimizations. Batched Key Access Traditionally, MySQL always uses Nested Loop Join to join two or more tables. What this means… additional optimization that is used in conjunction with BKA and that is MRR Key-ordered Scan. Let’s see what this optimization actually is. Key-ordered Scan for BKA With this optimization the idea of MRR is further extended to improve…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
….5 Now let’s take a look at what this optimization actually is and what benefits it brings. Multi Range Read With traditional… MySQL 5.6, the optimizer chooses the wrong query execution plan. Let’s take a look at what are the good and….00 Using where So during cold query runs the optimizer would switch to using plan ‘a’, which does not involve MRR, and…
Comment: How much space does empty Innodb table take ?
April 3, 2012 By 7 Comments
…. After a lot of churn, the block splits will lead to an average of a block being 69% full. * Due…. * Each row has (used to have) 29 bytes of overhead. Your 77 bytes is actually less than what this info predicts. Peter… (or whatever) is completely freed. This makes it difficult to know when to OPTIMIZE a table (or REORGANIZE PARTITION) because one cannot see…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We… same way this blog post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL… Now let’s take a look briefly at what this enhancement actually is, and what is it aimed at. Index Condition Pushdown…
Comment: Joining many tables in MySQL - optimizer_search_depth
April 23, 2012 By 6 Comments
… this what your blog boils down to? At the time when I implemented the so-called “greedy optimizer“, I did some experiments to determine what is a reasonable limit for the search depth (and the total number of tables) that can be optimized in… join optimizer is N!. The goal of this choice was to provide a conservative limit that guarantees that any query can be optimized…
Comment: Joining many tables in MySQL - optimizer_search_depth
April 23, 2012 By 6 Comments
… post is what default value of optimizer_search_depth is impractical as 62! which is potential number of combinations it exposes to is huge and it needs to be lowered down significantly in the cases optimizer spends too much time choosing the plan. I’ve looked at playing with optimizer…
Post: MariaDB 5.3 is released as GA!
March 5, 2012 By 17 Comments
… very good indeed. Peter will present the results in an “optimizer standoff” talk at the upcoming MySQL conference, and Timour and… its features might be helpful to you. So what‘s new and great in MariaDB as compared to MySQL? A lot of things… as a lot of optimizer improvements. Even the standard MySQL 5.5 offers pluggable authentication; you just have to pay for the…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… because of the time it may take to complete this statement. Lets look at what the problem is. As of MySQL 5.5 FLUSH TABLES WITH READ LOCK does not work as optimally as… is waiting for that very nasty “full join” select to complete. What is worse as the statement started execution all writes will…
Comment: Finding out largest tables on MySQL Server
April 24, 2012 By 24 Comments
… the total_size column equals 1GB, how will this relate to the actual size of the table.ibd file on disk….ibd file will not shrink if data is deleted. However, what I’m wondering is if there is a large discrepancy… this an indicator that it may be a good opportunity to run OPTIMIZE and thus reclaim some hard disk space? Taking real…
