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.
Employees Database Installation
We need a test database for testing. I decided to use the ‘employees’ database.
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.
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.
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.
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.
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.
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.
Note, the first query took 939ms. But the identical subsequent queries took only 22ms and 19ms respectively.
More InnoDB Optimization
For tips on optimization, visit the following page,