… optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr=on’ optimizer_switch=’mrr_sort_keys=on’ optimizer_switch=’mrr_cost_based=off’ optimizer…15506 Innodb_pages_read 329115 355323 143808 358308 144798 144881 Innodb_rows_read 4127318 6718351 6611675 6707882 5479445 5527245 Select_scan…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
….5 FLUSH TABLES WITH READ LOCK does not work as optimally as you could think it works. Even though with general… means if you have workload which includes some very long SELECT queries you can be potentially waiting for hours for this… running SELECT queries to let backup to proceed, but resolving server gridlock one way or another. If you’re just using Innodb…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… were made in the MySQL config: optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr=on’ optimizer_switch=’mrr_sort_keys=on’ (only… 100551 103011 77213 Innodb_pages_read 120548 123868 100551 123592 100566 Innodb_rows_read 799239 914146 912318 914146 912318 Select_scan 1 1…
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: 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 …=InnoDB Table 1 has 512000 randomly generated rows. This query uses index (type: index, key: a, key_len: 103, Extra: ”): EXPLAIN SELECT… 1698, 1 This one uses filesort (Extra: Using filesort): EXPLAIN SELECT * FROM table1 ORDER BY a LIMIT 1699, 1 Can someone…
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
… is no histograms or any other skew metrics etc. MySQL optimizer also uses number of rows in the table for many… query execution for Innodb). This basic information means it does not change whole that quickly at extent to affect optimizer plans. If…. Now let us populate antest_innodb table which is same but uses Innodb format: mysql> insert into antest_innodb select * from antest; Query OK…
Post: Choosing innodb_buffer_pool_size
November 3, 2007 By 38 Comments
My last post about Innodb Performance Optimization got a lot of comments choosing proper innodb_buffer_pool_size and indeed I oversimplified things… you need to set innodb_flush_method=O_DIRECT. On other Operating Systems you may be able to select it on OS… for other things. P.S I only described Innodb Buffer Pool selection for dedicated Innodb system. If you have fair amount of MyISAM…
Post: Recovering Innodb table Corruption
July 4, 2008 By 19 Comments
… you simply rebuild table by using OPTIMIZE TABLE ? This is because Running in innodb_force_recovery mode Innodb becomes read only for data… can create or drop Innodb tables): mysql> optimize table test; +———–+———-+———-+———————————-+ | Table | Op | Msg_type | Msg_text | +———–+———-+———-+———————————-+ | test.test | optimize | error | Got error -1… in a way Innodb processes page data. The next comes trial and error approach: mysql> insert into test2 select * from test; ERROR…
Post: Tuning InnoDB Concurrency Tickets
May 24, 2010 By 4 Comments
… the table) – 1 As with any performance optimization effort, you will want to optimize for the common case. If you have a… patterns: single row primary-key lookups and SELECT statements that examine 900 rows? If innodb_concurrency_tickets is set to 500, then… is always one when a thread first enters InnoDB) while the 900-row SELECT statements will always be subject to one additional…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 9 Comments
… KEY `a` (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=6029313 DEFAULT CHARSET=latin1 mysql> select count(*) from idxitest where… | +—-+————-+———-+——-+—————+———+———+——+——+————-+ 1 row in set (0.00 sec) MySQL Optimizer considers using both (a) and (a,b)…
