List:Replication« Previous MessageNext Message »
From:Rick James Date:February 26 2008 7:02pm
Subject:RE: MySQL replication delaying issue
View as plain text  
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
> 
> 
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