Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
    
Thanks everyone - It looks like even dropping indexes take more space than I have available. I will probably have to archive some tables, drop the whole thing, then modify the dump file so it doesn't create indexes. OPTIMIZE TABLE also requires more disk space than is available, though I don't expect it would shrink these tables that much. –  Seth Jul 7 '09 at 17:27

7 Answers 7

up vote 6 down vote accepted

Got any indexes you don't actually use?

share|improve this answer
1  
That's a great point - The indexes are taking up most of the space. Dropping them and re-creating the ones that are actually needed would certainly help. –  Seth Jul 6 '09 at 21:35
1  
Absolutally. Indexes are often 4-10x the size of the table data itself. –  Mark Henderson Jul 6 '09 at 21:40
    
Beware you will need 2x the total space of the table whilst it is rebuilding indices. –  staticsan Jul 6 '09 at 23:16

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:

/etc/init.d/mysql stop
rsync -avz /var/lib/mysql/ /mnt/anotherdisk/mysql/
mv /var/lib/mysql/ /var/lib/mysql_original/
ln -s /var/lib/mysql/ /mnt/anotherdisk/mysql/
/etc/init.d/mysql start

After one day/week/month/WhenIRememberOrCan, I do a backup of /var/lib/mysql_original/ and then remove the folder.

share|improve this answer

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).

Symbolic Links in MySQL

share|improve this answer

If most of your data is log aggregates, you basically have three options:

  1. Review your aggregation algorithm to see if it is designed to grow with the data, or designed to stay a fixed size.
  2. Acquire more storage.
  3. Stop storing logs in the database.
share|improve this answer

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.

share|improve this answer
    
Yeah. We don't really ever delete anything from the big tables, so OPTIMIZE doesn't help too much. –  Seth Jul 6 '09 at 21:05

if you don't mind your tables becoming read-only, you can use MyISAM PACK

http://dev.mysql.com/doc/refman/4.1/en/myisampack.html

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.