5

I'm quite new in database administration. The database architecture of my current company is master-slave replication. We are using MySQL version 5.0.86.

Some weeks ago, we upgraded the company's main app. This upgrade added a column to all the tables of its database. Before the upgrade, the size of our ibdata was ~3.1GiB. After the upgrade, it is ~5.2GiB.

Today, I've done a full backup-restoration test with mysqldump. After restoration, ibdata size is ~3.6 GiB. Standard selects show the same data in restoration as in original database.

I've checked the data size through a select to information_schema (CUSTODIA is the name of our main app):

select SUM(DATA_LENGTH+INDEX_LENGTH) from TABLES where TABLE_SCHEMA='CUSTODIA';

this is the result in master database:

+-------------------------------+
| SUM(DATA_LENGTH+INDEX_LENGTH) |
+-------------------------------+
|                    5683345068 |
+-------------------------------+

this is the result in restored database:

+-------------------------------+
| SUM(DATA_LENGTH+INDEX_LENGTH) |
+-------------------------------+
|                    3735748608 |
+-------------------------------+

My questions:

  1. Why is there this difference between original and restored database size?
  2. Is it safe to asume that restored database is OK, although this difference in size?
  3. How does MySQL calculate data_length? Is it an estimate?
  4. Can I safely reduce production's ibdata file size to 3.6GiB with no down-time?

Any thoughts about this will be kindly appreciated. Thank you.

2
  • This question gets +1 for introducing this series of questions in a single submission since the average data migration often produces this scenario. Jul 19, 2011 at 11:53
  • Added 2 comments to my answer. Please read. Jul 21, 2011 at 12:00

2 Answers 2

5

This makes all the sense in the world to me.

InnoDB creates data pages and index pages that are 16K each. If rows of data are being inserted, updated, deleted, committed, and rolled back, you are going to have FRAGMENTATION !!!

There are two cases where you can have internal fragmentation:

  • A single row could be written in multiple data pages because certain column values make a row too big to fit in the data page.
  • Having a TEXT column with 32K of data in it.

In those two cases, a single row spanning multiple data pages would have to be chained like a linked list. The internally generated list of data pages would always have to be navigated when the row is read.

Giving credit where credit is due, PostgreSQL implemented a very brilliant mechanism called TOAST (The Oversized-Attribute Storage Technique) to keep oversized data outside of tables to stem the tide of this kind of internal fragmentation.

Have used mysqldump to make a file with CREATE TABLE statements, followed by lots of INSERTs, you get a fresh table with no unused space along with contiguous data and index pages when loading the mysqldump into a new server.

For my explanantions, let's assume you have an InnoDB table in the CUSTODIA database called userinfo

If you would like to compress a table, you have three(3) options

Option 1

OPTIMIZE TABLE CUSTODIA.userinfo;

Option 2

ALTER TABLE CUSTODIA.userinfo ENGINE=InnoDB;

Option 3

CREATE TABLE CUSTODIA.userinfo2 LIKE CUSTODIA.userinfo;
INSERT INTO CUSTODIA.userinfo2 SELECT * FROM CUSTODIA.userinfo;
DROP TABLE CUSTODIA.userinfo;
ALTER TABLE CUSTODIA.userinfo2 RENAME CUSTODIA.userinfo;

CAVEAT : Option 3 is no good on a table with constraints. Option 3 is perfect for MyISAM.

Now for your questions:

Question 1. Why is there this difference between original and restored database size?

As explained above

Question 2. Is it safe to assume that restored database is OK, although this difference in size?

If you want to make absolutely sure that the data on both servers are identical, simply run this command on both DB servers:

CHECKSUM TABLE CUSTODIA.userinfo;

Hopefully, the checksum value is identical for the same table on both servers. If you have dozens, even hundreds, of tables, you may have to script it.

Question 3 : How does MySQL calculate data_length? Is it an estimate?

You are using the correct method in summing up the data_length and index_length. Based on my explanation of fragmentation, it is an estimate.

Question 4. Can I safely reduce production's ibdata file size to 3.6GiB with no down-time?

GOOD NEWS !!! You absolutely can compress it !!! In fact, how would like to compress it to a fraction of that number ??? Follow these two links because I addressed this issue in StackOverflow and ServerFault.

https://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#4056261

https://serverfault.com/questions/230551/mysql-innodb-innodb-file-per-table-cons/231400#231400

BAD NEWS !!! Sorry, but you will have a 3-5 minute window of downtime for rebuilding ib_logfile0 and ib_logfile1 as well shrinking ibdata1 once and for all. It's well worth it since it will be a one-time operation.

6
  • hello, thanks for your quick answer. Our database is already InnoDB. I've optimized the restored databased (mysqlcheck -o --all-databases) and its ibdata file has increased from 3.6GiB to 5.3GiB (the same size as the master's ibdata). Why does it expands ibdata? How can I test if mysqlcheck would remove unused space within production's ibdata without making any real change?
    – juanman80
    Jul 19, 2011 at 12:53
  • ibdata1 contains four types of info: 1) data pages, 2) index pages, 3) MultiVersion Concurrency (MVCC) Data, and 4) Table MetaData. Please read the links I supplied in the answer to Question 4. It tells you how to PERMANENTLY shrink ibdata1. It involves storing data and index pages OUTSIDE of ibdata1 using innodb_file_per_table. mysqlcheck will never shrink ibdata1. Jul 19, 2011 at 13:26
  • I understand that this method would shrink ibdata by spreading this data in different files, thus making the total amount equivalent to the size of the previous ibdata. Am I wrong?
    – juanman80
    Jul 21, 2011 at 8:56
  • Not only ibdata1 shrink, but the data will also be reduced significantly by 34.27 % ( 1 - (3735748608/5683345068) ) X 100. Jul 21, 2011 at 11:52
  • Also keep in mind that ibdata1 stores temp table space for MVCC and metadata for InnoDB table info. Those will always be written in ibdata1. Using Options 1 and 2 will shrink .ibd files once you cleanup ibdata1. If you never implement innodb_file_per_table, all three(3) options will make ibdata1 grow indefiinitely. So, please implement my answer to question 4 from the links I supplied and you will have better diskspace management for InnoDB. Jul 21, 2011 at 11:59
1

Q3 -- Are you asking about disk footprint? Or data size? The sizes are exact for how much disk space is used. There are overestimates of how big the data and index are -- because

  • 16KB blocks are rarely full

  • fields bigger than 767B are shunted off to other areas, that are allocated in 1MB units (or something like that)

  • Once a table is bigger than a trivial size, extents of 8MB are added, with some of that being left as 'free'.

  • INSERTing in PRIMARY KEY order will leave the data blocks reasonably densely packed. Otherwise, data and index blocks are at the whim of random inserts and block splits during any load process. Random inserting leads to blocks being about 69% full, simply because of the random nature of block splits.

  • InnoDB tries to delay index updates, thereby lessening the mess I just described. But if the table is much bigger than innodb_buffer_pool_size, there is a limit of how much of this optimization will happen.

Because of all the factors above, the reload may lead to a bigger disk footprint, or a smaller one.

Ever notice that a 1-row table has an avg_row_length of 16KB?

1
  • The application mainly does INSERTing in 3 tables: the first one is in PRIMARY KEY order, but the PRIMARY KEY of the other 2 is a HASH of the line inserted in the first table, so is almost never in correct order. So I bet there is a lot of fragmentation and poorly used blocks due to these tables... Isn't supposed an optimization tool to be designed for this scenario? I would say that an optimization tool should do it most in this case. Maybe it has defragmented ibdata, but it has increased the number of blocks used! (from 3.6GiB to 5.3GiB, in 16KiB blocks: something like 100,000 extra blocks).
    – juanman80
    Jul 21, 2011 at 9:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.