If you have only 1M hits a day, I suggest there are some optimizations
in your queries / indexes / etc, needed.
I work with a lightly loaded system (1 master, 12 slaves); the master
gets about 10M updates per day (363 queries/sec; 44% are SELECT). The
slaves are snoring most of the day; only about 40 selects/second.
Also prod them with ANALYZE TABLE -- sometimes query efficiency degrades
and this makes it better.
Do you have slowlog turned on? What are the main nasty queries? Maybe
we can look at them.
> -----Original Message-----
> From: Jed Reynolds [mailto:lists@stripped]
> Sent: Tuesday, February 26, 2008 10:19 AM
> To: Lasitha Alawatta
> Cc: replication@stripped
> Subject: Re: MySQL replication delaying issue
>
> Lasitha Alawatta wrote:
> >
> > Hello Everybody,
> >
> > We have 6 multi-master MySql instances within a LAN , that are
> > replicating is a sequential manner.
> >
> > Server Environment : Six identical Linux Enterprise version
> 4 running,
> > servers with having MySQL version 6.
> >
> > we are using "MySQL multi-master replication" method for database
> > replication.
> >
> > There is a delay (5-7 minutes) of that data replication process.
> >
> > We notice that it's because of MySQL table locking.
> >
>
>
> I have come across the locking problem before. It depends upon your
> application and how you want to segregate your data. I tried
> switching
> to InnoDB for my application but I did not have enough time
> or resources
> to find as performant a configuration, so I just had to switch back.
>
> For context, I'm running over 1M hits a day, and I have four servers
> with three mysqld instances each, running mostly MyISAM tables. I was
> having a lot of locking pressure on some of my main document
> tables, so
> I wrote a cron job to create some pre-sorted shadow tables
> and changed
> my site to do the bulk of it's queries to the shadow tables. The
> trouble-queries used to take about 30s and now they take
> closer to 1-3s.
> My technique here: a) create table Bcold like A; b) insert
> into B select
> * from A where ... order by...; c) rename table Bhot to
> Bexpired, Bcold
> to Bhot; d) drop table Bexpired. This added some replication
> lag spikes
> but improved my performance amazingly, since I never write to the B
> shadow tables. I added some replication-ignore-table statements to my
> config file, so these big table copies got ignored in
> replication, and
> just ran the cron job on each of the masters and slaves.
>
> The next replication lag issue I solved was with collection of
> statistics on my documents. I used to have all my statistics and
> documents in the same database instance. The flood of
> statistics update
> statements was serialized with all other updates. I moved my
> statistics
> tables into a different mysql instance. Now I'm only seeing
> occasional
> replication lag.
>
> It's been a lot of work. The icing on the cake has been incorporating
> memcached into my application, which has reduced a lot of the query
> pressure as well.
>
> Good luck.
>
> Jed
>
> --
> MySQL Replication Mailing List
> For list archives: http://lists.mysql.com/replication
> To unsubscribe:
> http://lists.mysql.com/replication?unsub=1
>
>
