ANALYZE TABLE will read index pages for a table, compute statistics, and store the results in
INFORMATION_SCHEMA.STATISTICS. No writes to
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
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:
Start the server and let InnoDB initialize the tablespace. Then stop the server and change the configuration from
After changing the configuration, restart the server.
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.