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.
I recommend against using this. Consider:
This would only be of some advantage if on two separate servers (no point in mixing both on same MySQL server, naturally, since reads & writes will take place on both)
Which means you would follow RolandoMySQLDBA's advice, and set up an InnoDB master with MyISAM slave.
Do you have foreign keys on your InnoDB tables? If so, you're into integrity issues. If you happen to have ON DELETE CASCADE
or ON DELETE SET NULL
- prepare for bad news -- the cascading will not propagate on the slave. See: Impact of foreign keys absence on replicating slaves
OK, so maybe it's just this one table, and no foreign keys. But then you get to inherit all of MyISAM's other disadvantages, such as the likely possibility of not being able to recover from crash. Transactions won't work; the slave may find itself trying to execute queries on a table that hasn't really recovered from a crash; contains incorrect data. You may run into replication failures.
Phil's comment is also very valid: by synchronizing the tables you actually mean any write to your InnoDB table must propagate to your MyISAM table. This means same amount of writing.
If you are willing to relax some constraints, such as only batching (somehow?) the writes to the slave every once in a while, then that makes somewhat more sense.
So, in general, while the solution could work, you would have a constant headache to take care of.
Best Answer
A few things to note:
XtraDB (which is InnoDB with a few patches from Percona) is a tried and tested technology, and XtraDB / InnoDB is the default storage engine in MySQL, Percona and MariaDB. InnoDB is being developed and supported by MySQL (owned by Oracle).
TokuDB is newer, it doesn't have as many years of usage and development. If I understand correctly, TokuDB is developed by Tokutek, now owned by Percona.
With these considerations, in a choice between these two technologies for a system for financial transaction data, I would probably go with XtraDB.