…. As of MySQL 5.5 FLUSH TABLES WITH READ LOCK does not work as optimally as you could think it works. Even… | 2359297 | | 10290 | root | localhost | NULL | Query | 317 | Waiting for table flush | flush tables with read lock | 0 | 0 | 1 | | 10291 | root | localhost…’re just using Innodb tables and you’re not actively changing users, stored procedures etc (which are stored in MyISAM tables anyway) you…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… and ran it on TPC-H dataset (InnoDB tables) with a Scale Factor of 2 (InnoDB dataset size ~5G). Note that query cache… config: optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr=on’ optimizer_switch=’mrr_sort_keys=on’ optimizer_switch=’mrr_cost_based=off’ optimizer_switch…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… example. Suppose you have the following query executed on the InnoDB table: SELECT non_key_column FROM tbl WHERE key_column=x… dataset (InnoDB tables) with a Scale Factor of 2 (InnoDB dataset size ~5G). I did not use Scale Factor of 40 (InnoDB dataset size… were made in the MySQL config: optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr=on’ optimizer_switch=’mrr_sort_keys=on’ (only…
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…
Comment: How much space does empty Innodb table take ?
April 3, 2012 By 7 Comments
Bruno, There is a lot of overhead in InnoDB. * A 16KB block is never completely full. After a lot … 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…
Comment: What does Using filesort mean in MySQL?
March 15, 2012 By 27 Comments
I looked at the “ORDER BY OPTIMIZATION” section in MySQL manual but could not find a reason … MySQL does not use index in the following scenario: CREATE TABLE table1 ( id bigint(20) NOT NULL AUTO_INCREMENT, a varchar…) DEFAULT NULL, PRIMARY KEY (id), KEY (a), KEY (b) ) ENGINE=InnoDB Table 1 has 512000 randomly generated rows. This query uses index…
Comment: Should you move from MyISAM to Innodb ?
March 9, 2012 By 53 Comments
…% of my tables to innodb and currently going into queries and db optimization. Could someone really confirm where should i use Innodb. Big table ie. users? comments? etc… i just compared MyIsam to Innodb while…
Post: MariaDB 5.3 is released as GA!
March 5, 2012 By 17 Comments
… joins, faster subqueries, and elimination of useless tables in joins Thread pooling and a segmented …timestamp support, as well as a lot of optimizer improvements. Even the standard MySQL 5.5 …, improvements to partitioning, and the addition of multiple InnoDB buffer pools. Best of all, MariaDB 5….
Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
December 9, 2010 By 13 Comments
… produces a lot better fill factor. The problem is…. OPTIMIZE TABLE for Innodb tables does not get advantage of it for whatever reason. Lets take a look at little benchmark I done by running OPTIMIZE… stops you from using LOCK TABLES on Innodb table to ensure there is not ton of queries starting reading table with no indexes and…
Post: Recovering Innodb table Corruption
July 4, 2008 By 19 Comments
… you can create or drop Innodb tables): mysql> optimize table test; +———–+———-+———-+———————————-+ | Table | Op | Msg_type | Msg_text | +———–+———-+———-+———————————-+ | test.test | optimize | error | Got error -1 from…
