April 18, 2014

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 […]

PERFORMANCE_SCHEMA vs Slow Query Log

A couple of weeks ago, shortly after Vadim wrote about Percona Cloud Tools and using Slow Query Log to capture the data, Mark Leith asked why don’t we just use Performance Schema instead? This is an interesting question and I think it deserves its own blog post to talk about. First, I would say main […]

How to find MySQL queries worth optimizing ?

One question I often get is how one can find out queries which should be optimized. By looking at pt-query-digest report it is easy to find slow queries or queries which cause the large portion of the load on the system but how do we know whenever there is any possibility to make this query […]

On Character Sets and Disappearing Tables

The MySQL manual tells us that regardless of whether or not we use “SET FOREIGN_KEY_CHECKS=0″ before making schema changes, InnoDB will not allow a column referenced by a foreign key constraint to be modified in such a way that the foreign key will reference a column with a mismatched data type. For instance, if we […]

Hijacking Innodb Foreign Keys

I guess I’m first to post in 2012 so Happy New Year all blog readers ! Now back to HardCore MySQL business – foreign Keys. MySQL supported Foreign Keys for Innodb for many years, yet rudimentary support initially added in MySQL 3.23.44 have not been improved in new releases as much as I’d like. We […]

Identifying the load with the help of pt-query-digest and Percona Server

Overview Profiling, analyzing and then fixing queries is likely the most oft-repeated part of a job of a DBA and one that keeps evolving, as new features are added to the application new queries pop up that need to be analyzed and fixed. And there are not too many tools out there that can make […]

When Does InnoDB Update Table Statistics? (And When It Can Bite)

An InnoDB table statistics is used for JOIN optimizations and helping the MySQL optimizer choose the appropriate index for a query. If a table’s statistics or index cardinality becomes outdated, you might see queries which previously performed well suddenly show up on slow query log until InnoDB again updates the statistics. But when does InnoDB […]

More on MySQL Error Messages

I wrote about MySQL Error Messages before and as you might guess I’m not very happy with quality for error messages it produces. Now I’m revisiting this subject with couple of more annoying examples I ran into during last couple of days.

Debugging problems with row based replication

MySQL 5.1 introduces row based binary logging. In fact, the default binary logging format in GA versions of MySQL 5.1 is ‘MIXED’ STATEMENT*;   The binlog_format  variable can still be changed per sessions which means it is possible that some of your binary log entries will be written in a row-based fashion instead of the […]

Recovering CREATE TABLE statement from .frm file

So lets say you have .frm file for the table and you need to recover CREATE TABLE statement for this table. In particular when we do Innodb Recovery we often get .frm files and some mess in the Innodb tablespace from which we have to get data from. Of course we could relay on old […]