One of the very frequent cases with performance problems with MySQL is what they happen every so often or certain times… need to know it is not MySQL problem, might be even not problem with your MySQL configuration, queries and hardware, even though…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one… post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… problem is. As of MySQL 5.5 FLUSH TABLES WITH READ LOCK does not work as optimally as you could think it… query blocks any READ queries to execute on the table. mysql> show processlist; +——-+——+———–+———-+————+——+——————————+——————————+———–+—————+———–+ | Id | User | Host | db | Command | Time | State | Info… gridlock one way or another. If you’re just using Innodb tables and you’re not actively changing users, stored procedures…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…made on MySQL 5.6 config: optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr=on’ optimizer_switch=’mrr_cost_based=off’ optimizer_switch=’batched…2.2G Innodb_data_reads 329115 355323 143808 335526 16164 15506 Innodb_pages_read 329115 355323 143808 358308 144798 144881 Innodb_rows_…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… the MySQL config: optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr=on’ optimizer_switch=’mrr_sort_keys=on’ (only on MariaDB 5.5) optimizer… rows than MySQL 5.5, as can be seen by the numbers reported for the status counter Innodb_rows_read. While MySQL 5… thing is that though both MariaDB and MySQL 5.6 are reporting high numbers for Innodb_rows_read, which is completely in…
Post: MariaDB 5.3 is released as GA!
March 5, 2012 By 17 Comments
… and MySQL 5.6 optimizer improvements, and both of them look very good indeed. Peter will present the results in an “optimizer standoff… fix, and MySQL 5.6 offers microsecond timestamp support, as well as a lot of optimizer improvements. Even the standard MySQL 5.5…, better diagnostics, improvements to partitioning, and the addition of multiple InnoDB buffer pools. Best of all, MariaDB 5.5 might be…
Comment: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 22, 2012 By 10 Comments
… variation in InnoDB statistics. It is not much the Optimizer can do about that. In MySQL 5.6 you can use InnoDB Persistent Statistics… and plan stability. (See http://oysteing.blogspot.com/2011/05/innodb-persistent-statistics-save-day.html). You can also reduce the…
Comment: What does Using filesort mean in MySQL?
March 15, 2012 By 27 Comments
… looked at the “ORDER BY OPTIMIZATION” section in MySQL manual but could not find a reason why MySQL does not use index in…) DEFAULT NULL, PRIMARY KEY (id), KEY (a), KEY (b) ) ENGINE=InnoDB Table 1 has 512000 randomly generated rows. This query uses… note: With EXPLAIN SELECT … ORDER BY, you can check whether MySQL can use indexes to resolve the query. It cannot if…
Post: MySQL Optimizer and Innodb Primary Key
October 3, 2006 By 9 Comments
…`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; “myisam” is same table created with MyISAM storage engine used to show difference: MySQL Optimizer correctly knows Innodb tables… Extra: Using filesort 1 row in set (0.00 sec) MySQL Optimizer is also able to figure out every key also holds…,id) which means MySQL could skip filesort if ordering is done by primary key: mysql> explain select id from innodb where a=3…
Post: Guidance for MySQL Optimizer Developers
September 20, 2009 By 4 Comments
I spend large portion of my life working on MySQL Performance Optimization and so MySQL Optimizer is quite important to me. For probably last… – make optimizer pluggable and make it possible to stick to old optimizer behavior with new MySQL Version. Make Cost Model Adjustable MySQL Optimizer looks at… 100% in RAM (even for Disk tables such MyISAM or Innodb) others keep database on SSD which has completely different ratio…
