MediaWiki Taking Too Much Space in MySQL Database
Much fun as having my own wiki is, MediaWiki is a beast that sometimes gives you headache.
Good on me I remembered to check how much disc space I had on my virtual server — the one which is hosting marteno.eu — as I was quite surprised to see that 17 GB was used up and only 3.8 GB free :-O
I checked here and there and then I thought how much data the individual databases took up in MySQL server? So I typed SELECT table_schema, SUM((data_length+index_length)/1024/1024) AS MiB FROM information_schema.tables GROUP BY table_schema on the MySQL command line just to see where we were.
And no kidding — the database with my wiki was taking up whopping 10 GB. A-ha!
I knew I had neglected one thing — after I installed my MediaWiki, I left it open for anyone to edit, which turned out useful once for a friend of mine to whom I was helping with some translation project and whom I didn't want to bother with having to register and log in, but this was subsequently freely misused by all kind of spam robots. I locked the main page against editing by anonymous users, and removed any links from the main page to other wiki pages, which stopped some, but not all of the robotic abuse of my wiki; but I didn't take any more serious measures to stop it.
What to do? As a quick fix, I tried to delete old revisions (as the spam robots had made a lot of edits). This is done with cd /var/www/html/wiki/maintenance; php deleteOldRevisions.php --delete
But alas, it seemed the script above bit more than the mySQL server could chew: so it was necessary to add
max_allowed_packet=200M innodb_buffer_pool_size=50Mto /etc/my.cnf then restart mysqld then run the above php script again (it doesn't need to be run as root, I ran it as a user membering in the apache group though).
So the revisions were successfully deleted :) only I managed to delete one short story I was in the middle of re-writing from memory that I forgot to back up and which had been overwritten by some malicious robots with some useless spam links :( but never mind.
Immediately I could see that the database with my wiki shrank to just 3 GB and later on (probably due to some self-maintenance of MySQL?) to just 0.5 GB.
Then I commented out the two above lines in /etc/my.cnf then restarted mysqld again so that it doesn't take up too much resources, until I need to do a similar mega-operation again.
Still it seems MySQL won't give up any disc space once taken (and going by what I read on the Internet, this is its normal behaviour).
So what I am going to have to do is to re-install MediaWiki and MySQL (not much stuff on the wiki that couldn't be easily backed up in a couple of txt files; and just one more database in MySQL which needs to have an update downloaded and imported anyway). As I have some more plans with my VPS so probably I will need the disc space.
Until then I have to figure out how to protect my wiki more efficiently — is allowing editions only to registered users (and subjecting all new users to approval — as the robots sometimes even register themselves as users!) the best way of doing it? It seems Wikipedia has a better way as it doesn't seem to be pestered by spam robots the way my wiki was, and it still allows editing to anonymous users.
Comments
No comments.