On Mon, 2008-04-21 at 10:24 -0400, Neil Joseph Schelly wrote: > On Saturday 19 April 2008 07:44, Lloyd Kvam wrote: > > Was there any reason for not using the MySQL replication feature to copy > > from the writeable database to the read-only database? > > > > I rely on replication mostly for off-site backup, but I've read of > > people splitting databases as you have done for performance reasons. > > I imagine a workable solution could be made, but I've only setup replication > between multiple boxes, not multiple databases on the same box. I'm not even > sure that's a supported configuration for replication - not sure how you'd do > it.
I've done it by specifying host and port for the master. > And any real-time replication would probably face the same locking > problems. If you just restricted replication to play catchup at regular > intervals and then you'd still want to disable the Bayesian filtering at > those times before doing the replication, because it would hang up matters. The replication stream is handled at a low priority, but I am not going to argue against your decision. > > One problem I did run into was that the binlogs filled up fast. After only a > few hours of a full load of traffic, the binary logs had filled up several > gigabytes of space. The MySQL traffic is an overwhelmming percentage of > INSERT statements: > http://jenandneil.com/sites/jenandneil.com/files/sf00.dc0.oasis-open.net-mysql_queries-week.png > > In my original partitioning, this was especially a problem since those, the > databases themselves, the logs, the Exim queueing and spooling and tmp space, > etc were all on one partition for /var. I've broken it up some to get some > isolation, but it's still just one physical disk. The IO of keeping up a > binlog with everything else happening would result it more IO overhead than I > want to spend on replication. OK. Thanks very much for taking the time to explain. That is very convincing and enlightening. The IO is even worse than you outlined: binlog, relaylog, replicated table. > > I would venture that the computing effort required to replicate all the > queries that happen in a few hours time would be far more costly than the > couple-of-minutes spent re-duplicating the database. > -N -- Lloyd Kvam Venix Corp DLSLUG/GNHLUG library http://www.librarything.com/catalog/dlslug http://www.librarything.com/profile/dlslug http://www.librarything.com/rsshtml/recent/dlslug _______________________________________________ gnhlug-discuss mailing list gnhlug-discuss@mail.gnhlug.org http://mail.gnhlug.org/mailman/listinfo/gnhlug-discuss/