Replication issues, slaves falling "behind"

Hi all,

I've got a three server setup. Master, and two slaves. Both slaves replicate from the master.

I've tweaked the two slaves as best as I possibly can, and all performance indicators look good. However, a script running keeps seeing the seconds behind master falling behind 3-4, sometimes 10, and even 20 seconds.

I've trapped these, and most of the time, the reason is either that a replicated query is taking too long to run, which shouldn't happen, because many times they are just simple updates on myisam tables using indexes (an explain confirms this). Sometimes, the table is InnoDB. It really is random on what causes it, but a "show processlist" on the slaves show replication lagged because of either replicated and/or direct queries taking forever, but again, that specific query only "takes forever" sometimes.

I believe I've tuned these two slaves the best I can. Here's what I've got..

| Key_read_requests | 1672843810 |
| Key_reads | 803414 |

This looks good, the ratio is great...

| Key_write_requests | 12696602 |
| Key_writes | 5313303 |

Do you need help?

In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software: Learn more about Pantek's Technical Support offerings Place an order for 24/7/365 Technical Support here Learn more about Pantek, who we are, and what we do Chat with a Live Operator Related lists.mysql.com Links announce benchmarks bugs certification cluster commits dotnet gui-tools internals java maxdb myodbc mysql mysqldoc packagers perl plusplus replication win32 Request more information about Pantek

Less than 1, which is good.

| Threads_cached | 15 |
| Threads_connected | 2 |
| Threads_created | 350013 |
| Threads_running | 1 |

Thread caching turned on, threads created not increasing substantially... a few per minute, if that.

| Innodb_buffer_pool_pages_data | 462 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 468790 |
| Innodb_buffer_pool_pages_free | 0 |
| Innodb_buffer_pool_pages_latched | 0 |
| Innodb_buffer_pool_pages_misc | 50 |
| Innodb_buffer_pool_pages_total | 512 |
| Innodb_buffer_pool_read_ahead_rnd | 272 |
| Innodb_buffer_pool_read_ahead_seq | 2044 |
| Innodb_buffer_pool_read_requests | 108601590 |
| Innodb_buffer_pool_reads | 36763 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 46188825 |

InnoDB buffers looking good.

| Created_tmp_disk_tables | 821424 |
| Created_tmp_files | 960 |
| Created_tmp_tables | 6538711 |

I just noticed this actually. Is that too high of a ratio? About 13% of our temporary tables are going to disk.

Query caching is turned on...

Do you need more help?

In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software: Learn more about Pantek's Technical Support offerings Place an order for 24/7/365 Technical Support here Learn more about Pantek, who we are, and what we do Chat with a Live Operator Related lists.mysql.com Links announce benchmarks bugs certification cluster commits dotnet gui-tools internals java maxdb myodbc mysql mysqldoc packagers perl plusplus replication win32 Request more information about Pantek

| Table_locks_immediate | 11399631 |
| Table_locks_waited | 17457 |

As far as locking is concerned, the locks_waited value is increasing, but its no where near the value of table_locks_immediate, which provides for a great ratio.

Sure, maybe the table_locks_waited is a bit high anyway, and that could be causing a bunch of problems.

Anyway, was wondering if anyone had any ideas.

Thanks,

Matt

-- 
MySQL Replication Mailing List
For list archives: 
http://lists.mysql.com/replication
To unsubscribe:    
http://lists.mysql.com/replication?unsub=lists@pantek.com