innodb_data_file_path bug with long line limits
I have a MySQL server which was starting to scrunch its data more and more slowly. Some analysis led me to blame an autoextending innodb file which had grown somewhat unkept to several GB. I wish the autoextend behaviour could be configured to grow more files rather than grow one file, but that’s another rant. I decided to clear it up which is a simple process :
- Stop the inflow of new data
- mysqldump the database to a .sql file
- Bin the junk innodb data files
- Edit my.cnf to create files of a spec that I wanted, start mysql
- Import the data.
I decided to create 500 data files of 100MB. When MySQL started, it ignored the config I had set in my.cnf and loaded the innodb defaults – 5MB log files and an ibdata file of 10MB which could autoextend.
It turns out that if the innodb_data_file_path line is too long then the innodb section is ignored and the defaults will prevail. I changed the plan to create 200 files of 250MB, which made for much smaller config, and the innodb settings loaded just fine.
If you want a perl one liner to generate the innodb_data_file_path, I used:
perl -e ‘$i=1; do { $padded = sprintf(“%03d”, $i); print “ibdata$padded:250M;”; $i++; } until ($i eq 200)’;
1 Comment
Comments
One Response to “innodb_data_file_path bug with long line limits”
Leave a Reply
You must be logged in to post a comment.
July 22nd, 2009 at 9:00 am
What is your view on using innodb_file_per_table to (as the name suggests) store the InnoDB data in one file per table?