I have read your post and I completely understand OPTIMIZE TABLE to perform in an environment where innodb_file_per_table is disabled, does not shrink the global ibdata1 tablespace.

But what if I need to perform index maintenance on InnoDB tables with ANALYZE command, it will grow the single tablespace also?

What other alternatives are there to increase performance or doing some maintenance in Innodb engine, when using a single tablespace and without grow out of control the single ibdata1.

Regards.
Matthew

ANALYZE TABLE will read index pages for a table, compute statistics, and store the results in INFORMATION_SCHEMA.STATISTICS. No writes to ibdata1 whatsoever.

Notwithstanding, anything DDL-related such as

ALTER TABLE ... ADD INDEX ...
ALTER TABLE ... DROP INDEX ...
ALTER TABLE ... MODIFY COLUMN ...
OPTIMIZE TABLE ...

performed against an InnoDB table or its indexes with innodb_file_per_table disabled will make ibdata1 mercilessly grow.

There are two things you could try to minimally control (or at least monitor) ibdata1’s growth

ALTERNATIVE #1 : Place a limit on ibdata1 on creation

Perhaps create a large ibdata1

ibdata1:innodb_data_file_path = ibdata1:100G

or set to large initial size and a larger max filesize

ibdata1:innodb_data_file_path = ibdata1:50G:autoextend:max:100G

ALTERNATIVE #2 : Use a Large Raw Data Device

According to MySQL 5.0 Certification Study Guide, Page 428

Any raw partitions in the configuration must exist but must have the modifier newraw listed after the size of the file specification. newraw tells InnoDB to initialize the partition when the server starts up. New partitions are treated as read-only after initialization. After InnoDB initializes the tablespace, stop the server change newraw to raw in the partition specfication, and restart the server. For example, to use a 10GB Unix partition named /dev/hdc6, begin with a configuration like this:

[mysqld]
innodb_data_home_dir=
innodb_data_file_path=/dev/hdc6:10Gnewraw

Start the server and let InnoDB initialize the tablespace. Then stop the server and change the configuration from newraw to raw:

[mysqld]
innodb_data_home_dir=
innodb_data_file_path=/dev/hdc6:10Graw

After changing the configuration, restart the server.

CAVEAT

With either alternate you stop worrying about growth until there is no more room. Otherwise, you must eventually deal with any imposed limits on ibdata1’s size.

• Apr 15, 2012 : What happens when InnoDB hits its tablespace autoextend max?
• Apr 11, 2012 : How do you remove fragmentation from InnoDB tables?