Quality Work Make Your Dream Come True
Wordpress Buddypress bbPress developer

Search engine, Mysql, Ibdata1

Some months before I customized a search engine on my server and it works well, the problem is it “eat” each “G” on my VPS, last month I find a cheap server and move it to the new server, and then delete the database and busy for other things, today I decide to customize a new multi-user rss reader on my server because Google Reader was gone and new reader is too~~ooo slow and it can not get my fav rss, I am a heavy-customer of feeds, I need some keywords can be find out easier and can help me analyze the hidden rule in these amazing article so I can study more quickly, but when I installed my system on the VPS, I found the mysql is strange, I try to df -lh and du -a /var | sort -n -r | head -n 10, then I found a big guy — “Ibdata1” used my 80G, I vi /etc/my.cnf, added a line
innodb_file_per_table, then use mysqldump to dump all InnoDB tables, service mysqld stop, rm ib_logfile0, rm ib_logfile1, rm ib_logfile1, service mysqld start, reinstall my rss system, all things works well, and I can read my feeds quicker & easier now. 🙂 This is problem is because I use the InnoDB storage engine for my Search engine because its design follows the ACID model, with transactions featuring commit, rollback, crash-recovery,Row-level locking, but MyISAM do not support these features, but the trouble is the InnoDB design can not reclaim the space, I do not want to limit its size, so the only way is use innodb_file_per_table option and watching it per month and do some manually work to clean the Ibdata1 again, I think I need find a easier way to do this, it full of risk and it is waste my time, I always lost my sleeps and I want health, machine work for me, not I work for machine….

Leave a Reply