Re: Replication issues, slaves falling "behind"

The value is:

| innodb_thread_concurrency       | 8                      |

On Thu, 9 Aug 2007, Mark Callaghan wrote:

> 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
>
>
> !DSPAM:46bb3fbd418225209328925!
>

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