>>>> 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 success/failure... > 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- --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
