List:Replication« Previous MessageNext Message »
From:Rick James Date:January 8 2013 6:19pm
Subject:RE: Replicating on multiple Slaves DB from one Master
View as plain text  
Make sure Galera will multi-thread multiple tables in the same db.  Else, that would be a waste of effort.

innodb_flush_method =O_DSYNC
--> O_DIRECT or O_ALL_DIRECT

You are saying that the disk is 30% busy on the Slave?  What is it on the Master?
But, 30% implies that you are about 30% of the max that it can handle.

70% of _one_ core -- sounds reasonable.

(Sorry; I don't have a link for Plan D.) 

SSD instead of 6x RAID-10 on slave -- this might be twice as fast.  It would be tricky (and costly) to get a big enough SSD for 800GB db.

Ponder application changes that would decrease the 1000+10000 qps.  Batching.  Saving updates (even for a second), then performing them in a batch.  More things in a BEGIN...COMMIT might help.  Etc.  (Without a feel for the queries you are performing, I can only be vague.)

> -----Original Message-----
> From: shayne.alone@stripped [mailto:shayne.alone@stripped]
> Sent: Tuesday, January 08, 2013 12:21 AM
> To: replication@stripped
> Subject: Re: Replicating on multiple Slaves DB from one Master
> 
> DEAR alex:
> 
> we have 144GB  memory
> it's a DUAL Xeon G7 HP
> 
> of will try PERCONA Again.( cos we Had a failed try one year ago with
> it
> :_) )
> 
> 
> On Tue, Jan 8, 2013 at 9:54 AM, Alex Yurchenko <
> alexey.yurchenko@stripped> wrote:
> 
> > Hi,
> >
> > I see that you are using InnoDB and it really seems like your slaves
> > are IO bound. In that case you may want to try Galera replication for
> > MySQL (e.g. Percona XtraDB Cluster) - it can do "parallel
> replication"
> > (which is, more precisely, parallel replication stream applying) even
> > on a single table. Plus it has other nice properties. A drawback is
> > that you can't just try it on a single slave, you'll have to migrate
> master too.
> >
> >
> > On 2013-01-08 07:23, shayne.alone@stripped wrote:
> >
> >> Dear Rick;
> >> thanks for you reply.
> >> I will try to explain what I did before, depend on your idea:
> >>
> >> I use xfs as file system to maximize the I/O performance on disk
> Disk
> >> are hardware raided with BBCW 2x 600GB (RAID 1) 15K
> >> ** operation system
> >> ** ralay logs
> >> 6x 300GB (RAID 10) 15K
> >> ** Mysql DATA (ibdata and tables data)
> >>
> >> I had been enabled file_per_table and partitioned the large tables
> to
> >> lookup faster
> >> innodb_flush_log_at_trx_commit = 0   ( to not to commit per
> transaction )
> >> innodb_flush_support_xa = 0  ( to not to sync file system and log
> >> files per transaction ) innodb_flush_method =O_DSYNC ( to import
> disk
> >> utilization ) and enabled the Large File large-pages cat
> >> /proc/meminfo | grep -i huge
> >> AnonHugePages:   6326272 kB
> >> HugePages_Total:   61440
> >> HugePages_Free:    59727
> >> HugePages_Rsvd:    51106
> >> HugePages_Surp:        0
> >> Hugepagesize:       2048 kB
> >> and set the innodb buffer pool to 100GB ( which is using huge block
> >> is memory as mentioned above)
> >>
> >
> > What is the total size of RAM on the slave? Is it swapping? You
> should
> > set innodb_buffer_pool_size to roughly 80-90% of RAM but make sure
> > that in no event it tries to swap.
> >
> >
> >  write now when I start the replication, disk utilization is finally
> > some
> >> about %30
> >> and just one of the 24Cores are under the load of about (70% :-S &&
> I
> >> don't know why it dose not read 100% load ( this core seem to be in
> >> use with SQL
> >> thread   )
> >> cos I have just one DB to be replication ( totally about 800GB ) the
> >> new multi thread capability dose not changed any things for me.
> >>
> >>
> >> regard to what I explained:
> >> Plan A is Done
> >> Plan B is Done
> >> Plan C is in progress
> >>   ** but I didn't still split tables is septate DB ( software team
> >> challenges :-) ) Pan D this is what I am reading some about
> yesterday
> >> and like to test
> >>
> >>
> >>
> >>
> >>
> >>
> >> On Tue, Jan 8, 2013 at 1:45 AM, Rick James <rjames@yahoo-inc.com>
> wrote:
> >>
> >>  Plan A:  Minimize I/O on Slaves:
> >>> innodb_flush_log_at_trx_commit = 2
> >>> sync_binlog = 0
> >>> innodb_doublewrite = OFF
> >>> (If a slave crashes, it may be best to reclone it rather than hope
> >>> that these performance settings did not lose data.)
> >>>
> >>> Plan B:  Faster Hardware on Slaves.
> >>>
> >>> Plan C:  Pave the way for 5.6:
> >>> Move tables to different databases.
> >>> Change the code to reference the tables thus:  dbname.tblname
> >>>
> >>> Plan D:  If the Slaves are I/O-bound:
> >>> Seems like there is a script (in Python?) that peeks in the relay
> >>> log, turns queries into SELECTs, does the SELECTs -- thereby
> priming
> >>> the buffer_pool using a separate thread.
> >>>
> >>> > -----Original Message-----
> >>> > From: shayne.alone@stripped [mailto:shayne.alone@gmail.com**]
> >>> > Sent: Monday, January 07, 2013 12:29 PM
> >>> > To: replication@stripped
> >>> > Subject: Replicating on multiple Slaves DB from one Master
> >>> >
> >>> > Dears;
> >>> >
> >>> > I have been faced with a case of replication which may most of
> you
> >>> > had been faced before...
> >>> > I did some checks and test to find out the ways to solve, but I'm
> >>> > not pretty sure about the cons and pron.
> >>> > problems is as fallow:
> >>> > Master:
> >>> > Single Mysql with about ~30K QPS, mainly just work as AAA data
> >>> > back end.
> >>> > not all but lots of these queries are writes (INSERT/UPDATE).
> >>> > the matter is that when the slave wants to execute Transactions
> >>> > with one thread! this will lead to a lots of replication delay...
> >>> >
> >>> > I'm looking for a way to rewrite DB user for queries depend on
> >>> > table name and not just rename whole of statements.
> >>> > is such away! i hope be able to use Multi thread replication
> >>> > ability of
> >>> > mysql5.6 for a single DB and multiple independent tables.
> >>> >
> >>> >
> >>> > --
> >>> > Regards,
> >>> > Ali R. Taleghani <a.taleghani@stripped>
> >>>
> >>>
> > --
> > Alexey Yurchenko,
> > Codership Oy, www.codership.com
> > Skype: alexey.yurchenko, Phone: +358-400-516-011
> >
> > --
> > MySQL Replication Mailing List
> > For list archives:
> http://lists.mysql.com/**replication<http://lists.mysql.com/replication
> >
> > To unsubscribe:
> http://lists.mysql.com/**replication<http://lists.mysql.com/replication
> >
> >
> >
> 
> 
> --
> Regards,
> Ali R. Taleghani
Thread
Replicating on multiple Slaves DB from one Mastershayne.alone@gmail.com7 Jan
  • RE: Replicating on multiple Slaves DB from one MasterRick James7 Jan
    • Re: Replicating on multiple Slaves DB from one Mastershayne.alone@gmail.com8 Jan
      • Re: Replicating on multiple Slaves DB from one MasterAlex Yurchenko8 Jan
        • Re: Replicating on multiple Slaves DB from one Mastershayne.alone@gmail.com8 Jan
          • RE: Replicating on multiple Slaves DB from one MasterRick James8 Jan
      • Re: Replicating on multiple Slaves DB from one MasterManuel Arostegui10 Jan
    • Re: Replicating on multiple Slaves DB from one MasterSimon J Mudd10 Jan