What happens when InnoDB hits its tablespace autoextend max? 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!
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.