>>>> 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
> 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,