SQL Server Magazine just tweeted about their latest article, a reader-submitted solution on how to shrink your database files with ease. To make matters worse, this particular article is by a Microsoft DBA and doesn’t include a single word about the problems involved with shrinking your database files. If you use Innodb engine for your MySQL database, you may have noticed that the /var/lib/mysql/ibdata1 file is the most important and heavy file of your MySQL server. Day by day, this file will increase and can quickly consume all your hard drive. Reducing its size can be a little bit tricky.
I have a 88.9 GB MySQL 5.0 database sitting on a 90GB drive. The database contains a number of MyISAM tables for our (custom) system usage reporting. The data is date-based aggregations of what are essentially web server logs.
I thought about converting the big tables to the MERGE storage engine. The idea being that I could move the older data to a different drive. However, I've never done this before, and am a bit nervous about testing on my production database.
And, of course, I'm under a time crunch. I have to process all of last month's reporting data real soon. So installing a larger drive is not an option at the moment.
Does anyone have some advice or experience to share on reducing the size of this database?
SethSeth
7 Answers
freiheitfreiheit
Ok... this post is OOOOLD .. but.. I think if is complete, is better
When I have to move a mysql database to another partition I do:
After one day/week/month/WhenIRememberOrCan, I do a backup of /var/lib/mysql_original/ and then remove the folder.
user319660user319660
If you're already considering moving the data to a larger partition, you can selectively move the larger tables and symlink them back into the database directory. There are some downsides to this, but they're noted in the documentation. This has the benefit of being easily backed out (assuming your tables still fit in the old partition).
Travis CampbellTravis Campbell
If most of your data is log aggregates, you basically have three options:
- Review your aggregation algorithm to see if it is designed to grow with the data, or designed to stay a fixed size.
- Acquire more storage.
- Stop storing logs in the database.
staticsanstaticsan
Are you prepared to rewrite the application?
OPTIMIZE TABLE will rebuild a table to remove 'holes'. This may save quite a lot of space, or none at all depending on how optimal they are already. This is however, very slow on a large table, AND USES QUITE A LOT OF TEMPORARY SPACE.
Dropping indexes, adding PACK_KEYS to the tables, these will reduce the size of the indexes, but again, this involves a rebuild AND USES TEMPORARY SPACE.
Have you looked at the size of the indexes vs. data?
Sounds like your server is so full you won't really be able to do any work.
SOLUTION: Use monitoring to ensure that in the future, your server never gets this full, as it's a lost cause by the time you reach this stage.
In the short term, transfer the whole lot off on to a bigger box.
MarkRMarkR
I'm guessing you've tried this already, but there's an OPTIMIZE TABLE command that can shrink them depending on how they've been used.
Tom RitterTom Ritter
if you don't mind your tables becoming read-only, you can use MyISAM PACK
user10103user10103