Spam and extra MX records + cool dual-db setup

[Neil Joseph Schelly]

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.  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.

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.

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