The InnoDB storage engine of MySQL has the drawback that log entries in the ibdata files aren't deleted automatically. This leads to ibdata files growing endlessly, even exceeding disk space. The only solution of this seems to be dumping and reloading the tables (shame on MySQL). Using per-tables ibdata files instead of one big ibdata file for the complete database seems to mititgate that problem. Because of that the option "innodb_file_per_table" should be added to the default my.cnf configuration file shipped with the MySQL ebuild. Reproducible: Always Steps to Reproduce: 1. Use InnoDB storage engine 2. Do a lot of changes in a database 3. check size of ibdata files Actual Results: ibdata file is growing and growing but never shrinks. When data exceeds disk space, MySQL stops working (when installed on the system partition system can even crash by exceeding disk space). Expected Results: MySQL should handle size of ibdata files correctly. This problem is known to the MySQL developers at least since version 4.1.1, but it seems to be a structural problem that won't be solved. So we can only mitigate the problem.
fixed in dev-db/mysql and dev-db/mysql-community.
it's not a bug, it's a "feature" :) anyway, it's actually due to how InnoDB works: http://dev.mysql.com/doc/refman/5.0/en/innodb-file-space.html to 'compact' the files you don't have to dump & reload but you can just do a "null alter table": http://dev.mysql.com/doc/refman/5.0/en/innodb-file-defragmenting.html this should /solve/ (rather than mitigate) the problem, but it's obviously for DB admins and not for ebuilds / distributions.
(In reply to comment #2) In my opinion a reliable database administration system has to keep data integrity itself, which includes handling available diskspace. That means MySQL should do such cleanups automagically! :( And yes, in my opinion a 40 MByte database growing to 1024 MByte in a month is a bug whatever the MySQL guys do to sell it as a "feature". So I suggest to keep the "innodb_file_per_table" and print a ebuild warning to use "ALTER TABLE tbl_name ENGINE=INNODB" regularly as many MySQL beginners will walk right into a trap.
(In reply to comment #3) > In my opinion a reliable database administration system has to keep data > integrity itself, which includes handling available diskspace. > That means MySQL should do such cleanups automagically! :( I'm not a DBA but looking in the sql world I came to the conclusion that normal people opinions are out of context there :) Seriously, this is more a MySQL issue than a gentoo/portage/ebuild one, so it's probably best managed on mysql.com. Also, data integrity isn't an issue here (it's not loosing or changing data), and db efficiency/optimization/taking_care_of is NOT an automagical thing, that's why DBAdmins exist and are paid for :) Of course I don't expect the db to optimize itself to 100% efficiency but I would expect that Average Joe running MySQL on its system doesn't incur too heavy space and/or time penalties. If you have 1GiB of files for 40MiB of data that's actually an issue. Did you try different mysql versions? If you have a test case (like an SQL file causing lots of wasted space in innodb files) I'll be glad to play with it and help testing. > And yes, in my opinion a 40 MByte database growing to 1024 MByte in a month is > a bug whatever the MySQL guys do to sell it as a "feature". I have few InnoDB tables but no one experience big updates so I never had your problem. Reading mysql's manual it talks about "50% to 100%" space efficiency for InnoDB trees, so I would guess file size should not grow above 200% actual (data+index) size. Or at least not _much_ above that! > So I suggest to keep the "innodb_file_per_table" and print a ebuild warning to > use "ALTER TABLE tbl_name ENGINE=INNODB" regularly as many MySQL beginners > will walk right into a trap. That's exactly what I meant. Keep that setting as default but definitely add a warning.
(In reply to comment #4) > If you have 1GiB of files for 40MiB of data > that's actually an issue. > Did you try different mysql versions? > I have few InnoDB tables but no one experience big updates so I never had your > problem. Reading mysql's manual it talks about "50% to 100%" space efficiency > for InnoDB trees, so I would guess file size should not grow above 200% actual > (data+index) size. Or at least not _much_ above that! I've done four or five updates with portage starting with a 4.x Version up to current 5.0.60-r1 (which now uses "innodb_file_per_table"). > If you have a test case (like an SQL file causing lots of wasted space in > innodb files) I'll be glad to play with it and help testing. I just use DBMail (per day basis): - incoming: 300 spam mails 200 mails from mailing lists 10 mails from individual users with attachments - outgoing: 10 mails with attachments Useless or read mails are deleted per day and finally erased from the database by cron.daily job. - dumps or cleaned up database files have a current maximum of 60 MByte (when I didn't use "innodb_file_per_table" there where about 40 MByte, but the CF-Disk with Gentoo got filled up to the brim with about 1024 MByte MySQL data). As you can see there are a lot of operations and I assume the attachments increased the log files/indices that much getting never deleted using the INNODB engine without user interaction.
(In reply to comment #5) > I just use DBMail (per day basis): I know this isn't related to this bug report, but why don't you use MyISAM tables for DBMail? Do you actually have a need for any of the InnoDB features? MyISAM is the default for a reason...
(In reply to comment #6) I'm moving from one database per task to one big database with table prefixes per task to share and manipulate data between tasks by triggers and procedures and replication of single tables (I assume MySQL won't support triggers, procedures and replication over several databases). The goal is to get all data to third normal form (correct translation?) and share e.g. address data between organizer, phone, CMS and webmail. But now back to the bug ;)
I'm with Luca here. Running a database yourself means that you are responsible for the DBA tasks. In the case of MySQL, Postgres or even Oracle, this means regularly running CHECK TABLE, OPTIMIZE TABLE, VACUUM (pg/ora) etc. There is no need for further comment on this bug. Your DBmail workload does lead to a lot of sparse pages in InnoDB, and I'd venture probably in Postgresql as well. Postgresql does the ability to have settings to run auto-vacuum stuff, but innodb doesn't have, so you get to do it yourself.
(In reply to comment #8) > Postgresql does the ability to have settings to run auto-vacuum stuff, > but innodb doesn't have, so you get to do it yourself. I don't see any reason to reinvent the wheel. We have had cron for a while.
(In reply to comment #9) > I don't see any reason to reinvent the wheel. We have had cron for a while. Yes, and I think I've found a good solution. Just add a cron.daily script with the following content: /usr/bin/mysqlcheck -h localhost -u root -p <password> --auto-repair --check --optimize --all-databases > /dev/null 2>&1 I've used a fixed <password> in the cron.daily script, but I'm sure there's a more secure way to handle this ... This could either be a ebuild warning or even a cron.daily script added at mysql installation automatically if there's a secure and reliable solution for the password. Renne
