Spam and extra MX records + cool dual-db setup

[Lloyd Kvam]

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