InnoInfo

InnoInfo is a tool designed for two purposes:

Getting statistics information on innodb data files
Retrieving data from (corrupt) data files

InnoInfo is a totally seperate tool from MySQL and works directly on the InnoDB table space. InnoBase and Oracle have nothing to do with this tool, and you cannot get support from MySQL, Oracle or InnoBase for this tool. What you *can* do, is download the source, and try it out. However, neither Zarafa nor the author provide ANY WARRANTY on ANY PART of this software. If you trash your database, lose data, or simply stop breathing, this is not our fault.

Download

Download innoinfo 0.1 here

License

InnoInfo is distributed under the GPLv2 license.

Analyse

Analysing is done per table, and outputs this kind of information:

Total leaf pages: 3720 (60948480 bytes)
Sequential pages: 1099 (70.46% fragmented)
Free space in pages: 13071348 (78.38% full avg)
Records (Page count): 538922 (113 bytes effective per record avg)
Actual records: 538922 (should match page count records)
Garbage: 682870 bytes
Proximity: 1510.33 pages avg
B-tree levels (including leaf): 3
Blobs: 469
Blob pages: 593 (9715712 bytes)
Blob sequential pages: 124 (0.00% fragmented)
Blob data size: 6697337 (14280 bytes per blob avg)
Blob-to-Leaf ratio: 0.16
Total data size in records: 43635403
Total data size: 4313 pages (70664192 bytes)
Overhead: 61.94%

A quick explanation:

Total leaf pages: the total number of pages containing data in the table.
Sequential pages: the total number of pages whose logically preceding page was also physically the preceding page. (obviously 'physically' as far as the FS is concerned)
Free space in pages: the total number of bytes of free space in the pages
Records: This is the number of records as calculated from the record count on each page
Actual records: This is the number of records that innoinfo actually traversed on the pages
Garbage: Bytes used by deleted records in the pages
Proximity: This is the AVERAGE amount of pages that we had to seek from page to page while reading the entire table. If everything were unfragmented, this number should be 1. It is quite often much higher than you'd expect
B-tree levels: The amount of levels from the root node of the B-tree to the leaf pages. So, when doing a random read from the table, this is the maximum amount of pages we need to read to get a record
Blobs: number of records that contain BLOB information OUTSIDE the normal B-Tree. Normally InnoDB stores something like the first 768 bytes in the page, and the rest in special blob pages
Blob pages: number of pages used by blobs
Blob sequential pages: number of pages that are sequential on the disc. This obviously does not count the first page for each blob.
Blob data size: amount of data in blobs (the part that is in seperate blob pages, that is)
Blob-to-Leaf ration: number of blob pages compared to the number of leaf pages. A ratio of 0.5 means you have say 100 leaf pages, and 50 blob pages.
Total data size in records: the total actual size of data in the tables (ie, an int is 4 bytes, a double is 8 bytes, a string "hello" is 5 bytes, etc)
Total data size: the actual number of pages (and bytes) used to store the data
Overhead: percentage of (data size in records) that is used as overhead for storage. 25% overhead means for each 100 bytes of data, there is 25 bytes of overhead.

Dump

You can dump all data in an table directly with innoinfo with the -d option. This does basically what mysqldump would do, simply traversing all your data and dumping it into an SQL file. We do not dump the table defs through (only the INSERT INTO data).

If your table is broken, it may loop back on itself or crash in other strange ways. A mysqldump may only retrieve a small amount of data in that case. If you use the --linear option, this will allow you to (try to) retrieve more data; the linear mode scans the entire ibdata file, looking for pages that are part of the table you're trying to dump. If it finds a page, it dumps the data. This means that in most cases, you will get TOO MANY records; pages that were no longer really in use are often found with the linear option. So, you will never get a perfect dump, but that's still way better than losing 80% of all your data.

How does it work?

InnoInfo simply contains the code for reading 'ibdata' (normally ibdata1) files directly. It is therefore a good idea to close MySQL before using InnoInfo. What it can then do, is show you information on the tables within the file, like column types. It can also run through your entire data set and generate statistics like how many bytes are actually being used by you table, and fragmentation information

How do you know the output data is correct?

We don't. It seems to be pretty logical and correct in many cases though

What is unsupported?

Currently unsupported are:

Multiple-file tablespaces. You can only specify ONE ibdata file, and this must be the FIRST ibdata file
Various types of mangled data files. If you really have broken data, it can still segfault or loop on some cases.
We don't do any checking of the file-allocation structure within innodb. We just start at the beginning, and keep going.

I want to talk to someone!

Try e-mailing to me, although it may be in vain: s.hardy at zarafa dot com