GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Possible performance problem with Percona Mysql

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

  • 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":"Coromande l Valley, SA 5051","channel":"buy","listingType":"buy","searchV iew ":"list","domain":"www.realestate.com.au","preferr edState ":"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 66663830383138313263613061633637303132636136326265 3630643161 6235; asc ff8081812ca0ac67012ca62be60d1ab5;;
    1: len 32; hex 66663830383138313264303133393633303132643665656166 3435663333 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 66663830383138313263613061633637303132636136343339 3030313064 ; 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 < 349BF3EBB undo n < 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","searc hView ":"list","domain":"www.realestate.com.au","preferr edState ":"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='ff8081812cc4a1f8012ccaa6 045e0911' 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='ff8081812ced8a32012cf84e 2d5d5946' 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

  • #2
    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.

    Comment


    • #3
      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

      Comment


      • #4
        Find a snapshot of the good server running

        while true; do echo "show engine innodb status\G" | mysql; echo "show global status" | mysql; echo "show full processlist" | mysql; sleep 60; done

        Comment


        • #5
          Find a snapshot of the bad server running

          while true; do echo "show engine innodb status\G" | mysql; echo "show global status" | mysql; echo "show full processlist" | mysql; sleep 60; done

          Comment


          • #6
            Hi,

            I'm sorry I can't get t much details here. If you have 2 servers with same workload and they show different behavior it is good to investigate things in details.
            One of the easy solution if you're larger MySQL shop is to swap the "problematic" server with different one and see if problem persist. This can be quickest troubleshooting. We also surely can help you to find the cause of the issue on consulting basics.

            Comment


            • #7
              Hi Peter

              Yes, appreciate the complexities meaning it cannot be quickly solved by blog post - I'm trying to get a business case to give you guys a call for support going forward. Thanks for your input.

              Kind regards,

              Trent Hornibrook
              Senior Systems Engineer

              Comment


              • #8
                Trent,

                Thank you. Feel free to contact our sales team http://www.percona.com/contact/sales/ and they may be able to help you to get the business case. We also will be able to offer a technical pre-sales call which often help "business people" get a confidence with us.

                Comment

                Working...
                X