MySQL Performance Blog

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 page 7.
InnoDB: You may have to recover from a backup.
080703 23:46:16 InnoDB: Page dump in ascii and hex (16384 bytes):
… A LOT OF HEX AND BINARY DATA…
080703 23:46:16 InnoDB: Page checksum 587461377, prior-to-4.0.14-form checksum 772331632
InnoDB: stored checksum 2287785129, prior-to-4.0.14-form stored checksum 772331632
InnoDB: Page lsn 24 1487506025, low 4 bytes of lsn at page end 1487506025
InnoDB: Page number (if stored to page already) 7,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 6353
InnoDB: Page may be an index page where index id is 0 25556
InnoDB: (index “PRIMARY” of table “test”.”test”)
InnoDB: Database page corruption on disk or a failed

and crash with assertion failure.
So what can you do to recover such a table ?
[read more...]

Recently I had a customer ask me about loading two huge files into InnoDB with LOAD DATA INFILE. The goal was to load this data on many servers without putting it into the binary log. While this is generally a fast way to load data (especially if you disable unique key checks and foreign key checks), I recommended against this. There are several problems with the very large transaction caused by the single statement. We didn’t want to split the file into pieces for the load for various reasons. However, I found a way to load the single file in chunks as though it were many small files, which avoided splitting the file and let us load with many transactions instead of one huge transaction.

[read more...]

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 show some status information about InnoDB. Here are the tables:

The _CMP tables show statistics about compression; they contain a lot of useful information about compression, decompression, memory management, fragmentation etc. Beware that selecting from the tables whose names contain RESET has a side effect: it resets the statistics back to 0.

There are also locks and transactions tables. A while ago, the InnoDB developers contacted me to ask my opinion about what would be useful to put in the INFORMATION_SCHEMA. I told them the single biggest thing I could not get from InnoDB at the time was visibility into which transactions are blocking others when there are lock waits. It appears that they agreed this was important to add. (I subsequently discovered that it is possible to find out more information on InnoDB locks even in the older versions of InnoDB, but it's not really easy.)

These tables are fully documented in the InnoDB plugin manual, along with extensive examples of how to use them to find out what is blocking what and so on. Note that the InnoDB plugin manual is being maintained on www.innodb.com, not as part of the regular MySQL manual.

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 support. It would be great if Innodb would implement command to preload table to Innodb buffer pool, which would simply go through .ibd file sequentially and inject pages in the buffer pool. This would make preload done using sequential file scan even if indexed suffered a lot of page splits.

Now lets continue to the hacks
[read more...]

Recently I was working with the customer who need quick warmup - to get Innodb table fetched in memory as fast as possible to get good in memory access performance.

To do it I run the query: "SELECT count(*) FROM tbl WHERE non_idx_col=0" I use this particular form of query because it will do full table scan - running count(*) without where clause may pick to scan some small index instead.

If your table is not fragmented one of two things should happen - either you should be reading at your hard drive sequential read rate or you would see MySQL becoming CPU bound if IO subsystem is too fast.
[read more...]

Let us talk few more about disks. You might have read my previous post and Matt's Reply and it looks like there are few more things to clarify and explain.

Before I get to main topic of the article lets comment on IO vs Disk question. If you look at Disk Based databases all data accesses are treated as IOs - it can be "logical" if they are cached or "phyiscal" if they require actual IO done but in the general sense all data accesses are IOs. If you use this terminology when most of the problems would come down to IO - making queries to touch fewer rows (or row portions) or having these "touches" resolved as logical IO rather than physical. There is still locking ,networking etc to deal with but it is minor story.

This is not however as Most of the people understand IO and as not as I typically use these terms. For me IO is IO bound workload - disks are moving and CPU sits idle. With such terminology there is instantly much smaller amount of cases are about IO because we would call cases when too much of logical IO is happening CPU bound. The beauty of this terminlogy (and so why I use it) - it is very easy to see if system is IO bound or CPU bound, while to understand if MySQL goes through more rows than it needs to requires look at the queries and schema.

Ok Let us new get to back to main point of the article.
[read more...]

Sometimes you have the task of storing multiple of boolean values (yes/now or something similar) in the table and if you get many columns and many rows you may want to store them as efficient way as possible.
For MyISAM tables you could use BIT(1) fields which get combined together for efficient storage:

[read more...]

In one of his recent posts Vadim already gave some information about possible benefits from using new InnoDB file format but in this post I'd like to share some real-life example how compression in InnoDB plugin could be useful for large warehousing tasks.

[read more...]

New features of InnoDB - compression format and fast index creation sound so promising so I spent some time to research time and sizes on data we have on our production. The schema of one of shards is

[read more...]

It seems there is interesting problem with compatibility of MySQL binaries and binaries of third-party plugins.

I personally found and there is confirmation from InnoDB team that current InnoDB-plugin binaries do not work with lastest 5.1.24-rc binaries. It was very charming move from MySQL side to release new incompatible binary on the second day after the announce of InnoDB plugin. I do not think it was intentional, but still looks funny and shows broken communication between teams.

The more interesting becomes from Sergei Golubchik presentation on MySQL Conference, where Sergei says that in current API "versioning binds a plugin binary to specific server release". That simply means that InnoDB has to release binary for each binary of MySQL. I suppose it should be changed, otherwise there will be total madness with versions - InnoDB 1.0.0 binary for MySQL 5.1.23, 5.1.24, ..., InnoDB 1.0.1 binary for MySQL 5.1.23, 5.1.24, 5.1.25... etc.

Even when that is fixed I am looking forward to have good mess with double versioning and problems like "
MySQL version 5.X.Y does not work with InnoDB version 1.N.M, you need to upgrade InnoDB to version 1.N1.M1 or MySQL to version 5.X1.Y1".