MySQL InnoDB Disk Space Consumption

InnoDB Folders and .frm Files

MySQL stores its data dictionary information for tables in .frm files in database directories.

Every InnoDB table also has its own entry in the InnoDB internal data dictionary inside the tablespace. When MySQL drops a table or a database, it has to delete one or more .frm files as well as the corresponding entries inside the InnoDB data dictionary.

http://dev.mysql.com/doc/refman/5.5/en/innodb-table-and-index.html

Employees Database Installation

We need a test database for testing. I decided to use the ‘employees’ database.

http://dev.mysql.com/doc/employee/en/index.html

The database contains about 300,000 employee records with 2.8 million salary entries. The export data is 167 MB, which is not huge, but heavy enough to be non-trivial for testing.

Login to remote host using ssh uwlab. Become root. Download the ‘employees’ test database. Inspect current disk usage of MySQL.

MySQL InnoDB Disk Usage

Employee Database tarball is of size 26M. MySQL main tablespace is about 18M (see ibdata1 file). Extract the database from tarball with tar -jxvf employees_db-full-1.0.6.tar.bz2.

The uncompressed database size is about 161M (use du -h -d 1)

Load the database dump into MySQL using mysql -u root -p -t < employees.sql.

Measue the MySQL data file size again.

Tablespace File Size Increase

After installing ‘employees’ test database, ‘ibdata1’ size increased from 18M to 218M. /var/lib/mysql/employees is only 80K.

InnoDB Per-Table .ibd Files

By default, all InnoDB tables and indexes are stored in the system tablespace. As an alternative, you can store each InnoDB table and its indexes in its own file.

First drop the ‘employee’ database using mysql -u root -p -e "drop database employees;". Add the following in your mysql configuration file.

’ (/etc/mysql/my.cnf)’

[mysqld]
innodb_file_per_table = 1

Restart the mysql server. You can verify the configuration using mysqld --help --verbose.

Now add the ‘employees’ database again using the above instructions. Now we have an ‘ibd’ file per table.

InnoDB IBD Per Table

Be sure to do a full sql dump before experimenting with the server using, mysqldump -u root -p --all-databases > /home/upeksha/full-dump.sql.

Note how the file size is changed. Now ‘ibdata1’ is only 18M. But the employees directory is 232M.

InnoDB Disk Space Distribution

MySQL Query Cache Size

The amount of memory allocated for caching query results. The default value is 0, which disables the query cache. Note that query_cache_size bytes of memory are allocated even if query_cache_type is set to 0.

http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_query_cache_size

MySQL Query Cache Effects