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
CREATE TABLE `wall` ( `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`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='User profile wall' AUTO_INCREMENT=9710655 ; CREATE TABLE IF NOT EXISTS `chapter` ( `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`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Story content by chapter'; ALTER TABLE `chapter` ADD CONSTRAINT `chapter_ibfk_1` FOREIGN KEY (`id_story`) REFERENCES `story` (`id_story`) ON DELETE CASCADE ON UPDATE CASCADE;