TRANSACTION ACTIVE 55 sec starting index read

MySQL & MariaDB Other MySQL® Questions
1

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