Smokes your problems, coughs fresh air.

Tag: InnoDB

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 | tablename;
  mysql $OPT --execute="alter table $tablename ENGINE=InnoDB; optimize table $tablename"

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…

Converting all tables in MySQL DB to InnoDB

#!/bin/bash
 
exit 1
 
dbname="eorder"
 
echo 'SHOW TABLES;'  | mysql $dbname  | awk '!/^Tables_in_/ {print "ALTER TABLE `"$0"` ENGINE = InnoDB;"}'  | column -t 
echo 'SHOW TABLES;'  | mysql $dbname  | awk '!/^Tables_in_/ {print "ALTER TABLE `"$0"` ENGINE = InnoDB;"}'  | column -t | mysql $dbname

MediaWiki problems with MySQL VARBINARY padding

I have been wanting to protect a certain page on my Dutch 15Monkeys wiki for weeks. I could install the ReCaptcha extension already (the best Captcha there is), but it’s just that one page that’s being spammed. So far, I’ve been unsuccessful in regaining my Sysop right for that wiki. I need these rights if I’m going to protect these pages.

In the previous post about this problem, I reported how there appeared to be padding problem caused by a MySQL upgrade. Fixing the padding didn’t solve the problem, however. If I look at the user list in MediaWiki, I notice that MediaWiki doesn’t seem to like it that MySQL returns all the \0x0 padding characters on SELECT:

15Monkeys MediaWiki NL user list with unrecognized characters

15Monkeys MediaWiki NL user list with unrecognized characters

The same problem in the EN wiki

The same problem in the EN wiki

BigSmoke ‎(bureaucrat������, sysop�����������)

Repair was suggested by the check operation in phpMyAdmin. (CHECK TABLE `mw_nl_user_groups`) I couldn’t find a repair operation (only OPTIMIZE), so I tried that and I was told that it went ok. In the database structure view, the repair option was available ; when I tried to repair all the tables belonging to this installation, I was told for this particular table (and one other) “The storage engine for the table doesn’t support repair”. I found out that the reason this error didn’t appear the first time because it appears quite randomly. Sometimes it just silently fails in true MySQL style.

Googling for the error message produced the advice to dump and recreate the table. Then, when recreating the table from my backup didn’t work, I found out that REPAIR really isn’t supported for InnoDB. Only CHECK is. If you want to repair an InnoDB table, you use OPTIMIZE or you have to restart mysqld with the innodb_force_recovery option enabled.

Then, it turned out that I misinterpreted the MySQL documentation for the VARBINARY type, although I still can’t figure out how I am supposed to deduce this from said documentation. Anyway, if I UPDATE the rows, all I can achieve is that whitespace is converted to \0 characters. But, if I reinsert them without the padding, it does work. 😕 Go figure…

© 2024 BigSmoke

Theme by Anders NorenUp ↑