The way to tune InnoDB is centered around
- InnoDB Buffer Pool : It caches data pages and index pages. The amount of data and index you can cache is not a function of disk space constraints but a function of available memory and diskspace currently used by InnoDB.
- InnoDB MetaData : By default, the file ibdata1 normally houses anything and everything InnoDB. That would include data pages, index pages, table metadata, MVCC data.
Here is a formula I have used for the past 5 years to compute the InnoDB Buffer Pool based on diskspace used by InnoDB data and index pages:
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,(SELECT 2 Power1024) B;
- Use (SELECT 0 Power1024) for Bytes
- Use (SELECT 1 Power1024) for KB
- Use (SELECT 2 Power1024) for MB
- Use (SELECT 3 Power1024) for GB
- Use (SELECT 4 Power1024) for TB (Email Me if you have TerraBytes of RAM)
Of course, I said a function of available memory and diskspace currently used by InnoDB. From here, just use common sense. The recommended number from the above query SHOULD NOT EXCEED 75% OF INSTALLED RAM !!! That's the simplest rule-of-thumb for sizing the InnoDB Buffer Pool.
You should also set innodb_flush_method to O_DIRECT since it will provided stable synchronous writes of InnoDB. I have also written a post on how to optimize Disk Storage for InnoDB.
With regard to the message Table does not support optimize, doing recreate + analyze instead, the reason why you get that error message is the fact that the storage engine is InnoDB. Mechanically, OPTIMIZE TABLE just copies the table to a temp table and performs ANALYZE TABLE.
In reality, ANALYZE TABLE against InnoDB is completely useless. Even if you ran ANALYZE TABLE on an InnoDB table, the InnoDB storage engine performs dives into the index for cardinality approximations over and over again, thus trashing the statistics you just compiled. In fact, Percona performed some tests on ANALYZE TABLE and came to that same conclusion as well.
Here are other posts I have made over the year about InnoDB Tuning