|
»
»
slave is running slow in another data center
|
View:
New views
4 Messages
—
Rating Filter:
Alert me
|
 |
slave is running slow in another data center
by Sagar, Sanjeev
::
Rate this Message:
Hello All,
I've 4 slaves of a master. Out of 4, 3 are in same data center and have no issues. The fourth one is in another data center and some time that slave sql_thread lag behind more then 5000 sec. It's only sql_thread, which slow down. All H/W, S/W and my.cnf files are same for all slaves. I ran the mysqladmin extended-status in iterative mode and following kind of results are there in my log file | Variable_name | Value | +----------------------------+-----------+ | Bytes_received | 2117975 | | Com_insert | 39 | | Com_show_slave_status | 4 | | Com_show_status | 1 | | Com_update | 49 | | Connections | 1 | | Handler_read_key | 49 | | Handler_read_next | 2 | | Handler_update | 3629 | | Key_blocks_not_flushed | 1 | | Key_blocks_unused | -21 | | Key_blocks_used | 21 | | Key_reads | 21 | | Key_write_requests | 94 | | Key_writes | 75 | | Max_used_connections | 1 | | Questions | 93 | | Table_locks_immediate | 88 | | Threads_connected | 1 | | Threads_created | 1 | +----------------------------+-----------+ +----------------------------+-----------+ | Variable_name | Value | +----------------------------+-----------+ | Bytes_received | 1469626 | | Bytes_sent | 3837 | | Com_delete | 2 | | Com_insert | 143 | | Com_show_status | 1 | | Com_update | 1247 | | Handler_delete | 5 | | Handler_read_key | 1249 | | Handler_read_next | 73 | | Handler_read_rnd | 8379 | | Handler_update | 9625 | | Handler_write | 8492 | | Key_blocks_not_flushed | 1 | | Key_blocks_unused | -158 | | Key_blocks_used | 158 | | Key_read_requests | 52672 | | Key_reads | 158 | | Questions | 1394 | | Table_locks_immediate | 1392 | | Threads_cached | 1 | | Threads_connected | -1 | Any idea any one, that what can be tunned here for keeping sql_thread running same as other slaves. My key_buffers are set to 1G in cnf file. Also on this slave, no binary logging is enabled or no slow log is enabled. And no application is connected. This is purely stand alone slave for disaster recovery. Regards, |
|
|
Re: slave is running slow in another data center
by Ananda Kumar
::
Rate this Message:
do a show full processlist on the 4th slave and see what process is running.
This will the sql's running which will be the cause of the slowness. Also u need to check on the INTERNET bandwidth from the current data center to the other data center. On 6/18/08, Sagar, Sanjeev <sanjeev.sagar@...> wrote: > > Hello All, > > > > I've 4 slaves of a master. Out of 4, 3 are in same data center and have > no issues. The fourth one is in another data center and some time that > slave sql_thread lag behind more then 5000 sec. It's only sql_thread, > which slow down. All H/W, S/W and my.cnf files are same for all slaves. > > > > I ran the mysqladmin extended-status in iterative mode and following > kind of results are there in my log file > > > > | Variable_name | Value | > > +----------------------------+-----------+ > > | Bytes_received | 2117975 | > > | Com_insert | 39 | > > | Com_show_slave_status | 4 | > > | Com_show_status | 1 | > > | Com_update | 49 | > > | Connections | 1 | > > | Handler_read_key | 49 | > > | Handler_read_next | 2 | > > | Handler_update | 3629 | > > | Key_blocks_not_flushed | 1 | > > | Key_blocks_unused | -21 | > > | Key_blocks_used | 21 | > > | Key_reads | 21 | > > | Key_write_requests | 94 | > > | Key_writes | 75 | > > | Max_used_connections | 1 | > > | Questions | 93 | > > | Table_locks_immediate | 88 | > > | Threads_connected | 1 | > > | Threads_created | 1 | > > +----------------------------+-----------+ > > > > +----------------------------+-----------+ > > | Variable_name | Value | > > +----------------------------+-----------+ > > | Bytes_received | 1469626 | > > | Bytes_sent | 3837 | > > | Com_delete | 2 | > > | Com_insert | 143 | > > | Com_show_status | 1 | > > | Com_update | 1247 | > > | Handler_delete | 5 | > > | Handler_read_key | 1249 | > > | Handler_read_next | 73 | > > | Handler_read_rnd | 8379 | > > | Handler_update | 9625 | > > | Handler_write | 8492 | > > | Key_blocks_not_flushed | 1 | > > | Key_blocks_unused | -158 | > > | Key_blocks_used | 158 | > > | Key_read_requests | 52672 | > > | Key_reads | 158 | > > | Questions | 1394 | > > | Table_locks_immediate | 1392 | > > | Threads_cached | 1 | > > | Threads_connected | -1 | > > > > > > > > > > Any idea any one, that what can be tunned here for keeping sql_thread > running same as other slaves. My key_buffers are set to 1G in cnf file. > Also on this slave, no binary logging is enabled or no slow log is > enabled. And no application is connected. This is purely stand alone > slave for disaster recovery. > > > > Regards, > > > > > > |
|
|
RE: slave is running slow in another data center
by Sagar, Sanjeev
::
Rate this Message:
do a show full processlist on the 4th slave and see what process is
running. This will the sql's running which will be the cause of the slowness. >> This does not help because there is not a specific query, which is running slow. Other 3 slaves are running same query and I"ve compared the explain on all slaves for most of the updates and they looks same. All H/W and S/W config are same including the cnf file settings. Also u need to check on the INTERNET bandwidth from the current data center to the other data center. >> This is also not an issue because IO thread is way ahead then SQL thread. It's a SQL thread, which is not able to keep up. On 6/18/08, Sagar, Sanjeev <sanjeev.sagar@...> wrote: Hello All, I've 4 slaves of a master. Out of 4, 3 are in same data center and have no issues. The fourth one is in another data center and some time that slave sql_thread lag behind more then 5000 sec. It's only sql_thread, which slow down. All H/W, S/W and my.cnf files are same for all slaves. I ran the mysqladmin extended-status in iterative mode and following kind of results are there in my log file | Variable_name | Value | +----------------------------+-----------+ | Bytes_received | 2117975 | | Com_insert | 39 | | Com_show_slave_status | 4 | | Com_show_status | 1 | | Com_update | 49 | | Connections | 1 | | Handler_read_key | 49 | | Handler_read_next | 2 | | Handler_update | 3629 | | Key_blocks_not_flushed | 1 | | Key_blocks_unused | -21 | | Key_blocks_used | 21 | | Key_reads | 21 | | Key_write_requests | 94 | | Key_writes | 75 | | Max_used_connections | 1 | | Questions | 93 | | Table_locks_immediate | 88 | | Threads_connected | 1 | | Threads_created | 1 | +----------------------------+-----------+ +----------------------------+-----------+ | Variable_name | Value | +----------------------------+-----------+ | Bytes_received | 1469626 | | Bytes_sent | 3837 | | Com_delete | 2 | | Com_insert | 143 | | Com_show_status | 1 | | Com_update | 1247 | | Handler_delete | 5 | | Handler_read_key | 1249 | | Handler_read_next | 73 | | Handler_read_rnd | 8379 | | Handler_update | 9625 | | Handler_write | 8492 | | Key_blocks_not_flushed | 1 | | Key_blocks_unused | -158 | | Key_blocks_used | 158 | | Key_read_requests | 52672 | | Key_reads | 158 | | Questions | 1394 | | Table_locks_immediate | 1392 | | Threads_cached | 1 | | Threads_connected | -1 | Any idea any one, that what can be tunned here for keeping sql_thread running same as other slaves. My key_buffers are set to 1G in cnf file. Also on this slave, no binary logging is enabled or no slow log is enabled. And no application is connected. This is purely stand alone slave for disaster recovery. Regards, |
|
|
Re: slave is running slow in another data center
by Ananda Kumar
::
Rate this Message:
IO Thread will always be ahead,becuase this will keep interacting with
master and any changes at master will be pulled by this IO thread and insert into RELAY LOG. Its the SQL THREAD that will execute all the statements in RELAY LOG. By the way how did u check IO thread is ahead of SQL Thread. On 6/18/08, Sagar, Sanjeev <sanjeev.sagar@...> wrote: > > do a show full processlist on the 4th slave and see what process is > running. This will the sql's running which will be the cause of the > slowness. > > > > >> This does not help because there is not a specific query, which is > running slow. Other 3 slaves are running same query and I"ve compared the > explain on all slaves for most of the updates and they looks same. All H/W > and S/W config are same including the cnf file settings. > > > > Also u need to check on the INTERNET bandwidth from the current data center > to the other data center. > > >> This is also not an issue because IO thread is way ahead then SQL > thread. It's a SQL thread, which is not able to keep up. > > > > > On 6/18/08, *Sagar, Sanjeev* <sanjeev.sagar@...> wrote: > > Hello All, > > > > I've 4 slaves of a master. Out of 4, 3 are in same data center and have > no issues. The fourth one is in another data center and some time that > slave sql_thread lag behind more then 5000 sec. It's only sql_thread, > which slow down. All H/W, S/W and my.cnf files are same for all slaves. > > > > I ran the mysqladmin extended-status in iterative mode and following > kind of results are there in my log file > > > > | Variable_name | Value | > > +----------------------------+-----------+ > > | Bytes_received | 2117975 | > > | Com_insert | 39 | > > | Com_show_slave_status | 4 | > > | Com_show_status | 1 | > > | Com_update | 49 | > > | Connections | 1 | > > | Handler_read_key | 49 | > > | Handler_read_next | 2 | > > | Handler_update | 3629 | > > | Key_blocks_not_flushed | 1 | > > | Key_blocks_unused | -21 | > > | Key_blocks_used | 21 | > > | Key_reads | 21 | > > | Key_write_requests | 94 | > > | Key_writes | 75 | > > | Max_used_connections | 1 | > > | Questions | 93 | > > | Table_locks_immediate | 88 | > > | Threads_connected | 1 | > > | Threads_created | 1 | > > +----------------------------+-----------+ > > > > +----------------------------+-----------+ > > | Variable_name | Value | > > +----------------------------+-----------+ > > | Bytes_received | 1469626 | > > | Bytes_sent | 3837 | > > | Com_delete | 2 | > > | Com_insert | 143 | > > | Com_show_status | 1 | > > | Com_update | 1247 | > > | Handler_delete | 5 | > > | Handler_read_key | 1249 | > > | Handler_read_next | 73 | > > | Handler_read_rnd | 8379 | > > | Handler_update | 9625 | > > | Handler_write | 8492 | > > | Key_blocks_not_flushed | 1 | > > | Key_blocks_unused | -158 | > > | Key_blocks_used | 158 | > > | Key_read_requests | 52672 | > > | Key_reads | 158 | > > | Questions | 1394 | > > | Table_locks_immediate | 1392 | > > | Threads_cached | 1 | > > | Threads_connected | -1 | > > > > > > > > > > Any idea any one, that what can be tunned here for keeping sql_thread > running same as other slaves. My key_buffers are set to 1G in cnf file. > Also on this slave, no binary logging is enabled or no slow log is > enabled. And no application is connected. This is purely stand alone > slave for disaster recovery. > > > > Regards, > > > > > > |
| Free Forum Powered by Nabble | Forum Help |