Re: Replication issues, slaves falling "behind"

The obvious problem for MyISAM tables is that the replication thread is blocked trying to update tables while user sessions are querying them.

What is the value for innodb_thread_concurrency in SHOW VARIABLES? If the number of sessions actively doing queries on InnoDB tables is >= to this value, then the replication thread will occasionally block trying to get tickets that allow it to enter the InnoDB engine. This can make replication statements appear to take forever (100X slower than expected).

On 8/9/07, Matt Juszczak <matt@atopia.net> wrote:
>
> 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=mcallaghan@google.com
>
>

-- 
Mark Callaghan
mcallaghan@google.com