RE: Replication issues, slaves falling "behind"

You checked the query before the delayed one?

Is the hardware on the slaves at least as good as on the master?

Are you willing to share these:
EXPLAIN ... \G (on the SELECT equivalent of the UPDATE/INSERT/...) SHOW TABLE STATUS LIKE 'tbl';
SHOW CREATE TABLE tbl;

Many of the numbers below need to be divided by Uptime to make any sense. Also deltas would be more useful.

Threads_created can be improved by increasing thread_cache_size.

> -----Original Message-----
> From: Matt Juszczak [mailto:matt@atopia.net]
> Sent: Thursday, August 09, 2007 8:43 AM
> To: replication@lists.mysql.com
> Subject: 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 |
>
> 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...
>
> | 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=rjames@yahoo-inc.com
>
>

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