> 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 would use SHOW STATUS, but mytop did it for you :-) > 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)... It's a design choice. Personally I prefer to load-balance across multiple boxes as it gives me the fail-over protection and umm... load balancing :-) Obviously local disk will always be faster than TCP/IP (MySQL AB claims 30% faster), but chances are your users won't see a difference if your LAN is any decent. However fail-over & load-balancing is a big benefit of such a set 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? You must set DELAY_KEY_WRITE on each of the four tables: ALTER TABLE table_name DELAY_KEY_WRITE=1 You could certainly try low_priority_updates, although it is possible that it will make even harder for your slave to catch-up with master's binlog. This function delays your updates until no more clients are reading from the table. Please note that there was a bug in low_priority_updates that wasn't fixed until 3.23.40. > > 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? Do you need to notify the others? You say that you are using a local slave on each webserver. In that case, you'd simply have the server read the data from master while the bulk load occurs. Or you could create an agent process that would watch for bulk loading on all web servers. > 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... If you are using a stock distribution, then there are many optimizations that you could implement to increase the performance. You should really review: http://www.mysql.com/doc/en/MySQL_Optimisation.html Why don't you try to upgrade to the latest MySQL 3.23.54a. I don't know about 3.23.36, but I know that replication was buggy. Also, feel free to send me the remaining info: 1. How much RAM do you have in your master & slaves? 2. Perform 'SHOW VARIABLES' and 'SHOW STATUS' queries on the master & slaves and send me the output. Simon Grabowski GetResponse.com --------------------------------------------------------------------- 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
