List:General Discussion« Previous MessageNext Message »
From:Simon Grabowski Date:January 1 2003 11:44am
Subject:Re: Replication priority / speed
View as plain text  
> 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

Thread
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