Re: Replication priority / speed
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
Re: Replication priority / speed
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
Re: Replication priority / speed
Hello, Jeremy, et al-- Thanks for the reply before... Further questions:
Is it possible to set either set the priority ('nice') of the Slave
thread down so it doesn't do that?
The slave thread only? No, not really. You could nice MySQL when you
start it up. But I'm not sure how much effect (positive or negative)
that'd have.
When I run show [full] processlist, there's an Id column, but it
doesn't corrospond with the Unix PID of the process (on OS's that use a
seperate PID for each thread-- like Linux does)... Is there any way
to(easilly) figure out which PID is handling the slave thread, so that I
might re-nice it after it's already been started up?
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...
Are your updates already well optimized? If you're doing enough work to
cause noticeable speed problems, I'd double-check that if you
haven't already.
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.
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...
Thanks again,
-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
Re: Replication priority / speed
On Mon, Dec 30, 2002 at 01:21:49PM -0700, Matt Sturtz wrote:
Hello, Jeremy, et al-- Thanks for the reply before... Further questions:
Is it possible to set either set the priority ('nice') of the Slave
thread down so it doesn't do that?
The slave thread only? No, not really. You could nice MySQL when you
start it up. But I'm not sure how much effect (positive or negative)
that'd have.
When I run show [full] processlist, there's an Id column, but it
doesn't corrospond with the Unix PID of the process (on OS's that
use a seperate PID for each thread-- like Linux does)...
Right.
Is there any way to(easilly) figure out which PID is handling the
slave thread, so that I might re-nice it after it's already been
started up?
Not that I know of. From MySQL's point of view there's no way to
know.
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...
I've not heard of any. You can always lobby to get it on the MySQL
TODO list.
Are your updates already well optimized? If you're doing enough work to
cause noticeable speed problems, I'd double-check that if you
haven't already.
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. 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...
Ahh, okay. I buy that.
Jeremy
--
Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo!
[EMAIL PROTECTED] | http://jeremy.zawodny.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
Re: Replication priority / speed
On Mon, Dec 30, 2002 at 03:48:30PM -0600, Dan Nelson wrote:
In the last episode (Dec 30), Matt Sturtz said:
Hello, Jeremy, et al-- Thanks for the reply before... Further questions:
Is it possible to set either set the priority ('nice') of the
Slave thread down so it doesn't do that?
The slave thread only? No, not really. You could nice MySQL when
you start it up. But I'm not sure how much effect (positive or
negative) that'd have.
When I run show [full] processlist, there's an Id column, but it
doesn't corrospond with the Unix PID of the process (on OS's that use
a seperate PID for each thread-- like Linux does)... Is there any
way to(easilly) figure out which PID is handling the slave thread, so
that I might re-nice it after it's already been started up?
That probably won't help you, since I doubt you're CPU-bound. Most
likely your slave thread is monopolizing the disk I/O. You could try
manually stopping and starting the thread by sending it SIGSTOP and
SIGCONT signals. Alternating the two every 5 seconds will give you a
50% slowdown. I don't know how LinuxThreads is going to like having
threads messes with like that though. You can probably also do SLAVE
STOP / SLAVE START and get the same effect.
Using SLAVE {START|STOP} is much better. Image what happens if the
slave thread gets a critical lock and then you SIGSTOP it!
Jeremy
--
Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo!
[EMAIL PROTECTED] | http://jeremy.zawodny.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
Re: Replication priority / speed
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? 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. How many queries/second do you see on your slave box? How many slow queries per hour? You say you that most queries are done directly on the slave. Why don't you spread the SELECTs across both boxes? 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? Why not spread the bulk-load in time, so that the keywords aren't added instantly? Why not modify your software so that it doesn't read from the slave while your bulk-loader runs? Can you give some more information on your master slave config? (hardware, OS, MySQL show variables, show status) 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
Re: Replication priority / speed
In the last episode (Dec 30), Matt Sturtz said:
Hello, Jeremy, et al-- Thanks for the reply before... Further questions:
Is it possible to set either set the priority ('nice') of the
Slave thread down so it doesn't do that?
The slave thread only? No, not really. You could nice MySQL when
you start it up. But I'm not sure how much effect (positive or
negative) that'd have.
When I run show [full] processlist, there's an Id column, but it
doesn't corrospond with the Unix PID of the process (on OS's that use
a seperate PID for each thread-- like Linux does)... Is there any
way to(easilly) figure out which PID is handling the slave thread, so
that I might re-nice it after it's already been started up?
That probably won't help you, since I doubt you're CPU-bound. Most
likely your slave thread is monopolizing the disk I/O. You could try
manually stopping and starting the thread by sending it SIGSTOP and
SIGCONT signals. Alternating the two every 5 seconds will give you a
50% slowdown. I don't know how LinuxThreads is going to like having
threads messes with like that though. You can probably also do SLAVE
STOP / SLAVE START and get the same effect.
--
Dan Nelson
[EMAIL PROTECTED]
-
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
Replication priority / speed
Hello,
We run several frontend servers (Linux/Apache/PHP) behind a load balancer.
Each frontend is also a MySQL slave, and all queries are done locally
(all changes go directly to the master). The problem is, whenever
somebody makes a lot of changes to the database at once (deletes, updates,
or adds a lot of rows), the slave thread loads up the server to the point
where Apache doesn't get much CPU anymore, and then our sites slow way
down...
Is it possible to set either set the priority ('nice') of the Slave thread
down so it doesn't do that? Or, alternatly, is there a way to limit the
slave thread to only X bin-log transactions per second? We don't much
care if the frondends aren't updated at exactly the same second (or even
the same minute, just so we have the appearance of real-time), so we'd
prefer to slow down the slave process in an effort to keep Apache fast...
Thanks for any advice,
-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
Re: Replication priority / speed
On Thu, Dec 26, 2002 at 12:17:28PM -0700, Matt Sturtz wrote:
Hello,
We run several frontend servers (Linux/Apache/PHP) behind a load
balancer. Each frontend is also a MySQL slave, and all queries are
done locally (all changes go directly to the master). The problem
is, whenever somebody makes a lot of changes to the database at once
(deletes, updates, or adds a lot of rows), the slave thread loads up
the server to the point where Apache doesn't get much CPU anymore,
and then our sites slow way down...
Hmm.
Is it possible to set either set the priority ('nice') of the Slave
thread down so it doesn't do that?
The slave thread only? No, not really. You could nice MySQL when you
start it up. But I'm not sure how much effect (positive or negative)
that'd have.
Or, alternatly, is there a way to limit the slave thread to only X
bin-log transactions per second?
There is not.
As a gross hack, you could monitor the speed closely and use a lot of
SLAVE STOP and SLAVE START commands to throttle it. But that's
really quite ugly.
Are your updates already well optimized? If you're doing enough work
to cause noticeable speed problems, I'd double-check that if you
haven't already.
Jeremy
--
Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo!
[EMAIL PROTECTED] | http://jeremy.zawodny.com/
MySQL 3.23.51: up 11 days, processed 442,093,184 queries (435/sec. avg)
-
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
