… the hash table fits in memory. You can read more about the hash join algorithm here. Now let’s move on to… and ran it on TPC-H dataset (InnoDB tables) with a Scale Factor of 2 (InnoDB dataset size ~5G). Note that query cache… 2.2G Innodb_data_reads 329115 355323 143808 335526 16164 15506 Innodb_pages_read 329115 355323 143808 358308 144798 144881 Innodb_rows_read…
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….askmonty.org/en/multi-range-read-optimization Now let’s move on to the benchmarks, to see the difference in numbers… dataset (InnoDB tables) with a Scale Factor of 2 (InnoDB dataset size ~5G). I did not use Scale Factor of 40 (InnoDB dataset size…
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: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
…global_temporary_tables \G *************************** 1. row *************************** SESSION_ID: 7234 TABLE_SCHEMA: test TABLE_NAME: my ENGINE: InnoDB NAME: #sql516_1c42_2 TABLE_ROWS:… is really not the total any more as Innodb has moved allocating memory from operation system directly not from …
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 …improvements to partitioning, and the addition of multiple InnoDB buffer pools. Best of all, MariaDB 5… characteristics, and feature and performance improvements are moving rapidly forward for the MySQL world …
Post: Impossible - possible, moving InnoDB tables between servers
June 8, 2009 By 9 Comments
…, but QA still needs to have some tables in fresh status. – resharding databases, moving some shards from one server to another. So… as with MyISAM tables when you just copy table.frm, table.MYD, table.MYI files, but so nice here – just can copy InnoDB tables in fully… should have XtraDB with innodb_expand_import extension and with innodb_expand_import=1 setting) 4) run alter table IMPORTEDTABLE discard tablespace; 5…
Post: Copying InnoDB tables between servers
July 31, 2009 By 10 Comments
… ago http://www.mysqlperformanceblog.com/2009/06/08/impossible-possible-moving-innodb-tables-between-servers/ is now available in our latest releases of… innodb_expand_import=1; to put XtraDB into extended import mode, and now we need to create empty table with the same table…: alter table order_line import tablespace; now some magic happens and you can see progress of import in error.log InnoDB: import…
Post: How to recover a single InnoDB table from a Full Backup
January 25, 2012 By 7 Comments
… if your tables are InnoDB the process is a little bit different story. With Oracle’s stock MySQL you cannot move your ibd… alter the schema of the table after the backup has been taken. The variable innodb_file_per_table must be enabled. Then, our… again! Conclusion: As we learned , you can also recover single InnoDB table as with MyISAM but knowing in advance that there are…
Post: Few more ideas for InnoDB features
June 29, 2009 By 7 Comments
…-for-next-innodb-features/ (and one of them – moving InnoDB tables between servers are currently under development), we have few mores: – Stick some InnoDB tables / indexes in buffer pool, or set priority for InnoDB tables…
Post: Should you move from MyISAM to Innodb ?
January 12, 2009 By 53 Comments
… for them move until it is worked around. Operations What is good for MyISAM kills Innodb, such as copying binary tables between the… MyISAM but instantly break upon upgrading to Innodb. Features The MyISAM features which forbid moving to Innodb are typically Full Text Search and… it can be as much as 10-50 times for Innodb tables in particular for write intensive workloads. Check here for details…
