fred_zimmerman at September 30th, 2011 11:24 — #1
I have painfully discovered over the last month that mysql's innodb tables have a number of well-intentioned features for transaction recovery that produce many disk management headaches. specifically, innodb tables and logs tend to grow very fast for big data sets. there is an innodb option for my.cnf that dramatically eases disk management.
As far as I can tell, there would not be much of a downside to making this a default in bitnami mysql my.cnf, it is a common and recommended practice. It can be a real lifesaver when working with big data, because the only way to free up disk from an innodb file is to dump all your databases, delete them, and recreate them, which is both tedious and error-prone.
The gotcha is that this parameter can only be applied when a database is created, so the application databases created by bitnami stacks have to stay in single-tablespace mode unless you delete them and recreate them, which obviates the whole point of using the stacks.
kaysa at October 3rd, 2011 10:46 — #2
Thanks for your suggestion. This is something that we will consider once we upgrade to MySQL 5.5. There are indeed some downside of adding this feature as the default in a multi-database environment:
1.- It can have a bad impact in performance because when writing to multiple tables can't be combined into a single IO operation.
2.- MySQL doesn't free data when removed. So if you have set
innodb_file_per_table if rows or even the table is removed, MySQL doesn't free that disk space. However when using the system tablespace (when this setting is not set), MySQL can reuse that free space for other tables and database data. This is useful in BitNami when you can install/uninstall more than one application.
In any case it seems that in MySQL 5.5 there improvement in perfomance with InnoDB although it may have incompatibility with MySQL 5.1 databases. We will need to consider this carefully befero decide if add this setting of not. Also notice that this parameter can only be applied when a new table is created (not a database).
Thanks again for your feedback!
fred_zimmerman at October 3rd, 2011 10:53 — #3
Fair enough. Worth thinking about I guess. The way MySQL failes to free data seems incredibly stupid at first glance. I suppose there are some good reasons behind it.