Skip to content

Trying to reduce MySQL InnoDB disk usage after major reduction of data

So, two days ago, I tried to shrink my MediaWiki database and it almost worked, except the MySQL process wouldn’t shrink along with it.

Of course I tried all the obvious things such as dropping the database, stopping and restarting the process, followed by reloading the database. Optimizing tables, altering tables, all the obvious. But, to no avail, because there’s this bug. Well, technically it’s not a bug. Like most of MySQL, it has been “designed” to be counter-intuitive, non-standard, riddled with special cases, exceptions, work-arounds and duct tape. So, the “bug” is really just a feature request from dumb people much like myself who want stupid things like a database that becomes smaller in size when you delete most of your data.

I should really move to a virtual private server environment, where I can just run a real database (PostgreSQL), but I’m still on NFSN, whom (besides the sky-rocketing storage costs as of late) have given me no reason to complain so far.

I thought I’d ask them to help me out.

Recently, due to inattention to spam, one of my wiki databases has grown to well over 10 GiB. After finally getting around to removing most of this spam and tweaking some settings to reduce the table size from over 11Gig to a couple of MiB, I thought my Last Reported Size would go down accordingly.

But no such luck. Apparently it’s a MySQL issue, with the only solution being to drop the offending database (after a dump, of course), stop the MySQL process, remove the offending table, restart the process and then reload the database.

Instead, you could use the innodb_file_per_table option, which is enabled by default on MariaDB.

Without that option set, OPTIMIZE, ALTER TABLE and all that stuff will do nothing to reduce a table size. It’s one of those issues which the MySQL devs are really not interested in solving: http://bugs.mysql.com/bug.php?id=1341

I hope you can help me out with this, either by setting the innodb_file_per_table option or by removing all my database files. In the latter case, I’d hope you ask me to green light this first so that I can make some other data size reductions in various databases before I make a final backup.

But then I thought better of it, when I learned that—contrary to my expectations—the option really was enabled:

SHOW VARIABLES LIKE ‘innodb_file_per_table’;

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    | 
+-----------------------+-------+
1 row in set (0.00 sec)

So the option was enabled. I had to look elsewhere for an answer, which made me decide to do the following.

OPT="--host=myhost --user=myuser --password=mypassword --skip-column-names --batch hardwood";
mysql $OPT --execute="show tables" | grep -v mw_searchindex | while read tablename;
  do mysql $OPT --execute="alter table $tablename ENGINE=InnoDB; optimize table $tablename"
done

Needless to say, this didn’t help. So I wrote the support request for my hosting provider:

Recently, due to inattention to spam, one of my wiki databases has grown to well over 11 GiB. After finally getting around to removing most of this spam and tweaking some settings to reduce the table size of the most offending table from over 11Gig to a couple of MiB, I thought my Last Reported Size would go down accordingly.

Since you have the innodb_file_per_table option enabled (the default in MariaDB and confirmed by “SHOW VARIABLES LIKE 'innodb_file_per_table';”), I’d expect “ALTER TABLE mw_text ENGINE=InnoDB” to shrink the table (the command should move the table to its own file, even if it wasn’t already in its own file before the move to MariaDB). It didn’t. Last Reported Size is still approximately the same. Dropping and reloading the entire database didn’t do much to help either.

I suspect that the problem is that that the old shared file still exists and that this file won’t be deleted even if there are no more tables in it with the only solution then being to dump the database, drop it, remove all its files and then reload the dump.

Anyway, I’d like it to be so that my database will actually shrink when I delete stuff and the way I understand it this should actually be possible thanks to innodb_file_per_table. If it’s the same old story as without innodb_file_per_table, that would just be awful, because then I’d need your intervention every time that I’m trying to reduce my database process size.

I hope that you can somehow reload the database and remove the bloated
ibdata1 file.

Now, I’m just waiting patiently for their response while my funds whither…


    2 Comments ( Add comment / trackback )

    1. (permalink)
      Comment by Rowan Rodrik
      On October 24, 2012 at 20:20

      Jeff from NFSN recommended me to create a new MySQL process through their member interface and move all my databases over to get rid of all the legecay crap. He reminded me that my old process stems from 2005.

      I started importing mysqldump backups in my new process, while at the same time straightening out some long overdue privilege issues. It seemed to go fine with the English HardwoodWiki database, until I tried the Dutch HardhoutWiki database:

      Specified key was too long; max key length is 1000 bytes
      

      “Fuck this shit! I’ve seen this before. Let’s Google it…” The MediaWiki documentation said something about this error and pointed to BugZilla. Looking through the comments there, I noticed someone suggesting to use InnoDB instead of MyISAM. “But all the tables in my dump (except mw_searchindex) specify “ENGINE=InnoDB”. MySQL being the piece of absolute crap that it is, I decided to check it anyway to notice that, apparently, the newly created process didn’t have InnoDB enabled. Nothing wrong with that (InnoDB being more resource-intensive than MyISAM yet without delivering what you’d expect from a modern DBMS). But what is wrong with this is that it didn’t even fail silently. No, it decided that, in the absense of InnoDB support, me specifying the InnoDB engine must really mean that I want MyISAM tables. Fuck you, MySQL! No, really, fuck you! And fuck your persistent silent data corruption fetish too!

      In the meantime, Jeff enabled InnoDB, so now I can drop these databases and make another attempt at loading all this data.

    2. (permalink)
      Comment by Kev
      On July 16, 2014 at 09:56

      ” No, it decided that, in the absense of InnoDB support, me specifying the InnoDB engine must really mean that I want MyISAM table”

      See the mysql docs for ‘engine substitution’

      http://dev.mysql.com/doc/refman/5.1/en/storage-engine-setting.html

    Post a comment

    (required)
    (required)

    Your email is never published nor shared.

    (optional)
    Allowed HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>