Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Problem: I have two fairly large tables. A 'wall' table for messages between users with 9 million rows at 2 GB and a 'chapter' table at 2 million rows and 18 GB. I want to keep the number of active rows for the 'wall' table small while I want to diminish the size of the chapter table. I made the mistake of not compressing text data to begin with and I'd like to start compressing data in the archives.

For the 'wall' table, I'm thinking that everything older than a certain wall id will be transferred and compressed to a 'wall_archive'. Anyone wanting to view older posts will just be given a "view archive" link where older post queries use the archive table. Then I run a cron job to do this every now and then and the last wall id archived will be stored somewhere for reference. Am I going the right direction here?

I'm not so sure how to keep the 'chapter' table manageable. Perhaps it's less archiving and more needing to partition the table (or both). But what's the best way to do this? I was thinking of splitting 'story' IDs into odds and evens and dividing the chapter into two tables but I'll run into the same problem again down the road. Or I can archive stories modified before a certain date. Or before a certain story ID. Any suggestions for a scalable solution?

Lastly, how should I go about compressing text data? Should I use PHP's gzcompress function at level 9 to store text data into a BLOB column then gzuncompress the data on retrieval? Or should I use MySql's COMPRESS/UNCOMPRESS functions? I'm leaning towards using PHP in case I separate the web server(s) from the DB server where I can have PHP do the compression processes instead of the more valuable DB server but I'd like to know what best practices are.

Considerations: I'll still need to be able to access old 'chapter' data easily. 'wall' data can be put into slower storage if needed but it isn't necessary at the moment.

Environment: 6 Core AMD Opteron, 16 GB RAM, 256 GB SSD for MySql, Percona Server 5.5, Apache, CentOS 6, PHP 5.3, innodb_file_per_table is enabled, database and webserver is running on same machine, total database size is 30 GB, all tables are InnoDB


  `id_wall` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_author` int(10) unsigned NOT NULL 
     COMMENT 'wall''s owner',
  `id_user` int(10) unsigned NOT NULL DEFAULT '0'
     COMMENT 'user that wrote the comment',
  `comment` text NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`id_wall`),
  KEY `id_user` (`id_user`),
  KEY `id_author` (`id_author`)
  COMMENT='User profile wall'
  AUTO_INCREMENT=9710655 ;

  `id_story` int(11) unsigned NOT NULL,
  `id_chapter` int(11) unsigned NOT NULL DEFAULT '0',
  `title` varchar(255) NOT NULL,
  `main_image` varchar(2047) DEFAULT NULL,
  `body` mediumtext,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  `is_not_shown` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id_story`,`id_chapter`)
  COMMENT='Story content by chapter';

ALTER TABLE `chapter`
  ADD CONSTRAINT `chapter_ibfk_1` 
    FOREIGN KEY (`id_story`) 
    REFERENCES `story` (`id_story`) 
share|improve this question
Do you expect your data to grow fast? A couple of GB is not that much nowadays. If the size is no real problem simply do not compress at all. – mdo Jul 22 '12 at 11:58
It's actually the 'chapter' table size that I'd like to reduce since it's getting harder to make schema changes. I'm also making a move to Intel 710 SSDs which my host only has in a 100 GB option so it's also a space consideration. – Nichiren Jul 22 '12 at 13:02
Seems like you would be through making schema changes by now. Consider making parallel tables if you must add columns. – Rick James Jul 24 '12 at 0:05

2 Answers 2

up vote 1 down vote accepted

For compressing...

Do it in the client; that will lead to less traffic between client and server. (OK, they are on the same machine, so this is not much of an issue.)

Use PHP's gzcompress, gzuncomress; don't worry about compression level. Expect about 3:1 compression for regular text.

Yes, the MEDIUMTEXT would need to be MEDIUMBLOB.

Don't "archive" old data; you have not justified the need for it (yet). Caching will generally take care of making 'recent' chapters faster.

Check out Facebook and Percona for "online alters".

innodb_buffer_pool_size should be about 70% of available ram.

share|improve this answer
It's been three years since this question and I pretty much went with almost all of your suggestions with things looking pretty good so far. My architecture looks very different now than it did back then with multiple webservers behind a load balancer separate from the main database server. Out of curiosity, would you still recommend the PHP gzcompress/gzuncompress route instead of Barracuda's native compression if I had asked this question today? – Nichiren Sep 30 at 19:04
It is reasonable to re-ask the question. I still stand by my answer. Several minor pros and cons (all in favor of compressing in client): InnoDB's compression is somewhat clumsy the way it copies things around. If you have the mysqld on one server and the clients on other server(s), client compression offloads the CPU of the mysql server. InnoDB claims 2x compression; gzip (and most compression techniques of today) get about 3x for typical "text". Switching to Barracuda + COMPRESSED is a multi-step process. – Rick James Oct 10 at 0:33
Two other compression techniques have become more popular in the last 3 years... FusionIO (Flash on a PCI card) does a good job of compression at that low level, plus obviates the "double write". TokuDB (an engine in MariaDB) does compression at a low level and claims 10x. (I suspect there is a big case of "YMMV".) I see either as better than InnoDB's. – Rick James Oct 10 at 0:37


I would develop analytics that tell me how frequently older data is accessed and by what date intervals. If very few people look at anything older than 1 month, your options are numerous. If it's all over the board, that will be a different strategy.

Let's pretend that content older than 1 month is only accessed occasionally, say < 2% of the requests. Two options available to you include: partitioning and horizontal sharding, both by date. With partitioning, you simply partition by RANGE using the 'created' field in your table. With horizontal sharding you do the same thing, except using a cron job or event to create a new periodic version of your table, then migrate rows to the periodic table. With horizontal sharding, you could use UNION statements to span multiple tables.

Partitioning will have little or no effect on your data model. Sharding will require data model intelligence.

share|improve this answer

Your Answer


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.