# Possible performance problem with Percona Mysql **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [January 10, 2011, 4:42pm UTC](https://forums.percona.com/t/possible-performance-problem-with-percona-mysql/1590 "2011-01-10T16:42:41Z")\ **Posts on this page:** 3\ **Page:** 1

**Author:** ![tnhtnh](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/tnhtnh/32/846_2.png) [@tnhtnh](https://forums.percona.com/u/tnhtnh)\ **Post date:** [January 10, 2011, 4:42pm UTC](https://forums.percona.com/t/possible-performance-problem-with-percona-mysql/1590/1 "2011-01-10T16:42:41Z")

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](mailto: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 ---

**Author:** ![Peter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/peter/32/2_2.png) [@Peter](https://forums.percona.com/u/Peter)\ **Post date:** [January 10, 2011, 7:01pm UTC](https://forums.percona.com/t/possible-performance-problem-with-percona-mysql/1590/2 "2011-01-10T19:01:32Z")

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. ---

**Author:** ![tnhtnh](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/tnhtnh/32/846_2.png) [@tnhtnh](https://forums.percona.com/u/tnhtnh)\ **Post date:** [January 10, 2011, 7:24pm UTC](https://forums.percona.com/t/possible-performance-problem-with-percona-mysql/1590/3 "2011-01-10T19:24:44Z")

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