List:Replication« Previous MessageNext Message »
From:Jed Reynolds Date:February 26 2008 6:19pm
Subject:Re: MySQL replication delaying issue
View as plain text  
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
Thread
MySQL replication delaying issueLasitha Alawatta26 Feb
  • Re: MySQL replication delaying issueMarcus Bointon26 Feb
  • RE: MySQL replication delaying issueRick James26 Feb
    • Re: MySQL replication delaying issueMarcus Bointon26 Feb
      • Re: MySQL replication delaying issueJed Reynolds26 Feb
        • Re: MySQL replication delaying issueMarcus Bointon26 Feb
          • RE: MySQL replication delaying issueRick James26 Feb
            • Re: MySQL replication delaying issueJed Reynolds26 Feb
        • Re: MySQL replication delaying issueEric Bergen26 Feb
  • Re: MySQL replication delaying issueJed Reynolds26 Feb
    • RE: MySQL replication delaying issueRick James26 Feb
  • RE: MySQL replication delaying issueLasitha Alawatta27 Feb