July 23, 2014

AUTO_INCREMENT and MERGE TABLES

How would you expect AUTO_INCREMENT to work with MERGE tables ? Assuming INSERT_METHOD=LAST is used I would expect it to work same as in case insertion happens to the last table… which does not seems to be the case. Alternatively I would expect AUTO_INCREMENT to be based off the maximum value across all tables, respecting […]

ANALYZE: MyISAM vs Innodb

Following up on my Previous Post I decided to do little test to see how accurate stats we can get for for Index Stats created by ANALYZE TABLE for MyISAM and Innodb. But before we go into that I wanted to highlight about using ANALYZE TABLE in production as some people seems to be thinking […]

Recovering Innodb table Corruption

Assume you’re running MySQL with Innodb tables and you’ve got crappy hardware, driver bug, kernel bug, unlucky power failure or some rare MySQL bug and some pages in Innodb tablespace got corrupted. In such cases Innodb will typically print something like this: InnoDB: Database page corruption on disk or a failed InnoDB: file read of […]

Can MySQL temporary tables be made safe for statement-based replication?

A while ago I wrote about how to make MySQL replication reliable, part of which is to eliminate temporary tables. The idea is this: if a slave is stopped (or crashed) while a temporary table is open and is then restarted, the temporary table doesn’t exist anymore, and the slave will have problems trying to […]

INFORMATION_SCHEMA tables in the InnoDB pluggable storage engine

Much has been written about the new InnoDB pluggable storage engine, which Innobase released at the MySQL conference last month. We’ve written posts ourselves about its fast index creation capabilities and the compressed row format, and how that affects performance. One of the nice things they added in this InnoDB release is INFORMATION_SCHEMA tables that […]

Quickly preloading Innodb tables in the buffer pool

In the previous post I mentioned a way I use to preload Clustered Index (data) for Innodb tables. Though I thought this topic would benefit from a bit more information. But lest first start with feature request for Innodb Team: All ways I mention here are hacks and they can’t be as efficient as native […]

Finding out largest tables on MySQL Server

Finding largest tables on MySQL instance is no brainier in MySQL 5.0+ thanks to Information Schema but I still wanted to post little query I use for the purpose so I can easily find it later, plus it is quite handy in a way it presents information:

Performance gotcha of MySQL memory tables

One performance gotcha with MEMORY tables you might know about comes from the fact it is the only MySQL storage engine which defaults to HASH index type by default, instead of BTREE which makes indexes unusable for prefix matches or range lookups. This is however not performance gotcha I’m going to write about. There is […]

Data Recovery Toolkit for InnoDB Version 0.1 Released

As Peter mentioned in one of previous posts, we’ve done huge work developing robust strategies of InnoDB data recovery to provide our customers effective data recovery services and one of major parts of these strategies is our toolkit for InnoDB data recovery. Today I’m proud to announce its first public release which was used to […]

Heikki Tuuri answers to Innodb questions, Part II

I now got answers to the second portions of the questions you asked Heikki. If you have not seen the first part it can be found here. Same as during last time I will provide my comments for some of the answers under PZ and will use HT for original Heikkis answer. Q26: You also […]