Drupal 7 uses InnoDB tables. InnoDB provides many benefits, but can cause some unexpected headaches. One headache for me is that, by default, MySQL tells InnoDB to create one file on your system, called
ibdata1, to hold ALL the data from EVERY InnoDB table you have on your MySQL server. This file never shrinks in size; it only expands to contain new data. If you delete something from MySQL or drop a table, the space that table was using is reallocated for other new data. This isn't a bad thing, especially for those who have a lot of drive space, and not many databases that are altered or dropped quite frequently.
I develop a lot of sites on my little MacBook Air (with a 128GB SSD), so I often download database snapshots from live and testing environments, empty out the tables on my local environment, then import the database dumps. Can you spot the problem here?
Using Daisy Disk I just noticed that my
ibdata1 file had grown to more than 10 GB, and my Air's drive only had about 5 GB free space!
So, after reading through MySQL's InnoDB Engine documentation and this answer on Stack Overflow, I found that it's not too hard to change MySQL to keep data tables in their own files, and delete the files after the tables are deleted (thus saving me a ton of space). It just takes a little time and annoyance.
Here's how to do it, roughly:
- Export/dump all your databases. (In my case, I didn't do this, since I could just grab them all from production or development servers.) If you have a good backup and restoration system in place, you shouldn't need to fret too much about this part, but if you don't, you'll probably need to spend a bit of time dumping each database or writing a script to do this for you.
- Drop (delete) all databases, except for the mysql database, and information_schema, if it exists.
- Shut down MySQL.
- Delete the
ibdata1file and any
ib_logfilelog files (I just had
innodb_file_per_tableunder the [mysqld] heading in your my.cnf file.
- Start MySQL.
- Import all your databases.
After doing this, my 'mysql' directory with all my databases only took up about 3 GB (there are a few large databases I regularly work with... but +/-3 GB is a lot less painful than 10+ GB!
I also took this opportunity to flush out some other testing databases that I had on my local computer for Drupal 4.7 (really!), 5, 6, 7 and 8 testing. It's easy enough to create a new database when the need arises, and with drush, it's easier than ever to create and sync databases and files for my Drupal sites.
On most of the production servers I manage, I don't worry about setting innodb_file_per_table, because there are often only one, two or three databases, and they aren't constantly changing like on my local computer—they only grow over time, so the ever-increasing size of the ibdata1 file isn't concerning to me.