…to check which sessions have created which temporary tables (both in memory and not): mysql> select * from information_schema.global_temporary_tables…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… table we created earlier. This step is known as probe step. This join algorithm works best for highly selective …10; In-memory workload Now let’s see how effective are the join optimizations when the workload…set to 6M. MariaDB 5.5 is close, yet slower by ~10s. Another thing that is important to know…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…table: SELECT non_key_column FROM tbl WHERE key_column=x This query will roughly be evaluated in following steps, without MRR: SELECT…to MySQL 5.5 MRR clearly changes the access pattern to sequential, and hence InnoDB is able to do many read_aheads. Another thing to…
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
…the table‘s rows. For example: transaction1> START TRANSACTION; transaction1> SELECT * FROM … locked. Then, we try to insert another value on the second session:… data to other sessions. How to troubleshoot gap locks? Is possible to … so there is no need to create locks to prevent that from happening….
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
I have been 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 …
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…tables to InnoDB. (But then again, it might not be.) Or maybe the client is doing queries like “… WHERE client IN (SELECT…to the query or the table. We explain how to…
Post: A recovery trivia or how to recover from a lost ibdata1 file
June 3, 2011 By 7 Comments
…table recording the space ids of the tables mysql> show create table test.tablesG *************************** 1. row *************************** Table: tables Create Table: CREATE TABLE `tables…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
…Another way to say “aggregate” is to say “pre-compute.” If you have time-critical queries for your app to… have to think about how to avoid enormous tables that are hard to maintain…You start out with this: create table ads_by_day_by_blueness …with INSERT..SELECT queries, it will propagate to the …
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 3 Comments
…table row format from the Information Schema: mysql (information_schema) > SELECT ROW_FORMAT from TABLES WHERE TABLE_SCHEMA=’employees’ AND TABLE…tables and indexes (with their IDs) to the error log. mysql (employees) > CREATE TABLE innodb_table_monitor (id int) ENGINE=InnoDB; TABLE…
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
…to do what we want to do – Just add yet another table to the join so the info table just has bunch of ref lookups: mysql> select…
