What happens when InnoDB hits its tablespace autoextend max?

Question

The latest version of High Performance MySQL (3rd ed.) recommends that one:

...place an upper limit on the tablespace's size to keep it from growing very large, because once it grows, it doesn't shrink.

They give the example of:

...ibdata3:1G:autoextend:max:2G

But it isn't clear to me what happens if your dataset keeps growing and hits this maximum. Unfortunately the MySQL docs aren't much help either.

Can someone shed some light on this? It seems like this advice is kind of dangerous on the face of it.

Thanks!

Answer 1

To answer the question what will happen, an error 1114 (ER_RECORD_FILE_FULL) will be generated when you try to insert new record. I've learned this by accident, while converting large MyISAM table to InnoDB, and there was a max setting left.

Answer 2

IMHO It is dangerous to place a max on an autoextend. A transaction's worth of data could be lost simply because there is no room to post any new data. I have seen this happen recently with a monitoring system whose ibdata2 has a max of 1024G imposed on it. All I did to fix it was bump up the max to 16384G (That was a band-aid until my employer will let me clean up its InnoDB layout [politics with outside vendors getting in the way])

My advice to you is to use the default path for the system tablespace file (a.k.a. ibdata1). The reason? This is the default innodb path

innodb_data_file_path = ibdata1:10M:autoextend

What does ibdata1 contain? Four(4) classes of info

• Table Data Pages
• Table Index Pages
• Table MetaData (List of TablespaceIDs + other related info)
• MVCC (to support Transaction Isolation and ACID compliance)

With innodb_file_per_table disabled by default, everything InnoDB and its grandmother lands in ibdata1. You can segregate the Table Data and Indexes Pages from ibdata1 by enabling innodb_file_per_table in /etc/my.cnf

[mysqld]
innodb_file_per_table

There is a way to shrink ibdata1 by means of reloading all MySQL Data. I have written a posts about this:

• How do you remove fragmentation from InnoDB tables?
• My original post in StackOverflow

Once you have performed this InnoDB Infrastructure Cleanup, you should never change the innodb_data_file_path. There is no need to create ibdata2, ibdata3, and so forth, when all Table Data and Index Pages live outside of ibdata1.