| Best way to handle compressed (read only) tables in replication? [message #1034] |
Sat, 07 April 2007 06:55  |
 joemasters
Messages: 6
Registered: April 2007
Location: Asheville, NC USA |
Junior Member |
|
|
I currently have a database server with around 100+ databases on it. The number of databases grows on a weekly basis. Except for one control database, all of the databases contain only compressed (read only) MyISAM tables. Those databases are updated at most weekly (and most are updated every quarter).
My understanding is that, with replication, the compressed tables will end up as uncompressed MyISAM tables on the slaves, which isn't the worst thing in the world, but having them compressed gives me much better performance, and allows me to keep the varchar()s in the table definition (and have them vary from database to database) without performance hits (although I suppose I could just set all of the tables to have fixed-length rows, but the compressing would likely reduce the size of the tables over that).
I can imagine replicating only the single control database, but since I only want to write to the master version of that, the replication would only be for reporting and backup, which is a relatively small load on the database compared to the writes it takes.
So I've been thinking about just using scp to get the database directories from the master to the slaves, and probably using MySQL's replication to do the control database for the backup benefits only.
Any other ideas?
|
|
|
|
| Re: Best way to handle compressed (read only) tables in replication? [message #1049 is a reply to message #1040 ] |
Sun, 08 April 2007 18:57  |
joemasters
Messages: 6
Registered: April 2007
Location: Asheville, NC USA |
Junior Member |
|
|
The problem is that changes to the databases come by dropping and recreating the tables (via a mysqldump file generated off a test server) on the master. Then, the tables are compressed via myisampack, also on the master. So I'd assume that the replication would be able to handle the table dropping, creating, and inserting, but it wouldn't compress the tables, right?
In addition to running replication, I'd have to have some script on all of the slaves running to compress the tables that were uncompressed, right? But the only way I'd know that they were full and ready for compression would be to have some checksum somewhere (probably in a table on the master?)...
So all that makes me think that it would be faster to scp all of the binary MyISAM files after they're loaded (I can build it into the loading script from test to production)... Right?
|
|
|
| Re: Best way to handle compressed (read only) tables in replication? [message #1051 is a reply to message #1049 ] |
Sun, 08 April 2007 23:15 |
joemasters
Messages: 6
Registered: April 2007
Location: Asheville, NC USA |
Junior Member |
|
|
|
To add one thing to my comment above -- I don't want to use the ARCHIVE table type, because I need to have several columns indexed for fast retrieval.
|
|
|
Members
Search
Help
Register
Login
Home
).