Announcement

Announcement Module
Collapse
No announcement yet.

TRANSACTION ACTIVE 55 sec starting index read

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • TRANSACTION ACTIVE 55 sec starting index read

    we have 12GB 8core percona 5.5.25a server.

    at random interval it start to pile up the queries and freeze.

    all our db tables are using innodb.

    when i do innodb status it showed

    TRANSACTION 1A932BE, ACTIVE 55 sec starting index read

    the full innodb during this freeze is attached.

    what can cause such sudden freeze.

    thread waited on buf0buf at those lines:


    for (i = 0; i < srv_buf_pool_instances; i++) { buf_pool_t* buf_pool; buf_pool = buf_pool_from_array(i); buf_flush_list_mutex_enter(buf_pool); bpage = UT_LIST_GET_LAST(buf_pool->flush_list); if (bpage != NULL) { ut_ad(bpage->in_flush_list); lsn = bpage->oldest_modification; } buf_flush_list_mutex_exit(buf_pool);

  • #2
    I'm seeing more buf0buf

    I have increased innodb_buffer_pool_instances from 1 to 3 will this help solve this issue.

    --Thread 140197389825792 has waited at buf0buf.ic line 1256 for 0.0000 seconds the semaphore:
    Mutex at 0x7f83655fc678 '&block->mutex', lock var 0
    waiters flag 0
    --Thread 140197392221952 has waited at buf0buf.ic line 1256 for 0.0000 seconds the semaphore:
    Mutex at 0x7f83655fc678 '&block->mutex', lock var 0
    waiters flag 0
    --Thread 140197391156992 has waited at buf0buf.ic line 372 for 0.0000 seconds the semaphore:
    Mutex at 0x7f83655fc678 '&block->mutex', lock var 0
    waiters flag 0
    --Thread 140201749812992 has waited at buf0buf.ic line 372 for 0.0000 seconds the semaphore:
    Mutex at 0x7f83655fc678 '&block->mutex', lock var 0
    waiters flag 0
    --Thread 140211026294528 has waited at buf0buf.ic line 372 for 0.0000 seconds the semaphore:
    Mutex at 0x7f83655fc678 '&block->mutex', lock var 0
    waiters flag 0
    Mutex spin waits 920731983, rounds 3303841208, OS waits 1310995
    RW-shared spins 4074733, rounds 23954032, OS waits 379441
    RW-excl spins 876568, rounds 10258878, OS waits 77146
    Spin rounds per wait: 3.59 mutex, 5.88 RW-shared, 11.70 RW-excl

    Comment

    Working...
    X