… the InnoDB table: SELECT…MySQL config: optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr=on’ optimizer_switch=’mrr_sort_keys=on’ (only on MariaDB 5.5) optimizer…performing badly. The interesting thing is that though both MariaDB and MySQL 5.6 are reporting high numbers for Innodb…
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… other possibilities like InnoDB doing read_ahead by noticing the sequential access pattern. BKA is available in both MySQL 5.6 and… With this optimization the idea of MRR is further extended to improve join performance. As I told you above, when table t1 would…
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: MariaDB 5.3 is released as GA!
March 5, 2012 By 17 Comments
… in an “optimizer standoff” talk at the upcoming MySQL conference, and …member of the MySQL family of databases. Finally, High Performance MySQL, Third … subqueries, and elimination of useless tables in joins Thread pooling …partitioning, and the addition of multiple InnoDB buffer pools. Best of all…
Post: When Does InnoDB Update Table Statistics? (And When It Can Bite)
October 6, 2011 By 5 Comments
… InnoDB table statistics is used for JOIN optimizations and helping the MySQL optimizer choose the appropriate index for a query. If a table‘s …InnoDB again updates the statistics. But when does InnoDB perform the updates aside from the first opening of the table or manually running ANALYZE TABLE…
Post: How to Monitor MySQL with Percona's Nagios Plugins
February 22, 2012 By 8 Comments
…mysql-innodb plugin checks for several indicators of severe internal problems within InnoDB…table-checksum tool from Percona Toolkit can generate table checksums, and the pmp-check-pt-table… server process, or poorly optimized queries, can consume … others that can be performed with existing plugins, …
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…optimization disabled. OPTIMIZE TABLE OPTIMIZE TABLE is mapped to ALTER TABLE … ENGINE=InnoDB for InnoDB tables and thus, is just a special case of the previous one: mysql…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…table_definitionsblogpercona.commanual Opened_tablesblogpercona.commanual optimizer_prune_levelblogpercona.commanual optimizer_search_depthblogpercona.commanual optimizer_switchblogpercona.commanual partitionblogpercona.commanual performance_schemablogpercona.commanual Performance…
Post: Tuning InnoDB Concurrency Tickets
May 24, 2010 By 4 Comments
… ALTER: (2 * rows in the table) – 1 As with any performance optimization effort, you will want to optimize for the common case. If you…/lib/mysql/mysql.sock run Applicable my.cnf settings: innodb_buffer_pool_size=24G innodb_data_file_path=ibdata1:10M:autoextend innodb_file_per_table=1 innodb…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… tables when things get big. And even if it’s read-only, there’s still another reason to use InnoDB/XtraDB tables… Optimize For I/O It is pretty much inevitable: if you do this kind of data processing in MySQL, you…, like star schemas. The problem is that MySQL doesn’t tend to perform well on a data warehousing workload. The…
