Possible performance problem with Percona Mysql

Hi guys

I’m currently running a very high read/write database server on M710 4*RAID10 2.5inch 10k 300G disks, PERC H700 raid controler - 138Gb buffer pool in a box with 144G of RAM.

I’m running
Server version: 5.1.45-89.lenny.52-log (Percona SQL Server (GPL), XtraDB 10.2)

All of a sudden yesterday the box became quite IO bound and I was seeing this in innodb status:

ype ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> show innodb status\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:

110110 19:28:46 INNODB MONITOR OUTPUT

Per second averages calculated from the last 59 seconds

BACKGROUND THREAD

srv_master_thread loops: 1614 1_second, 1613 sleeps, 161 10_second, 0 background, 0 flush
srv_master_thread log flush and writes: 1620

SEMAPHORES

OS WAIT ARRAY INFO: reservation count 51853, signal count 25577
–Thread 1193445712 has waited at btr/btr0cur.c line 480 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1255213392 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1221400912 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1205958992 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1225660752 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1240570192 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1264798032 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1205160272 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1277577552 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1228323152 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1232316752 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1262668112 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1224862032 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1269856592 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1201965392 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1280772432 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1246161232 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1234446672 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1225394512 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1253083472 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1207556432 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1249356112 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1281837392 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1288493392 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1217407312 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1195575632 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1245628752 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1236576592 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1249089872 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1229388112 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1273583952 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1266661712 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1200900432 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1214212432 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1280506192 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1279441232 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1205426512 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1234979152 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1276778832 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1204361552 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1265596752 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1222998352 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1236044112 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1274648912 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1289558352 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1196640592 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1254414672 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1243232592 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1248557392 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1212614992 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1290357072 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1193978192 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c073620 ‘&new_index->lock’
a writer (thread id 1216608592) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1278376272 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1261869392 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1243765072 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1215543632 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1224595792 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1263466832 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1258142032 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1273317712 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1202497872 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1289025872 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1196108112 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1208621392 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1251486032 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1288227152 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1262934352 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1281571152 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1251752272 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1238706512 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1204095312 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1291688272 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1249622352 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1282902352 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1219537232 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1254947152 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1195043152 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1278642512 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1232050512 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
–Thread 1217673552 has waited at btr/btr0cur.c line 487 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x7ef68c079fe0 ‘&new_index->lock’
a writer (thread id 1193445712) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0cur.c line 487
Last time write locked in file btr/btr0cur.c line 480
Mutex spin waits 25481, rounds 197092, OS waits 3688
RW-shared spins 35065, OS waits 39085; RW-excl spins 752, OS waits 8941
Spin rounds per wait: 7.73 mutex, 33.86 RW-shared, 366.57 RW-excl

LATEST FOREIGN KEY ERROR

110110 19:28:42 Transaction:
TRANSACTION 34A00DC46, ACTIVE 0 sec, process no 22145, OS thread id 1193978192 inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1216, 1 row lock(s), undo log entries 1
MySQL thread id 2, query id 1585323 update
insert into SAVED_SEARCH (ENCODED_PROPERTIES, NAME, NOTE, NTF_DISABLED_TIMESTAMP, NTF_FREQUENCY, SEARCH_TYPE, USAGE_TIMESTAMP, VISITOR_UID, SEARCH_ID) values (’{“includeSurrounding”:“false”,“where”:“Coromandel Valley, SA 5051”,“channel”:“buy”,“listingType”:“buy”,“searchView “:“list”,“domain”:“www.realestate.com.au”,“preferredState “:“sa”,“userWhere”:“coromandel valley, sa 5051;”,“resolvedLocations”:”|Coromandel Valley, SA 5051|”,“resolvedLocationCodes”:”|S-5770|”}’, ‘recent search - 1294646047836’, null, null, null, ‘recent’, ‘2011-01-10 18:54:07’, ‘ff8081812ca0ac67012ca62be60d1ab5’, 'ff8081812d
Foreign key constraint fails for table rea.saved_search:
,
CONSTRAINT vstr$ssearch$visitor_uid$fk FOREIGN KEY (visitor_uid) REFERENCES visitor (visitor_uid) ON DELETE CASCADE
Trying to add in child table, in index ssearch$search_id$visitor_uid$uk tuple:
DATA TUPLE: 2 fields;
0: len 32; hex 666638303831383132636130616336373031326361363262653630643161 6235; asc ff8081812ca0ac67012ca62be60d1ab5;;
1: len 32; hex 666638303831383132643031333936333031326436656561663435663333 3939; asc ff8081812d013963012d6eeaf45f3399;;

But in parent table rea.visitor, in index PRIMARY,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 12; compact format; info bits 0
0: len 30; hex 666638303831383132636130616336373031326361363433393030313064 ; asc ff8081812ca0ac67012ca64390010d; (total 32 bytes);
1: len 6; hex 0002ce12852a; asc *;;
2: len 7; hex 0000031c2410a5; asc $ ;;
3: len 25; hex 68727562656e7340727465646761727279652e636f6d2e6175; asc hrubens@rtedgarrye.com.au;;
4: len 5; hex 4948594655; asc IHYFU;;
5: len 5; hex 68616e7372; asc hansr;;
6: len 4; hex 4cf75d12; asc L ] ;;
7: SQL NULL;
8: SQL NULL;
9: len 0; hex ; asc ;;
10: SQL NULL;
11: SQL NULL;


FILE I/O

I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o’s: 0, sync i/o’s: 0
Pending flushes (fsync) log: 0; buffer pool: 0
365675 OS file reads, 151383 OS file writes, 11984 OS fsyncs
14 pending preads, 0 pending pwrites
717.33 reads/s, 16414 avg bytes/read, 115.18 writes/s, 8.81 fsyncs/s

INSERT BUFFER AND ADAPTIVE HASH INDEX

Ibuf: size 608, free list len 25600, seg size 26209,
141617 inserts, 33514 merged recs, 18402 merges
Hash table size 285936779, node heap has 604 buffer(s)
185.78 hash searches/s, 6305.93 non-hash searches/s

LOG

Log sequence number 4422828513103
Log flushed up to 4422828484743
Last checkpoint at 4422617240034
Max checkpoint age 271079793
Checkpoint age target 262608550
Modified age 210578125
Checkpoint age 211273069
0 pending log writes, 0 pending chkp writes
1820 log i/o’s done, 1.07 log i/o’s/second

BUFFER POOL AND MEMORY

Total memory allocated 142284455936; in additional pool allocated 0
Internal hash tables (constant factor + variable factor)
Adaptive hash index 2297394376 (2287494232 + 9900144)
Page hash 142969544
Dictionary cache 572107232 (571875632 + 231600)
File system 96928 (82672 + 14256)
Lock system 343365288 (343268168 + 97120)
Recovery system 0 (0 + 0)
Threads 434296 (406936 + 27360)
Dictionary memory allocated 231600
Buffer pool size 8469312
Buffer pool size, bytes 138761207808
Free buffers 8082267
Database pages 386441
Old database pages 142671
Modified db pages 135180
Pending reads 14
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2185, not young 0
0.91 youngs/s, 0.00 non-youngs/s
Pages read 377726, created 8701, written 195891
731.64 reads/s, 4.31 creates/s, 150.62 writes/s
Buffer pool hit rate 973 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 1.10/s, evicted without access 0.00/s
LRU len: 386441, unzip_LRU len: 0
I/O sum[0]:cur[300], unzip sum[0]:cur[0]

ROW OPERATIONS

0 queries inside InnoDB, 0 queries in queue
77 read views open inside InnoDB
Main thread process no. 22145, id 1168267600, state: sleeping
Number of rows inserted 232501, updated 275871, deleted 27560, read 467575
206.93 inserts/s, 96.44 updates/s, 20.19 deletes/s, 2049.25 reads/s

TRANSACTIONS

Trx id counter 34A00EE85
Purge done for trx’s n:o < 349BF3EBB undo n:o < 19
History list length 1116001
… truncated…
, sees < 34A00E973
—TRANSACTION 34A00EDB9, ACTIVE 0 sec, process no 22145, OS thread id 1253083472 estimating records in index range
mysql tables in use 2, locked 0
MySQL thread id 21866, query id 1610540 172.24.0.2 visitor statistics
select visitor0_.VISITOR_UID as VISITOR1_15_1_, visitor0_.VISITOR_ALIAS as VISITOR2_15_1_, visitor0_.DFLT_LOCATION as DFLT3_15_1_, visitor0_.DFLT_REGION as DFLT4_15_1_, visitor0_.PASSWORD_EXPIRATION as PASSWORD5_15_1_, visitor0_.SITE_NAME as SITE6_15_1_, visitor0_.VISITOR_LOGIN_ID as VISITOR7_15_1_, visitor0_.VISITOR_NAME as VISITOR8_15_1_, visitor0_.VISITOR_PASSWORD as VISITOR9_15_1_, visitorpre1_.VISITOR_UID as VISITOR4_3_, visitorpre1_.VISITOR_PREFERENCE_ID as VISITOR1_3_, visitorpre1_.PREFERENCE_CODE as formula5_3_, visitorpre1_.VISITOR_PREFERENCE_ID as VISITOR1_17_0_, visitorpre1_.PREFERE
Trx read view will not see trx with id >= 34A00EDBA, sees < 34A00E973
—TRANSACTION 34A00EDB0, ACTIVE 0 sec, process no 22145, OS thread id 1225394512 estimating records in index range
mysql tables in use 2, locked 0
MySQL thread id 21871, query id 1610498 172.24.0.2 visitor statistics
select visitor0_.VISITOR_UID as VISITOR1_15_1_, visitor0_.VISITOR_ALIAS as VISITOR2_15_1_, visitor0_.DFLT_LOCATION as DFLT3_15_1_, visitor0_.DFLT_REGION as DFLT4_15_1_, visitor0_.PASSWORD_EXPIRATION as PASSWORD5_15_1_, visitor0_.SITE_NAME as SITE6_15_1_, visitor0_.VISITOR_LOGIN_ID as VISITOR7_15_1_, visitor0_.VISITOR_NAME as VISITOR8_15_1_, visitor0_.VISITOR_PASSWORD as VISITOR9_15_1_, visitorpre1_.VISITOR_UID as VISITOR4_3_, visitorpre1_.VISITOR_PREFERENCE_ID as VISITOR1_3_, visitorpre1_.PREFERENCE_CODE as formula5_3_, visitorpre1_.VISITOR_PREFERENCE_ID as VISITOR1_17_0_, visitorpre1_.PREFERE
Trx read view will not see trx with id >= 34A00EDB1, sees < 34A00E973
—TRANSACTION 34A00EDAD, ACTIVE 0 sec, process no 22145, OS thread id 1289025872 estimating records in index range
mysql tables in use 2, locked 0
MySQL thread id 21281, query id 1610480 172.24.0.2 visitor statistics
select visitor0_.VISITOR_UID as VISITOR1_15_1_, visitor0_.VISITOR_ALIAS as VISITOR2_15_1_, visitor0_.DFLT_LOCATION as DFLT3_15_1_, visitor0_.DFLT_REGION as DFLT4_15_1_, visitor0_.PASSWORD_EXPIRATION as PASSWORD5_15_1_, visitor0_.SITE_NAME as SITE6_15_1_, visitor0_.VISITOR_LOGIN_ID as VISITOR7_15_1_, visitor0_.VISITOR_NAME as VISITOR8_15_1_, visitor0_.VISITOR_PASSWORD as VISITOR9_15_1_, visitorpre1_.VISITOR_UID as VISITOR4_3_, visitorpre1_.VISITOR_PREFERENCE_ID as VISITOR1_3_, visitorpre1_.PREFERENCE_CODE as formula5_3_, visitorpre1_.VISITOR_PREFERENCE_ID as VISITOR1_17_0_, visitorpre1_.PREFERE
Trx read view will not see trx with id >= 34A00EE59, sees < 34A00E973
—TRANSACTION 34A00EDAB, ACTIVE 0 sec, process no 22145, OS thread id 1234446672 estimating records in index range
mysql tables in use 2, locked 0
MySQL thread id 21459, query id 1610476 172.24.0.2 visitor statistics
select visitor0_.VISITOR_UID as VISITOR1_15_1_, visitor0_.VISITOR_ALIAS as VISITOR2_15_1_, visitor0_.DFLT_LOCATION as DFLT3_15_1_, visitor0_.DFLT_REGION as DFLT4_15_1_, visitor0_.PASSWORD_EXPIRATION as PASSWORD5_15_1_, visitor0_.SITE_NAME as SITE6_15_1_, visitor0_.VISITOR_LOGIN_ID as VISITOR7_15_1_, visitor0_.VISITOR_NAME as VISITOR8_15_1_, visitor0_.VISITOR_PASSWORD as VISITOR9_15_1_, visitorpre1_.VISITOR_UID as VISITOR4_3_, visitorpre1_.VISITOR_PREFERENCE_ID as VISITOR1_3_, visitorpre1_.PREFERENCE_CODE as formula5_3_, visitorpre1_.VISITOR_PREFERENCE_ID as VISITOR1_17_0_, visitorpre1_.PREFERE
Trx read view will not see trx with id >= 34A00EDAC, sees < 34A00E973
—TRANSACTION 34A00EDA7, ACTIVE 0 sec, process no 22145, OS thread id 1246161232 estimating records in index range
mysql tables in use 2, locked 0
MySQL thread id 21472, query id 1610459 172.24.0.2 visitor statistics
select visitor0_.VISITOR_UID as VISITOR1_15_1_, visitor0_.VISITOR_ALIAS as VISITOR2_15_1_, visitor0_.DFLT_LOCATION as DFLT3_15_1_, visitor0_.DFLT_REGION as DFLT4_15_1_, visitor0_.PASSWORD_EXPIRATION as PASSWORD5_15_1_, visitor0_.SITE_NAME as SITE6_15_1_, visitor0_.VISITOR_LOGIN_ID as VISITOR7_15_1_, visitor0_.VISITOR_NAME as VISITOR8_15_1_, visitor0_.VISITOR_PASSWORD as VISITOR9_15_1_, visitorpre1_.VISITOR_UID as VISITOR4_3_, visitorpre1_.VISITOR_PREFERENCE_ID as VISITOR1_3_, visitorpre1_.PREFERENCE_CODE as formula5_3_, visitorpre1_.VISITOR_PREFERENCE_ID as VISITOR1_17_0_, visitorpre1_.PREFERE
Trx read view will not see trx with id >= 34A00EDA8, sees < 34A00E973
—TRANSACTION 34A00ED9A, ACTIVE 0 sec, process no 22145, OS thread id 1209952592 starting index read
mysql tables in use 1, locked 0
MySQL thread id 21868, query id 1610286 172.24.0.2 visitor Sending data
select locations0_.VISITOR_UID as VISITOR25_1_, locations0_.LOCATION_ID as LOCATION2_1_, locations0_.LOCATION_ID as LOCATION2_10_0_, locations0_.CITY as CITY10_0_, locations0_.CITY_SECTION_1 as CITY4_10_0_, locations0_.CITY_SECTION_2 as CITY5_10_0_, locations0_.CITY_SECTION_3 as CITY6_10_0_, locations0_.COUNTRY as COUNTRY10_0_, locations0_.COUNTY as COUNTY10_0_, locations0_.FLAGS as FLAGS10_0_, locations0_.GEOCODE_PRECISION as GEOCODE10_10_0_, locations0_.ICON_NAME as ICON11_10_0_, locations0_.LATITUDE as LATITUDE10_0_, locations0_.LOCATION_TYPE as LOCATION1_10_0_, locations0_.LONGITUDE as LON
Trx read view will not see trx with id >= 34A00ED9B, sees < 34A00E973
—TRANSACTION 34A00ED95, ACTIVE 0 sec, process no 22145, OS thread id 1278376272 estimating records in index range
mysql tables in use 2, locked 0
MySQL thread id 21787, query id 1610370 172.24.0.2 visitor statistics
select visitor0_.VISITOR_UID as VISITOR1_15_1_, visitor0_.VISITOR_ALIAS as VISITOR2_15_1_, visitor0_.DFLT_LOCATION as DFLT3_15_1_, visitor0_.DFLT_REGION as DFLT4_15_1_, visitor0_.PASSWORD_EXPIRATION as PASSWORD5_15_1_, visitor0_.SITE_NAME as SITE6_15_1_, visitor0_.VISITOR_LOGIN_ID as VISITOR7_15_1_, visitor0_.VISITOR_NAME as VISITOR8_15_1_, visitor0_.VISITOR_PASSWORD as VISITOR9_15_1_, visitorpre1_.VISITOR_UID as VISITOR4_3_, visitorpre1_.VISITOR_PREFERENCE_ID as VISITOR1_3_, visitorpre1_.PREFERENCE_CODE as formula5_3_, visitorpre1_.VISITOR_PREFERENCE_ID as VISITOR1_17_0_, visitorpre1_.PREFERE
Trx read view will not see trx with id >= 34A00EE28, sees < 34A00E973
—TRANSACTION 34A00ED89, ACTIVE 0 sec, process no 22145, OS thread id 1201965392 estimating records in index range
mysql tables in use 2, locked 0
MySQL thread id 21754, query id 1610316 172.24.0.2 visitor statistics
select visitor0_.VISITOR_UID as VISITOR1_15_1_, visitor0_.VISITOR_ALIAS as VISITOR2_15_1_, visitor0_.DFLT_LOCATION as DFLT3_15_1_, visitor0_.DFLT_REGION as DFLT4_15_1_, visitor0_.PASSWORD_EXPIRATION as PASSWORD5_15_1_, visitor0_.SITE_NAME as SITE6_15_1_, visitor0_.VISITOR_LOGIN_ID as VISITOR7_15_1_, visitor0_.VISITOR_NAME as VISITOR8_15_1_, visitor0_.VISITOR_PASSWORD as VISITOR9_15_1_, visitorpre1_.VISITOR_UID as VISITOR4_3_, visitorpre1_.VISITOR_PREFERENCE_ID as VISITOR1_3_, visitorpre1_.PREFERENCE_CODE as formula5_3_, visitorpre1_.VISITOR_PREFERENCE_ID as VISITOR1_17_0_, visitorpre1_.PREFERE
Trx read view will not see trx with id >= 34A00ED8A, sees < 34A00E973
—TRANSACTION 34A00ED88, ACTIVE 0 sec, process no 22145, OS thread id 1269856592 estimating records in index range
mysql tables in use 2, locked 0
MySQL thread id 21454, query id 1610311 172.24.0.2 visitor statistics
select visitor0_.VISITOR_UID as VISITOR1_15_1_, visitor0_.VISITOR_ALIAS as VISITOR2_15_1_, visitor0_.DFLT_LOCATION as DFLT3_15_1_, visitor0_.DFLT_REGION as DFLT4_15_1_, visitor0_.PASSWORD_EXPIRATION as PASSWORD5_15_1_, visitor0_.SITE_NAME as SITE6_15_1_, visitor0_.VISITOR_LOGIN_ID as VISITOR7_15_1_, visitor0_.VISITOR_NAME as VISITOR8_15_1_, visitor0_.VISITOR_PASSWORD as VISITOR9_15_1_, visitorpre1_.VISITOR_UID as VISITOR4_3_, visitorpre1_.VISITOR_PREFERENCE_ID as VISITOR1_3_, visitorpre1_.PREFERENCE_CODE as formula5_3_, visitorpre1_.VISITOR_PREFERENCE_ID as VISITOR1_17_0_, visitorpre1_.PREFERE
Trx read view will not see trx with id >= 34A00ED89, sees < 34A00E973
—TRANSACTION 34A00ED82, ACTIVE 0 sec, process no 22145, OS thread id 1216608592 inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1216, 1 row lock(s), undo log entries 1
MySQL thread id 21597, query id 1610272 172.24.0.2 visitor update
insert into SAVED_SEARCH (ENCODED_PROPERTIES, NAME, NOTE, NTF_DISABLED_TIMESTAMP, NTF_FREQUENCY, SEARCH_TYPE, USAGE_TIMESTAMP, VISITOR_UID, SEARCH_ID) values (’{“maxPrice”:“350”,“minPrice”:“50”,“propertyType”: “house”,“where”:“Mawson Lakes, SA 5095”,“channel”:“rent”,“listingType”:“rent”,“searchView “:“list”,“domain”:“www.realestate.com.au”,“preferredState “:“SA”,“userWhere”:“mawson lakes, sa 5095;”,“resolvedLocations”:”|Mawson Lakes, SA 5095|”,“resolvedLocationCodes”:”|S-5891|”,"resolvedSurroundingLocationCodes “:” |S-5888|S-5889|S-5900|S-5901|S-5902|S-5892|S-5903|S-13259|S- 5894| "}’, 'recent search
TABLE LOCK table rea.saved_search trx id 34A00ED82 lock mode IX
TABLE LOCK table rea.visitor trx id 34A00ED82 lock mode IS
RECORD LOCKS space id 263 page no 546497 n bits 344 index PRIMARY of table rea.visitor trx id 34A00ED82 lock mode S locks rec but not gap
—TRANSACTION 34A00ED81, ACTIVE 0 sec, process no 22145, OS thread id 1224862032 inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 368, 1 row lock(s), undo log entries 2
MySQL thread id 21877, query id 1610274 172.24.0.2 visitor update
insert into VISITOR_PREFERENCE (PREFERENCE_CODE, PREFERENCE_VALUE, VISITOR_UID) values (‘locale’, ‘en_AU’, ‘ff8081812d002d39012d6f0aac89567f’)
TABLE LOCK table rea.visitor trx id 34A00ED81 lock mode IX
TABLE LOCK table rea.visitor_preference trx id 34A00ED81 lock mode IX
RECORD LOCKS space id 263 page no 240798 n bits 120 index PRIMARY of table rea.visitor trx id 34A00ED81 lock_mode X locks rec but not gap
—TRANSACTION 34A00ED7F, ACTIVE 0 sec, process no 22145, OS thread id 1262668112 estimating records in index range
mysql tables in use 2, locked 0
MySQL thread id 21880, query id 1610266 172.24.0.2 visitor statistics
select visitor0_.VISITOR_UID as VISITOR1_15_1_, visitor0_.VISITOR_ALIAS as VISITOR2_15_1_, visitor0_.DFLT_LOCATION as DFLT3_15_1_, visitor0_.DFLT_REGION as DFLT4_15_1_, visitor0_.PASSWORD_EXPIRATION as PASSWORD5_15_1_, visitor0_.SITE_NAME as SITE6_15_1_, visitor0_.VISITOR_LOGIN_ID as VISITOR7_15_1_, visitor0_.VISITOR_NAME as VISITOR8_15_1_, visitor0_.VISITOR_PASSWORD as VISITOR9_15_1_, visitorpre1_.VISITOR_UID as VISITOR4_3_, visitorpre1_.VISITOR_PREFERENCE_ID as VISITOR1_3_, visitorpre1_.PREFERENCE_CODE as formula5_3_, visitorpre1_.VISITOR_PREFERENCE_ID as VISITOR1_17_0_, visitorpre1_.PREFERE
Trx read view will not see trx with id >= 34A00ED80, sees < 34A00E973
—TRANSACTION 34A00ED7E, ACTIVE 0 sec, process no 22145, OS thread id 1222199632 updating or deleting
mysql tables in use 1, locked 1
2 lock struct(s), heap size 368, 1 row lock(s), undo log entries 1
MySQL thread id 21725, query id 1610260 172.24.0.2 visitor updating
delete from SAVED_SEARCH where SEARCH_ID=‘ff8081812d543aee012d5b685ef21587’
TABLE LOCK table rea.saved_search trx id 34A00ED7E lock mode IX
RECORD LOCKS space id 261 page no 5291264 n bits 96 index PRIMARY of table rea.saved_search trx id 34A00ED7E lock_mode X locks rec but not gap
—TRANSACTION 34A00ED7B, ACTIVE 0 sec, process no 22145, OS thread id 1232316752 estimating records in index range
mysql tables in use 2, locked 0
MySQL thread id 21382, query id 1610218 172.24.0.2 visitor statistics
select visitor0_.VISITOR_UID as VISITOR1_15_1_, visitor0_.VISITOR_ALIAS as VISITOR2_15_1_, visitor0_.DFLT_LOCATION as DFLT3_15_1_, visitor0_.DFLT_REGION as DFLT4_15_1_, visitor0_.PASSWORD_EXPIRATION as PASSWORD5_15_1_, visitor0_.SITE_NAME as SITE6_15_1_, visitor0_.VISITOR_LOGIN_ID as VISITOR7_15_1_, visitor0_.VISITOR_NAME as VISITOR8_15_1_, visitor0_.VISITOR_PASSWORD as VISITOR9_15_1_, visitorpre1_.VISITOR_UID as VISITOR4_3_, visitorpre1_.VISITOR_PREFERENCE_ID as VISITOR1_3_, visitorpre1_.PREFERENCE_CODE as formula5_3_, visitorpre1_.VISITOR_PREFERENCE_ID as VISITOR1_17_0_, visitorpre1_.PREFERE
Trx read view will not see trx with id >= 34A00ED7C, sees < 34A00E973
—TRANSACTION 34A00ED78, ACTIVE 0 sec, process no 22145, OS thread id 1228323152 estimating records in index range
mysql tables in use 2, locked 0
MySQL thread id 21756, query id 1610206 172.24.0.2 visitor statistics
select visitor0_.VISITOR_UID as VISITOR1_15_1_, visitor0_.VISITOR_ALIAS as VISITOR2_15_1_, visitor0_.DFLT_LOCATION as DFLT3_15_1_, visitor0_.DFLT_REGION as DFLT4_15_1_, visitor0_.PASSWORD_EXPIRATION as PASSWORD5_15_1_, visitor0_.SITE_NAME as SITE6_15_1_, visitor0_.VISITOR_LOGIN_ID as VISITOR7_15_1_, visitor0_.VISITOR_NAME as VISITOR8_15_1_, visitor0_.VISITOR_PASSWORD as VISITOR9_15_1_, visitorpre1_.VISITOR_UID as VISITOR4_3_, visitorpre1_.VISITOR_PREFERENCE_ID as VISITOR1_3_, visitorpre1_.PREFERENCE_CODE as formula5_3_, visitorpre1_.VISITOR_PREFERENCE_ID as VISITOR1_17_0_, visitorpre1_.PREFERE
Trx read view will not see trx with id >= 34A00ED79, sees < 34A00E973
—TRANSACTION 34A00ED74, ACTIVE 0 sec, process no 22145, OS thread id 1277577552 estimating records in index range
mysql tables in use 2, locked 0
MySQL thread id 21469, query id 1610192 172.24.0.2 visitor statistics
select visitor0_.VISITOR_UID as VISITOR1_15_1_, visitor0_.VISITOR_ALIAS as VISITOR2_15_1_, visitor0_.DFLT_LOCATION as DFLT3_15_1_, visitor0_.DFLT_REGION as DFLT4_15_1_, visitor0_.PASSWORD_EXPIRATION as PASSWORD5_15_1_, visitor0_.SITE_NAME as SITE6_15_1_, visitor0_.VISITOR_LOGIN_ID as VISITOR7_15_1_, visitor0_.VISITOR_NAME as VISITOR8_15_1_, visitor0_.VISITOR_PASSWORD as VISITOR9_15_1_, visitorpre1_.VISITOR_UID as VISITOR4_3_, visitorpre1_.VISITOR_PREFERENCE_ID as VISITOR1_3_, visitorpre1_.PREFERENCE_CODE as formula5_3_, visitorpre1_.VISITOR_PREFERENCE_ID as VISITOR1_17_0_, visitorpre1_.PREFERE
Trx read view will not see trx with id >= 34A00ED75, sees < 34A00E973
—TRANSACTION 34A00ED69, ACTIVE 0 sec, process no 22145, OS thread id 1282636112
MySQL thread id 21509, query id 1609886 172.24.0.2 visitor
Trx read view will not see trx with id >= 34A00ED6A, sees < 34A00E973
—TRANSACTION 34A00ED5A, ACTIVE 0 sec, process no 22145, OS thread id 1205160272 estimating records in index range
mysql tables in use 2, locked 0
MySQL thread id 21830, query id 1610102 172.24.0.2 visitor statistics
select visitor0_.VISITOR_UID as VISITOR1_15_1_, visitor0_.VISITOR_ALIAS as VISITOR2_15_1_, visitor0_.DFLT_LOCATION as DFLT3_15_1_, visitor0_.DFLT_REGION as DFLT4_15_1_, visitor0_.PASSWORD_EXPIRATION as PASSWORD5_15_1_, visitor0_.SITE_NAME as SITE6_15_1_, visitor0_.VISITOR_LOGIN_ID as VISITOR7_15_1_, visitor0_.VISITOR_NAME as VISITOR8_15_1_, visitor0_.VISITOR_PASSWORD as VISITOR9_15_1_, visitorpre1_.VISITOR_UID as VISITOR4_3_, visitorpre1_.VISITOR_PREFERENCE_ID as VISITOR1_3_, visitorpre1_.PREFERENCE_CODE as formula5_3_, visitorpre1_.VISITOR_PREFERENCE_ID as VISITOR1_17_0_, visitorpre1_.PREFERE
Trx read view will not see trx with id >= 34A00ED5B, sees < 34A00E973
—TRANSACTION 34A00ED40, ACTIVE 0 sec, process no 22145, OS thread id 1264798032 inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 368, 1 row lock(s), undo log entries 2
MySQL thread id 21532, query id 1610009 172.24.0.2 visitor update
insert into VISITOR_PREFERENCE (PREFERENCE_CODE, PREFERENCE_VALUE, VISITOR_UID) values (‘locale’, ‘en_AU’, ‘ff8081812d002d33012d6f0aac6029e6’)
TABLE LOCK table rea.visitor trx id 34A00ED40 lock mode IX
TABLE LOCK table rea.visitor_preference trx id 34A00ED40 lock mode IX
RECORD LOCKS space id 263 page no 1466350 n bits 216 index PRIMARY of table rea.visitor trx id 34A00ED40 lock_mode X locks rec but not gap
—TRANSACTION 34A00ED2C, ACTIVE 0 sec, process no 22145, OS thread id 1221400912 estimating records in index range
mysql tables in use 2, locked 0
MySQL thread id 21673, query id 1609880 172.24.0.2 visitor statistics
select visitor0_.VISITOR_UID as VISITOR1_15_1_, visitor0_.VISITOR_ALIAS as VISITOR2_15_1_, visitor0_.DFLT_LOCATION as DFLT3_15_1_, visitor0_.DFLT_REGION as DFLT4_15_1_, visitor0_.PASSWORD_EXPIRATION as PASSWORD5_15_1_, visitor0_.SITE_NAME as SITE6_15_1_, visitor0_.VISITOR_LOGIN_ID as VISITOR7_15_1_, visitor0_.VISITOR_NAME as VISITOR8_15_1_, visitor0_.VISITOR_PASSWORD as VISITOR9_15_1_, visitorpre1_.VISITOR_UID as VISITOR4_3_, visitorpre1_.VISITOR_PREFERENCE_ID as VISITOR1_3_, visitorpre1_.PREFERENCE_CODE as formula5_3_, visitorpre1_.VISITOR_PREFERENCE_ID as VISITOR1_17_0_, visitorpre1_.PREFERE
Trx read view will not see trx with id >= 34A00ED2D, sees < 34A00E973
—TRANSACTION 34A00ED27, ACTIVE 0 sec, process no 22145, OS thread id 1255213392 inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 368, 1 row lock(s), undo log entries 1
MySQL thread id 21297, query id 1609847 172.24.0.2 visitor update
insert into VISITOR_PREFERENCE (PREFERENCE_CODE, PREFERENCE_VALUE, VISITOR_UID) values (‘preferred_channel’, ‘buy’, ‘ff8081812d002d1d012d6f0a45660c68’)
TABLE LOCK table rea.visitor_preference trx id 34A00ED27 lock mode IX
TABLE LOCK table rea.visitor trx id 34A00ED27 lock mode IS
RECORD LOCKS space id 263 page no 422456 n bits 184 index PRIMARY of table rea.visitor trx id 34A00ED27 lock mode S locks rec but not gap
—TRANSACTION 34A00ED13, ACTIVE 0 sec, process no 22145, OS thread id 1208355152 starting index read
mysql tables in use 2, locked 0
MySQL thread id 21862, query id 1609515 172.24.0.2 visitor Sending data
select this_.VISITOR_UID as VISITOR1_15_1_, this_.VISITOR_ALIAS as VISITOR2_15_1_, this_.DFLT_LOCATION as DFLT3_15_1_, this_.DFLT_REGION as DFLT4_15_1_, this_.PASSWORD_EXPIRATION as PASSWORD5_15_1_, this_.SITE_NAME as SITE6_15_1_, this_.VISITOR_LOGIN_ID as VISITOR7_15_1_, this_.VISITOR_NAME as VISITOR8_15_1_, this_.VISITOR_PASSWORD as VISITOR9_15_1_, visitorpre2_.VISITOR_UID as VISITOR4_3_, visitorpre2_.VISITOR_PREFERENCE_ID as VISITOR1_3_, visitorpre2_.PREFERENCE_CODE as formula5_3_, visitorpre2_.VISITOR_PREFERENCE_ID as VISITOR1_17_0_, visitorpre2_.PREFERENCE_CODE as PREFERENCE2_17_0_, visit
Trx read view will not see trx with id >= 34A00ED14, sees < 34A00E973
—TRANSACTION 34A00ED12, ACTIVE 0 sec, process no 22145, OS thread id 1212082512 fetching rows
mysql tables in use 1, locked 0
MySQL thread id 21703, query id 1609729 172.24.0.2 visitor Sending data
select savedsearc0_.SEARCH_ID as SEARCH1_23_, savedsearc0_.CREATE_TIMESTAMP as CREATE2_23_, savedsearc0_.ENCODED_PROPERTIES as ENCODED3_23_, savedsearc0_.NAME as NAME23_, savedsearc0_.NOTE as NOTE23_, savedsearc0_.NTF_DISABLED_TIMESTAMP as NTF6_23_, savedsearc0_.NTF_FREQUENCY as NTF7_23_, savedsearc0_.SEARCH_TYPE as SEARCH8_23_, savedsearc0_.USAGE_TIMESTAMP as USAGE9_23_, savedsearc0_.VISITOR_UID as VISITOR10_23_ from SAVED_SEARCH savedsearc0_ where savedsearc0_.VISITOR_UID=‘ff8081812cc4a1f8012ccaa6045e0911’ and search_type=‘recent’
Trx read view will not see trx with id >= 34A00ED13, sees < 34A00E973
—TRANSACTION 34A00ED10, ACTIVE 0 sec, process no 22145, OS thread id 1269590352 fetching rows
mysql tables in use 1, locked 0
MySQL thread id 21729, query id 1609731 172.24.0.2 visitor Sending data
select savedsearc0_.SEARCH_ID as SEARCH1_23_, savedsearc0_.CREATE_TIMESTAMP as CREATE2_23_, savedsearc0_.ENCODED_PROPERTIES as ENCODED3_23_, savedsearc0_.NAME as NAME23_, savedsearc0_.NOTE as NOTE23_, savedsearc0_.NTF_DISABLED_TIMESTAMP as NTF6_23_, savedsearc0_.NTF_FREQUENCY as NTF7_23_, savedsearc0_.SEARCH_TYPE as SEARCH8_23_, savedsearc0_.USAGE_TIMESTAMP as USAGE9_23_, savedsearc0_.VISITOR_UID as VISITOR10_23_ from SAVED_SEARCH savedsearc0_ where savedsearc0_.VISITOR_UID=‘ff8081812ced8a32012cf84e2d5d5946’ and search_type=‘recent’
Trx read view will not see trx with id >= 34A00ED11, sees < 34A00E973
—TRANSACTION 34A00ED0C, ACTIVE 0 sec, process no 22145, OS thread id 1266129232 estimating records in index range
mysql tables in use 2, locked 0
MySQL thread id 21832, query id 1609685 172.24.0.2 visitor statistics
select visitor0_.VISITOR_UID as VISITOR1_15_1_, visitor0_.VISITOR_ALIAS as VISITOR2_15_1_, visitor0_.DFLT_LOCATION as DFLT3_15_1_, visitor0_.DFLT_REGION as DFLT4_15_1_, visitor0_.PASSWORD_EXPIRATION as PASSWORD5_15_1_, visitor0_.SITE_NAME as SITE6_15_1_, visitor0_.VISITOR_LOGIN_ID as VISITOR7_15_1_, visitor0_.VISITOR_NAME as VISITOR8_15_1_, visitor0_.VISITOR_PASSWORD as VISITOR9_15_1_, visitorpre1_.VISITOR_UID as VISITOR4_3_, visitorpre1_.VISITOR_PREFERENCE_ID as VISITOR1_3_, visitorpre1_.PREFERENCE_CODE as formula5_3_, visitorpre1_.VISITOR_PREFERENCE_ID as VISITOR1_17_0_, visitorpre1_.PREFERE
Trx read view will not see trx with id >= 34A00ED0D, sees < 34A00E973
—TRANSACTION 34A00ECE6, ACTIVE 0 sec, process no 22145, OS thread id 1205958992 estimating records in index range
mysql tables in use 2, locked 0
MySQL thread id 21831, query id 1609438 172.24.0.2 visitor statistics
select visitor0_.VISITOR_UID as VISITOR1_15_1_, visitor0_.VISITOR_ALIAS as VISITOR2_15_1_, visitor0_.DFLT_LOCATION as DFLT3_15_1_, visitor0_.DFLT_REGION as DFLT4_15_1_, visitor0_.PASSWORD_EXPIRATION as PASSWORD5_15_1_, visitor0_.SITE_NAME as SITE6_15_1_, visitor0_.VISITOR_LOGIN_ID as VISITOR7_15_1_, visitor0_.VISITOR_NAME as VISITOR8_15_1_, visitor0_.VISITOR_PASSWORD as VISITOR9_15_1_, visitorpre1_.VISITOR_UID as VISITOR4_3_, visitorpre1_.VISITOR_PREFERENCE_ID as VISITOR1_3_, visitorpre1_.PREFERENCE_CODE as formula5_3_, visitorpre1_.VISITOR_PREFERENCE_ID as VISITOR1_17_0_, visitorpre1_.PREFERE
Trx read view will not see trx with id >= 34A00ED2F, sees < 34A00E973
—TRANSACTION 34A00ECDB, ACTIVE 0 sec, process no 22145, OS thread id 1225660752 estimating records in index range
mysql tables in use 2, locked 0
MySQL thread id 21758, query id 1609372 172.24.0.2 visitor st

Hi,

Did you check if box is swapping by chance ? The BP size might be a bit high considering overhead.

The load is fairly IO bound as well see pending reads and the number of row reads/write is not high so you might evaluate your schema to see if you can get better working set fit to memory such as by using clustering or partitioning.

The lock you’re observing is not buffer pool lock but index lock - there is per index RW when entry need to be inserted/updated in the index which may be contention when heavy writes happen to the same table. In this case partitioning the table in question may help as well.

Hi Peter,

Thanks for the reply.
Theres no swapping what so ever. We run one of the highest traffic websites in OZ backed by MySQL. We have two active datacenters and use Akamai DSA to route users to the closest DC.

We run master/master active/active between the two datacenters. The specs of the boxes are as mentioned above DELL M710 blades, 144G RAM (134G buffer pool)
slave-skip-errors=all
innodb_flush_log_at_trx_commit=0
innodb_lock_wait_timeout=50
innodb_flush_method=O_DIRECT
innodb_file_per_table=1
innodb_fast_shutdown=1
innodb_lock_wait_timeout=50
innodb_data_file_path=Datafile1:500M:autoextend
key_buffer=4M
myisam_sort_buffer_size=1M
max_allowed_packet=1M
net_buffer_length=8K
wait_timeout=60
max_connections=5000
thread_cache_size=100
thread_concurrency=64
table_cache=1600
max_heap_table_size=658553K
tmp_table_size=658553K
query_cache_type=0
query_cache_size=0
query_cache_limit=0
join_buffer_size=16463K
sort_buffer_size=16463K
read_buffer_size=16463K
read_rnd_buffer_size=16463K
innodb_additional_mem_pool_size=329276K
innodb_log_file_size=164638K
innodb_log_buffer_size=164638K
innodb_buffer_pool_size=135509184K

We use xfs filesystem with nobarrier,noatime and use deadline scheduler.

Thanks for your post but whats confusing is that we have two boxes - one at each DC - yet one is showing the above innodb status output (and higher IO wait) even though the database traffic is lower, then the other DC.

So I’m not sure if its something hardware (failing disk / raid memory etc etc) thats not showing up.

We use Foreign keys so partitioning is not an option - I’m just confused why its fine at one DC (and doing more load) whereas at the other DC, on the exact same tin, the exact same config, we’re having these issues.

So I guess you’re saying that the innodb output is just contention which is possibly symptom of an underlining IO issue.

Have you ever come across fragmentation with xfs causing these types of issues?
I see

xfs_db -c frag -r /dev/sda3

actual 88906, ideal 212, fragmentation factor 99.76%

Though I see the same level of fragmentation at the other datacenter yet without the performance problems.

I’ll also post in a snaphost of innodb status/global status/processlist on the ‘good’ and ‘bad’ boxes next