….5 FLUSH TABLES WITH READ LOCK does not work as optimally as you could …hours for this statement to complete. Here is example how it can look: mysql> show processlist; …to let backup to proceed, but resolving server gridlock one way or another. If you’re just using Innodb tables…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… TPC-H dataset (InnoDB tables) with a Scale Factor of 2 (InnoDB dataset size ~5G)….how effective are the join optimizations when the workload fits entirely in memory. For the purpose of benchmarking in-memory workload, the InnoDB… in-memory workload. I intend to run tests to see what specific types of…
Comment: How much space does empty Innodb table take ?
April 3, 2012 By 7 Comments
… overhead in InnoDB. * A 16KB block is never completely full. After a lot of churn, the block splits will lead to an… predicts. Peter, I have empirically determined that “free space” (of TABLE STATUS) does not include free space in the 16KB blocks… completely freed. This makes it difficult to know when to OPTIMIZE a table (or REORGANIZE PARTITION) because one cannot see how much would be freed.
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…InnoDB tables) with a Scale Factor of 2 (InnoDB dataset size ~5G). I did not use Scale Factor of 40 (InnoDB…how counter is calculated inside MariaDB? Other Observations Sometimes both for MariaDB 5.5 and MySQL 5.6, the optimizer… cold query runs the optimizer would switch to using plan ‘a’, which does…
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 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 Pushdown (ICP). Its available in both MySQL…
Post: Recovering Innodb table Corruption
July 4, 2008 By 19 Comments
…how to get your data back from simple Innodb Table Corruption. In more complex cases you may need to use higher innodb_force_recovery modes to…
Post: How to load large files safely into InnoDB with LOAD DATA INFILE
July 3, 2008 By 28 Comments
… to just shut everything down and re-clone the machine from another, which takes about 10 or 12 hours. InnoDB is not optimized for rollbacks, it’s optimized for transactions that succeed and commit. Rollback can take an order of magnitude longer to… as ALTER TABLE in 10k row chunks, by the way; I chose 1M because the rows were small). But how to do this…
Post: How to estimate time it takes Innodb to Recover ?
May 9, 2007 By 7 Comments
… record size and workload. If you have tables with short rows your log records will…worth to note Innodb optimizes log recovery by reading portions of log records and sorting them to apply them to… system to handle and crash Innodb few times to see how long it takes Innodb to recover. I’d recommend to…
Post: Optimizing InnoDB for creating 30,000 tables (and nothing else)
December 22, 2011 By 5 Comments
… madness to even attempt to create 30,000 tables in InnoDB. That time is now a memory. We have customers with a lot more tables… option for innodb-flush-method called “nosync” that is meant to not flush data to disk. Since you could hear how much syncing to disk… using an easy to use C API. Writing a simple test program that creates 30,000 tables in a similar InnoDB configuration as…
Post: Efficient Boolean value storage for Innodb Tables
April 23, 2008 By 6 Comments
… how these 3 different table format look in Innodb (I’ve populated each with some 2M rows so difference is more visible) CREATE TABLE… row overhead Innodb has this will transform to much larger savings if you have hundreds of such columns. Lets see how things look… efficient if you would like optimal structure both for MyISAM and Innodb tables, however it is not as convenient to work with. Using NULL…
