Is it me, or is working with InnoDB tablespaces akin to working with Oracle 6.0?
For example, today somebody filled up a filesystem by unintentially loading a large amount of data. It was development, so this stuff happens. Since it was development, I setup the InnoDB tablespace to start with size 10M and autoextend to whatever free space was available. The one datafile kept autoextending until it completely filled the disk. The developer dropped the table and thought that would free up the space.
I knew that wouldn't be the case, so I had to dump all the data, recreate the tablespace, and reload all my data. Pretty standard DBA stuff so far.
"This time", I thought to myself, " I'm going to create a couple files each 10m and let them autoextend to 10240m so this doesn't happen again".
Speedbump #1: InnoDB doesn't let you autoextend more than one file.
OK, I know my data is about 30G and we'll be doing some development, so I'll create three 10240m files and let the last one autoextend to 10240m. After 20 minutes of combing through the manuals trying to find a "DROP TABLESPACE" or similar command, I finally deduce that you can't drop a tablespace while the server is up.
Speedbump #2: The server (or instance for you Oracle people) has to be down to remove the tablespace.
So I shutdown the server, remove my datafiles per the docs, setup my config file, and restarted the server. The logfile spits out that it's pre-creating the files (as expected) but then throws an error message:
Error: All logfiles must be created at the same time.
Speedbump #3: You have to recreate the logfiles when you recreate the tablespace.
I don't want to recreate 30G of space, so I shutdown my server, remove the logfiles, and restart the server. Except this time I get another error:
InnoDB: Cannot initialize created log files because
InnoDB: data files are corrupt, or new data files were
InnoDB: created when the database was started previous
InnoDB: time but the database was not shutdown
InnoDB: normally after that.
Speedbump #4: You have to recreate your logfiles and datafiles at the same time.
It's not out of the realm of possibility that I'm doing something totally wrong or I don't understand InnoDB very well. Maybe my version (5.0.27) doesn't support these features but they are in a newer version. Or maybe it's just a hassle. I'd be interested in other's practical experiences with managing the InnoDB tablespace.
Powered by Zoundry
5 comments:
Jeff,
you should consider using the
innodb_file_per_table
option in my.cnf, so that DROP TABLE frees the disk space occupied by a table to the operating system. Then the ibdata files of the system tablespace normally never grow bigger than a few hundred megabytes.
The way to remove a whole InnoDB installation is explained at the end of web page:
http://dev.mysql.com/doc/refman/5.0/en/error-creating-innodb.html
Best regards,
Heikki
Innobase Oy
I looked at innodb_file_per_table, but thought that restricting myself to a single directory for all my data files might be a performance determent later on. However, in the days of SAME (Stripe and Mirror Everything), maybe I need to reconsider.
Jeff,
if you work on Linux/Unix, you can place individual MySQL database directories to separate drives by symlinking the database directory.
You could also symlink individual .ibd files, but I do not recommend that, because an ALTER TABLE will rebuild the table and return the file physically back to the database directory!
Regards,
Heikki
Innobase Oy
What's InnoDB is this some variant of MySQL?
Never heard of it before sorry.
Had the same problem. Reinstalled many times & was confused. The solution was to free up disk space!
Post a Comment