List:General Discussion« Previous MessageNext Message »
From:Matt Sturtz Date:December 31 2002 9:30pm
Subject:Re: Replication priority / speed
View as plain text  
>>>> Or, alternatly, is there a way to limit the slave thread to only "X"
>>>> bin-log transactions per second?
>>> There is not.
>> Any plan to add this feature?  I would think it'd be useful...
> Wouldn't it be better to *solve* your problem instead of going
> around it?

Well, as this is how our software is designed, I'm going at solving it as
best I can...  The problem, as I see it, is the MySQL slaves consume the
system (load average goes very high-- most likely I/O bound as someone
else said) when there's a lot of updates to do at once.  Therefor, I'm
looking to MySQL resources to help solve the problem.

>> MyTOP says our key efficiency is 97.35%, with an average of 1.24 q/sec
>> (on the master--  most queries are done directly on the slave, with
>> only updates happening on the master).  We've optimized things as best
>> we can.
> 1.24 q/sec doesn't sound loaded at all.

Nope, it's not loaded up much at all, except when somebody makes a huge
change or bulk-load (not terrably often, but expected to be more)...

> How many queries/second do you see on your slave box?

13.75, according to MyTOP...

> How many slow queries per hour?

MyTOP says this particular slave has been up 47 days, 2 hours...  It's had
56M queries, of those 4,559 were "slow"...  Not being a MySQL expert, I'm
not sure how to get slow queries/hour directly from MySQL.

> You say you that most queries are done directly on the slave.
> Why don't you spread the SELECTs across both boxes?

Do you mean using a load balancer accross all the slaves?  That would
defeat the purpose of having a local slave on each web server--  that
purpose being to return results as quickly as possible (network traffic is
expensive compaired to local disk)...

>> The problem is our customers are allowed to bulk-load keywords into
>> our database, which causes about 4 large tables to be updated quite a
>> bit.  Whenever this happens, the slaves struggle to get caught back
>> up...
> Have you tried enabling DELAY_KEY_WRITE on the 4 tables that
> your bulk-loader updates?

I'm using DELAY_KEY_WRITE on my slaves...  I didn't know it, but I just
looked and it says "ON" in 'show variables';...  When looking through the
docs on this, I also found "low_priority_updates", which I could set on
the slaves-- would this help?

> Why not spread the bulk-load in time, so that the keywords aren't
> added instantly?

We're looking into this now, as well-- some type of log that would keep
track of the large updates, and insert/update/delete them later, a few at
a time.  The problem is we lose the real-time notification of

> Why not modify your software so that it doesn't read from the
> slave while your bulk-loader runs?

The bulk load can happen on any web server, how would it notify the
others?  Where would they query instead?

> Can you give some more information on your master & slave config?
> (hardware, OS, MySQL show variables, show status)

RedHat 6.2 on i386, stock RPM install of MySQL-3.23.36-1...  The only
options we set up on the slaves are to connect to the master-- everything
else is stock...

Thanks again for the help,

-Matt Sturtz-

Replication priority / speedMatt Sturtz26 Dec
  • Re: Replication priority / speedJeremy Zawodny27 Dec
    • Re: Replication priority / speedMatt Sturtz30 Dec
      • Re: Replication priority / speedJeremy Zawodny30 Dec
      • Re: Replication priority / speedDan Nelson30 Dec
        • Re: Replication priority / speedJeremy Zawodny30 Dec
  • Re: Replication priority / speedSimon K. Grabowski30 Dec
    • Re: Replication priority / speedMatt Sturtz31 Dec
  • Re: Replication priority / speedSimon Grabowski1 Jan