100128 15:26:58 mysqld_safe Starting mysqld daemon with databases from /home/data/mysql/ 100128 15:26:59 [Note] Plugin 'FEDERATED' is disabled. InnoDB: The InnoDB memory heap is disabled InnoDB: Mutexes and rw_locks use GCC atomic builtins 100128 15:26:59 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 256 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 200 100128 15:27:02 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 256 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 200 100128 15:27:05 InnoDB: highest supported file format is Barracuda. InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 100128 15:27:05 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 100128 15:27:09 InnoDB Plugin 1.0.6-9 started; log sequence number 141008948236 100128 15:27:09 [Warning] Storage engine 'SPHINX' has conflicting typecode. Assigning value 42. 100128 15:27:09 [Note] Event Scheduler: Loaded 0 events 100128 15:27:09 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.1.42-percona-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL), XtraDB 9, Revision 50 100128 15:29:05 [Note] /usr/sbin/mysqld: Normal shutdown 100128 15:29:05 [Note] Event Scheduler: Purging the queue. 0 events 100128 15:29:05 InnoDB: Starting shutdown... 100128 15:29:11 InnoDB: Shutdown completed; log sequence number 141247837589 100128 15:29:11 [Note] /usr/sbin/mysqld: Shutdown complete 100128 15:29:11 mysqld_safe mysqld from pid file /home/data/mysql//XXX.XXX.pid ended 100128 16:00:30 mysqld_safe Starting mysqld daemon with databases from /home/data/mysql/ 100128 16:00:31 [Note] Plugin 'FEDERATED' is disabled. InnoDB: The InnoDB memory heap is disabled InnoDB: Mutexes and rw_locks use GCC atomic builtins 100128 16:00:32 InnoDB: highest supported file format is Barracuda. 100128 16:00:34 InnoDB Plugin 1.0.6-9 started; log sequence number 141247837589 100128 16:00:34 [Warning] Storage engine 'SPHINX' has conflicting typecode. Assigning value 42. 100128 16:00:34 [Note] Event Scheduler: Loaded 0 events 100128 16:00:34 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.1.42-percona-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL), XtraDB 9, Revision 50 100128 16:03:03 [Note] /usr/sbin/mysqld: Normal shutdown 100128 16:03:03 [Note] Event Scheduler: Purging the queue. 0 events 100128 16:03:05 InnoDB: Starting shutdown... 100128 16:03:15 InnoDB: Shutdown completed; log sequence number 141531245736 100128 16:03:15 [Note] /usr/sbin/mysqld: Shutdown complete 100128 16:03:15 mysqld_safe mysqld from pid file /home/data/mysql//XXX.XXX.pid ended 100128 16:07:25 mysqld_safe Starting mysqld daemon with databases from /home/data/mysql/ 100128 16:07:26 [Note] Plugin 'FEDERATED' is disabled. InnoDB: The InnoDB memory heap is disabled InnoDB: Mutexes and rw_locks use GCC atomic builtins 100128 16:07:26 InnoDB: highest supported file format is Barracuda. 100128 16:07:28 InnoDB Plugin 1.0.6-9 started; log sequence number 141531245736 100128 16:07:28 [Warning] Storage engine 'SPHINX' has conflicting typecode. Assigning value 42. 100128 16:07:29 [Note] Event Scheduler: Loaded 0 events 100128 16:07:29 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.1.42-percona-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL), XtraDB 9, Revision 50 InnoDB: Warning: a long semaphore wait: --Thread 1211844928 has waited at row/row0purge.c line 541 for 241.00 seconds the semaphore: S-lock on RW-latch at 0xd3e640 '&dict_operation_lock' a writer (thread id 1102022976) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row/row0purge.c line 541 Last time write locked in file row/row0mysql.c line 3043 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0 ===================================== 100128 17:13:52 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 19 seconds ---------- BACKGROUND THREAD ---------- srv_master_thread loops: 2154 1_second, 2154 sleeps, 215 10_second, 110 background, 110 flush srv_master_thread log flush and writes: 2433 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 6204, signal count 5765 --Thread 1211844928 has waited at row/row0purge.c line 541 for 256.00 seconds the semaphore: S-lock on RW-latch at 0xd3e640 '&dict_operation_lock' a writer (thread id 1102022976) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row/row0purge.c line 541 Last time write locked in file row/row0mysql.c line 3043 Mutex spin waits 8548, rounds 165763, OS waits 4752 RW-shared spins 508, OS waits 447; RW-excl spins 0, OS waits 770 Spin rounds per wait: 19.39 mutex, 27.46 RW-shared, 30021.00 RW-excl -------- 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 22595 OS file reads, 30967 OS file writes, 11721 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 5697, seg size 5699, 0 inserts, 0 merged recs, 0 merges Hash table size 12749951, node heap has 1184 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 148242298319 Log flushed up to 148242096951 Last checkpoint at 148242096951 Max checkpoint age 434154333 Checkpoint age target 420587011 Modified age 201368 Checkpoint age 201368 0 pending log writes, 0 pending chkp writes 5526 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 6602883072; in additional pool allocated 0 Internal hash tables (constant factor + variable factor) Adaptive hash index 121402472 (101999608 + 19402864) Page hash 6375736 Dictionary cache 29002304 (25501328 + 3500976) File system 301456 (82672 + 218784) Lock system 15941544 (15938152 + 3392) Recovery system 0 (0 + 0) Threads 407576 (406936 + 640) Dictionary memory allocated 3500976 Buffer pool size 393215 Buffer pool size, bytes 6442434560 Free buffers 98423 Database pages 200603 Old database pages 74030 Modified db pages 14 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 85841, not young 0 0.11 youngs/s, 0.00 non-youngs/s Pages read 99102, created 191097, written 283898 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s LRU len: 200603, unzip_LRU len: 188468 I/O sum[18446744073709551615]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread process no. 3025, id 1243314496, state: waiting for server activity Number of rows inserted 6199212, updated 0, deleted 0, read 6199212 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ------------ TRANSACTIONS ------------ Trx id counter D27A Purge done for trx's n:o < D278 undo n:o < 0 History list length 3 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION D274, not started, process no 3025, OS thread id 1102022976 MySQL thread id 2, query id 2460 localhost root rename result table optimize table test_table ---TRANSACTION D279, ACTIVE 260 sec, process no 3025, OS thread id 1102022976 dropping table 11 lock struct(s), heap size 3024, 7 row lock(s), undo log entries 1 MySQL thread id 2, query id 2460 localhost root rename result table optimize table test_table TABLE LOCK table `SYS_TABLES` trx id D279 lock mode IS RECORD LOCKS space id 0 page no 215 n bits 256 index `CLUST_IND` of table `SYS_TABLES` trx id D279 lock mode S RECORD LOCKS space id 0 page no 213 n bits 248 index `CLUST_IND` of table `SYS_TABLES` trx id D279 lock mode S TABLE LOCK table `SYS_FOREIGN` trx id D279 lock mode IS RECORD LOCKS space id 0 page no 47 n bits 312 index `FOR_IND` of table `SYS_FOREIGN` trx id D279 lock mode S TABLE LOCK table `SYS_INDEXES` trx id D279 lock mode IS RECORD LOCKS space id 0 page no 242 n bits 280 index `CLUST_IND` of table `SYS_INDEXES` trx id D279 lock mode S TABLE LOCK table `SYS_FIELDS` trx id D279 lock mode IX RECORD LOCKS space id 0 page no 246 n bits 184 index `CLUST_IND` of table `SYS_FIELDS` trx id D279 lock_mode X TABLE LOCK table `SYS_INDEXES` trx id D279 lock mode IX TOO LOCKS PRINTED FOR THIS TRX: SUPPRESSING FURTHER PRINTS ---------------------------- END OF INNODB MONITOR OUTPUT ============================ InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Warning: a long semaphore wait: --Thread 1211844928 has waited at row/row0purge.c line 541 for 272.00 seconds the semaphore: S-lock on RW-latch at 0xd3e640 '&dict_operation_lock' a writer (thread id 1102022976) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row/row0purge.c line 541 Last time write locked in file row/row0mysql.c line 3043 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0 ===================================== 100128 17:14:08 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 16 seconds ---------- BACKGROUND THREAD ---------- srv_master_thread loops: 2154 1_second, 2154 sleeps, 215 10_second, 110 background, 110 flush srv_master_thread log flush and writes: 2433 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 6204, signal count 5765 --Thread 1211844928 has waited at row/row0purge.c line 541 for 272.00 seconds the semaphore: S-lock on RW-latch at 0xd3e640 '&dict_operation_lock' a writer (thread id 1102022976) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row/row0purge.c line 541 Last time write locked in file row/row0mysql.c line 3043 Mutex spin waits 8548, rounds 165763, OS waits 4752 RW-shared spins 508, OS waits 447; RW-excl spins 0, OS waits 770 Spin rounds per wait: 19.39 mutex, 27.46 RW-shared, 30021.00 RW-excl -------- 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 22595 OS file reads, 30967 OS file writes, 11721 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 5697, seg size 5699, 0 inserts, 0 merged recs, 0 merges Hash table size 12749951, node heap has 1184 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 148242372342 Log flushed up to 148242096951 Last checkpoint at 148242096951 Max checkpoint age 434154333 Checkpoint age target 420587011 Modified age 275391 Checkpoint age 275391 0 pending log writes, 0 pending chkp writes 5526 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 6602883072; in additional pool allocated 0 Internal hash tables (constant factor + variable factor) Adaptive hash index 121402472 (101999608 + 19402864) Page hash 6375736 Dictionary cache 29002304 (25501328 + 3500976) File system 301456 (82672 + 218784) Lock system 15941544 (15938152 + 3392) Recovery system 0 (0 + 0) Threads 407576 (406936 + 640) Dictionary memory allocated 3500976 Buffer pool size 393215 Buffer pool size, bytes 6442434560 Free buffers 98423 Database pages 200603 Old database pages 74030 Modified db pages 16 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 85842, not young 0 0.06 youngs/s, 0.00 non-youngs/s Pages read 99102, created 191097, written 283898 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s LRU len: 200603, unzip_LRU len: 188468 I/O sum[18446744073709551615]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread process no. 3025, id 1243314496, state: waiting for server activity Number of rows inserted 6199212, updated 0, deleted 0, read 6199212 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ------------ TRANSACTIONS ------------ Trx id counter D27A Purge done for trx's n:o < D278 undo n:o < 0 History list length 3 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION D274, not started, process no 3025, OS thread id 1102022976 MySQL thread id 2, query id 2460 localhost root rename result table optimize table test_table ---TRANSACTION D279, ACTIVE 276 sec, process no 3025, OS thread id 1102022976 dropping table 11 lock struct(s), heap size 3024, 7 row lock(s), undo log entries 1 MySQL thread id 2, query id 2460 localhost root rename result table optimize table test_table TABLE LOCK table `SYS_TABLES` trx id D279 lock mode IS RECORD LOCKS space id 0 page no 215 n bits 256 index `CLUST_IND` of table `SYS_TABLES` trx id D279 lock mode S RECORD LOCKS space id 0 page no 213 n bits 248 index `CLUST_IND` of table `SYS_TABLES` trx id D279 lock mode S TABLE LOCK table `SYS_FOREIGN` trx id D279 lock mode IS RECORD LOCKS space id 0 page no 47 n bits 312 index `FOR_IND` of table `SYS_FOREIGN` trx id D279 lock mode S TABLE LOCK table `SYS_INDEXES` trx id D279 lock mode IS RECORD LOCKS space id 0 page no 242 n bits 280 index `CLUST_IND` of table `SYS_INDEXES` trx id D279 lock mode S TABLE LOCK table `SYS_FIELDS` trx id D279 lock mode IX RECORD LOCKS space id 0 page no 246 n bits 184 index `CLUST_IND` of table `SYS_FIELDS` trx id D279 lock_mode X TABLE LOCK table `SYS_INDEXES` trx id D279 lock mode IX TOO LOCKS PRINTED FOR THIS TRX: SUPPRESSING FURTHER PRINTS ---------------------------- END OF INNODB MONITOR OUTPUT ============================ ===================================== 100128 17:14:24 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 16 seconds ---------- BACKGROUND THREAD ---------- srv_master_thread loops: 2154 1_second, 2154 sleeps, 215 10_second, 110 background, 110 flush srv_master_thread log flush and writes: 2433 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 6204, signal count 5765 --Thread 1211844928 has waited at row/row0purge.c line 541 for 288.00 seconds the semaphore: S-lock on RW-latch at 0xd3e640 '&dict_operation_lock' a writer (thread id 1102022976) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row/row0purge.c line 541 Last time write locked in file row/row0mysql.c line 3043 Mutex spin waits 8548, rounds 165763, OS waits 4752 RW-shared spins 508, OS waits 447; RW-excl spins 0, OS waits 770 Spin rounds per wait: 19.39 mutex, 27.46 RW-shared, 30021.00 RW-excl -------- 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 22595 OS file reads, 30967 OS file writes, 11721 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 5697, seg size 5699, 0 inserts, 0 merged recs, 0 merges Hash table size 12749951, node heap has 605 buffer(s) 0.00 hash searches/s, 0.37 non-hash searches/s --- LOG --- Log sequence number 148242411755 Log flushed up to 148242096951 Last checkpoint at 148242096951 Max checkpoint age 434154333 Checkpoint age target 420587011 Modified age 314804 Checkpoint age 314804 0 pending log writes, 0 pending chkp writes 5526 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 6602883072; in additional pool allocated 0 Internal hash tables (constant factor + variable factor) Adaptive hash index 111916136 (101999608 + 9916528) Page hash 6375736 Dictionary cache 29002304 (25501328 + 3500976) File system 301456 (82672 + 218784) Lock system 15941544 (15938152 + 3392) Recovery system 0 (0 + 0) Threads 407576 (406936 + 640) Dictionary memory allocated 3500976 Buffer pool size 393215 Buffer pool size, bytes 6442434560 Free buffers 99002 Database pages 200603 Old database pages 74030 Modified db pages 21 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 87533, not young 0 105.68 youngs/s, 0.00 non-youngs/s Pages read 99102, created 191097, written 283898 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 328 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s LRU len: 200603, unzip_LRU len: 188468 I/O sum[18446744073709551615]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread process no. 3025, id 1243314496, state: waiting for server activity Number of rows inserted 6199212, updated 0, deleted 0, read 6199212 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ------------ TRANSACTIONS ------------ Trx id counter D27A Purge done for trx's n:o < D278 undo n:o < 0 History list length 3 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION D274, not started, process no 3025, OS thread id 1102022976 MySQL thread id 2, query id 2460 localhost root rename result table optimize table test_table ---TRANSACTION D279, ACTIVE 292 sec, process no 3025, OS thread id 1102022976 dropping table 11 lock struct(s), heap size 3024, 11 row lock(s), undo log entries 5 MySQL thread id 2, query id 2460 localhost root rename result table optimize table test_table TABLE LOCK table `SYS_TABLES` trx id D279 lock mode IS RECORD LOCKS space id 0 page no 215 n bits 256 index `CLUST_IND` of table `SYS_TABLES` trx id D279 lock mode S RECORD LOCKS space id 0 page no 213 n bits 248 index `CLUST_IND` of table `SYS_TABLES` trx id D279 lock mode S TABLE LOCK table `SYS_FOREIGN` trx id D279 lock mode IS RECORD LOCKS space id 0 page no 47 n bits 312 index `FOR_IND` of table `SYS_FOREIGN` trx id D279 lock mode S TABLE LOCK table `SYS_INDEXES` trx id D279 lock mode IS RECORD LOCKS space id 0 page no 242 n bits 280 index `CLUST_IND` of table `SYS_INDEXES` trx id D279 lock mode S TABLE LOCK table `SYS_FIELDS` trx id D279 lock mode IX RECORD LOCKS space id 0 page no 246 n bits 184 index `CLUST_IND` of table `SYS_FIELDS` trx id D279 lock_mode X TABLE LOCK table `SYS_INDEXES` trx id D279 lock mode IX TOO LOCKS PRINTED FOR THIS TRX: SUPPRESSING FURTHER PRINTS ---------------------------- END OF INNODB MONITOR OUTPUT ============================ InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Warning: a long semaphore wait: --Thread 1211844928 has waited at row/row0purge.c line 541 for 303.00 seconds the semaphore: S-lock on RW-latch at 0xd3e640 '&dict_operation_lock' a writer (thread id 1102022976) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row/row0purge.c line 541 Last time write locked in file row/row0mysql.c line 3043 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0 ===================================== 100128 17:14:40 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 16 seconds ---------- BACKGROUND THREAD ---------- srv_master_thread loops: 2154 1_second, 2154 sleeps, 215 10_second, 110 background, 110 flush srv_master_thread log flush and writes: 2433 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 6204, signal count 5765 --Thread 1211844928 has waited at row/row0purge.c line 541 for 304.00 seconds the semaphore: S-lock on RW-latch at 0xd3e640 '&dict_operation_lock' a writer (thread id 1102022976) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row/row0purge.c line 541 Last time write locked in file row/row0mysql.c line 3043 Mutex spin waits 8548, rounds 165763, OS waits 4752 RW-shared spins 508, OS waits 447; RW-excl spins 0, OS waits 770 Spin rounds per wait: 19.39 mutex, 27.46 RW-shared, 30021.00 RW-excl -------- 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 22595 OS file reads, 30967 OS file writes, 11721 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 5697, seg size 5699, 0 inserts, 0 merged recs, 0 merges Hash table size 12749951, node heap has 597 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 148242418871 Log flushed up to 148242096951 Last checkpoint at 148242096951 Max checkpoint age 434154333 Checkpoint age target 420587011 Modified age 321920 Checkpoint age 321920 0 pending log writes, 0 pending chkp writes 5526 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 6602883072; in additional pool allocated 0 Internal hash tables (constant factor + variable factor) Adaptive hash index 111785064 (101999608 + 9785456) Page hash 6375736 Dictionary cache 29002304 (25501328 + 3500976) File system 301456 (82672 + 218784) Lock system 15941544 (15938152 + 3392) Recovery system 0 (0 + 0) Threads 407576 (406936 + 640) Dictionary memory allocated 3500976 Buffer pool size 393215 Buffer pool size, bytes 6442434560 Free buffers 99010 Database pages 200603 Old database pages 74030 Modified db pages 21 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 89732, not young 0 137.43 youngs/s, 0.00 non-youngs/s Pages read 99102, created 191097, written 283898 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 870 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s LRU len: 200603, unzip_LRU len: 188468 I/O sum[18446744073709551615]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread process no. 3025, id 1243314496, state: waiting for server activity Number of rows inserted 6199212, updated 0, deleted 0, read 6199212 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ------------ TRANSACTIONS ------------ Trx id counter D27A Purge done for trx's n:o < D278 undo n:o < 0 History list length 3 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION D274, not started, process no 3025, OS thread id 1102022976 MySQL thread id 2, query id 2460 localhost root rename result table optimize table test_table ---TRANSACTION D279, ACTIVE 308 sec, process no 3025, OS thread id 1102022976 dropping table 11 lock struct(s), heap size 3024, 11 row lock(s), undo log entries 5 MySQL thread id 2, query id 2460 localhost root rename result table optimize table test_table TABLE LOCK table `SYS_TABLES` trx id D279 lock mode IS RECORD LOCKS space id 0 page no 215 n bits 256 index `CLUST_IND` of table `SYS_TABLES` trx id D279 lock mode S RECORD LOCKS space id 0 page no 213 n bits 248 index `CLUST_IND` of table `SYS_TABLES` trx id D279 lock mode S TABLE LOCK table `SYS_FOREIGN` trx id D279 lock mode IS RECORD LOCKS space id 0 page no 47 n bits 312 index `FOR_IND` of table `SYS_FOREIGN` trx id D279 lock mode S TABLE LOCK table `SYS_INDEXES` trx id D279 lock mode IS RECORD LOCKS space id 0 page no 242 n bits 280 index `CLUST_IND` of table `SYS_INDEXES` trx id D279 lock mode S TABLE LOCK table `SYS_FIELDS` trx id D279 lock mode IX RECORD LOCKS space id 0 page no 246 n bits 184 index `CLUST_IND` of table `SYS_FIELDS` trx id D279 lock_mode X TABLE LOCK table `SYS_INDEXES` trx id D279 lock mode IX TOO LOCKS PRINTED FOR THIS TRX: SUPPRESSING FURTHER PRINTS ---------------------------- END OF INNODB MONITOR OUTPUT ============================ ===================================== 100128 17:14:56 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 16 seconds ---------- BACKGROUND THREAD ---------- srv_master_thread loops: 2154 1_second, 2154 sleeps, 215 10_second, 110 background, 110 flush srv_master_thread log flush and writes: 2433 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 6204, signal count 5765 --Thread 1211844928 has waited at row/row0purge.c line 541 for 320.00 seconds the semaphore: S-lock on RW-latch at 0xd3e640 '&dict_operation_lock' a writer (thread id 1102022976) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row/row0purge.c line 541 Last time write locked in file row/row0mysql.c line 3043 Mutex spin waits 8548, rounds 165763, OS waits 4752 RW-shared spins 508, OS waits 447; RW-excl spins 0, OS waits 770 Spin rounds per wait: 19.39 mutex, 27.46 RW-shared, 30021.00 RW-excl -------- 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 22595 OS file reads, 30967 OS file writes, 11721 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 5697, seg size 5699, 0 inserts, 0 merged recs, 0 merges Hash table size 12749951, node heap has 594 buffer(s) 0.00 hash searches/s, 0.19 non-hash searches/s --- LOG --- Log sequence number 148242429236 Log flushed up to 148242096951 Last checkpoint at 148242096951 Max checkpoint age 434154333 Checkpoint age target 420587011 Modified age 332285 Checkpoint age 332285 0 pending log writes, 0 pending chkp writes 5526 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 6602883072; in additional pool allocated 0 Internal hash tables (constant factor + variable factor) Adaptive hash index 111735912 (101999608 + 9736304) Page hash 6375736 Dictionary cache 29002304 (25501328 + 3500976) File system 301456 (82672 + 218784) Lock system 15941544 (15938152 + 3392) Recovery system 0 (0 + 0) Threads 407576 (406936 + 640) Dictionary memory allocated 3500976 Buffer pool size 393215 Buffer pool size, bytes 6442434560 Free buffers 99013 Database pages 200603 Old database pages 74030 Modified db pages 22 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 91947, not young 0 138.43 youngs/s, 0.00 non-youngs/s Pages read 99102, created 191097, written 283898 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 786 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s LRU len: 200603, unzip_LRU len: 188468 I/O sum[18446744073709551615]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread process no. 3025, id 1243314496, state: waiting for server activity Number of rows inserted 6199212, updated 0, deleted 0, read 6199212 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ------------ TRANSACTIONS ------------ Trx id counter D27A Purge done for trx's n:o < D278 undo n:o < 0 History list length 3 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION D274, not started, process no 3025, OS thread id 1102022976 MySQL thread id 2, query id 2460 localhost root rename result table optimize table test_table ---TRANSACTION D279, ACTIVE 324 sec, process no 3025, OS thread id 1102022976 dropping table 11 lock struct(s), heap size 3024, 13 row lock(s), undo log entries 7 MySQL thread id 2, query id 2460 localhost root rename result table optimize table test_table TABLE LOCK table `SYS_TABLES` trx id D279 lock mode IS RECORD LOCKS space id 0 page no 215 n bits 256 index `CLUST_IND` of table `SYS_TABLES` trx id D279 lock mode S RECORD LOCKS space id 0 page no 213 n bits 248 index `CLUST_IND` of table `SYS_TABLES` trx id D279 lock mode S TABLE LOCK table `SYS_FOREIGN` trx id D279 lock mode IS RECORD LOCKS space id 0 page no 47 n bits 312 index `FOR_IND` of table `SYS_FOREIGN` trx id D279 lock mode S TABLE LOCK table `SYS_INDEXES` trx id D279 lock mode IS RECORD LOCKS space id 0 page no 242 n bits 280 index `CLUST_IND` of table `SYS_INDEXES` trx id D279 lock mode S TABLE LOCK table `SYS_FIELDS` trx id D279 lock mode IX RECORD LOCKS space id 0 page no 246 n bits 184 index `CLUST_IND` of table `SYS_FIELDS` trx id D279 lock_mode X TABLE LOCK table `SYS_INDEXES` trx id D279 lock mode IX TOO LOCKS PRINTED FOR THIS TRX: SUPPRESSING FURTHER PRINTS ---------------------------- END OF INNODB MONITOR OUTPUT ============================ InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Warning: a long semaphore wait: --Thread 1211844928 has waited at row/row0purge.c line 541 for 334.00 seconds the semaphore: S-lock on RW-latch at 0xd3e640 '&dict_operation_lock' a writer (thread id 1102022976) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row/row0purge.c line 541 Last time write locked in file row/row0mysql.c line 3043 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0 ===================================== 100128 17:15:12 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 16 seconds ---------- BACKGROUND THREAD ---------- srv_master_thread loops: 2154 1_second, 2154 sleeps, 215 10_second, 110 background, 110 flush srv_master_thread log flush and writes: 2433 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 6204, signal count 5765 --Thread 1211844928 has waited at row/row0purge.c line 541 for 336.00 seconds the semaphore: S-lock on RW-latch at 0xd3e640 '&dict_operation_lock' a writer (thread id 1102022976) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row/row0purge.c line 541 Last time write locked in file row/row0mysql.c line 3043 Mutex spin waits 8548, rounds 165763, OS waits 4752 RW-shared spins 508, OS waits 447; RW-excl spins 0, OS waits 770 Spin rounds per wait: 19.39 mutex, 27.46 RW-shared, 30021.00 RW-excl -------- 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 22595 OS file reads, 30967 OS file writes, 11721 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 5697, seg size 5699, 0 inserts, 0 merged recs, 0 merges Hash table size 12749951, node heap has 594 buffer(s) 0.00 hash searches/s, 0.50 non-hash searches/s --- LOG --- Log sequence number 148242433037 Log flushed up to 148242096951 Last checkpoint at 148242096951 Max checkpoint age 434154333 Checkpoint age target 420587011 Modified age 336086 Checkpoint age 336086 0 pending log writes, 0 pending chkp writes 5526 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 6602883072; in additional pool allocated 0 Internal hash tables (constant factor + variable factor) Adaptive hash index 111735912 (101999608 + 9736304) Page hash 6375736 Dictionary cache 28996104 (25501328 + 3494776) File system 301456 (82672 + 218784) Lock system 15941544 (15938152 + 3392) Recovery system 0 (0 + 0) Threads 407576 (406936 + 640) Dictionary memory allocated 3494776 InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Warning: a long semaphore wait: --Thread 1211844928 has waited at row/row0purge.c line 541 for 365.00 seconds the semaphore: S-lock on RW-latch at 0xd3e640 '&dict_operation_lock' a writer (thread id 1102022976) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row/row0purge.c line 541 Last time write locked in file row/row0mysql.c line 3043 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0 InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Warning: a long semaphore wait: --Thread 1211844928 has waited at row/row0purge.c line 541 for 396.00 seconds the semaphore: S-lock on RW-latch at 0xd3e640 '&dict_operation_lock' a writer (thread id 1102022976) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row/row0purge.c line 541 Last time write locked in file row/row0mysql.c line 3043 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0 InnoDB: ###### Diagnostic info printed to the standard error stream