在对table_log_http操作时（现在是optimize table table_log_http，对delete from table_log_http where time
| InnoDB | |
=====================================
120302 14:54:24 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 47 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1376738, signal count 1249353
--Thread 2465463184 has waited at btr/btr0cur.c line 450 for 0.00 seconds the semaphore:
X-lock on RW-latch at 0x964580ac created in file buf/buf0buf.c line 550
a writer (thread id 2465463184) has reserved it in mode exclusive
number of readers 0, waiters flag 1
Last time read locked in file btr/btr0pcur.c line 249
Last time write locked in file buf/buf0buf.c line 1847
Mutex spin waits 0, rounds 111147055, OS waits 507048
RW-shared spins 1458431, OS waits 545916; RW-excl spins 2206472, OS waits 266782
------------------------
LATEST DETECTED DEADLOCK
------------------------
120228 17:44:28
*** (1) TRANSACTION:
TRANSACTION 0 65206414, ACTIVE 0 sec, process no 31911, OS thread id 2461453200 setting table lock
mysql tables in use 59, locked 59
LOCK WAIT 48 lock struct(s), heap size 2496, 0 row lock(s)
MySQL thread id 12298, query id 21131972 localhost root System lock
LOCK TABLES
table_anonymous_lan WRITE,
table_app_userdef WRITE,
table_authtype_lan WRITE,
table_black_list_ip WRITE,
table_black_list_url WRITE,
net_manager__system.table_constant WRITE,
table_ability_config WRITE,
table_user_ability_rank WRITE,
table_system_ability_limit WRITE,
table_user_ability_limit WRITE,
table_filter WRITE,
table_filter_app_rule WRITE,
table_filter_rule_param WRITE,
table_exception_url WRITE,
table_keyword_content WRITE,
table_keyword_email_attach WRITE,
table_keyword_email_from WRITE,
table_keyword_email_subject WRITE,
table_keyword_email_to WRITE,
table_policy_app WRITE,
table_policy_audit WRITE,
table_policy_block WRITE,
table_policy_default
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `net_manager__log`.`table_log_http` trx id 0 65206414 lock mode X waiting
*** (2) TRANSACTION:
TRANSACTION 0 65205413, ACTIVE 98 sec, process no 31911, OS thread id 2412227472 setting auto-inc lock
mysql tables in use 2, locked 2
22 lock struct(s), heap size 2496, 67 row lock(s), undo log entries 2018
MySQL thread id 680, query id 21131987 localhost root
LOAD DATA INFILE '/tmp/.SQL-wtKG5T'
IGNORE
INTO TABLE table_log_http
FIELDS
TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\''
ESCAPED BY '\\'
LINES
STARTING BY ''
TERMINATED BY '\n'
(time,object_user_id,source_ip,source_port,dest_ip,dest_port,url,site_id,accept,up_data_size,down_data_size,m_serial,source_mac,dest_mac,local_ip,serious_level)
*** (2) HOLDS THE LOCK(S):
TABLE LOCK table `net_manager__log`.`table_log_http` trx id 0 65205413 lock mode IX
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `net_manager__log`.`table_log_http` trx id 0 65205413 lock mode AUTO-INC waiting
*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 0 66287826
Purge done for trx's n:o < 0 66287764 undo n:o < 0 0
History list length 18
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 31911, OS thread id 2460650384
MySQL thread id 61029, query id 74982244 localhost root
show innodb status
---TRANSACTION 0 0, not started, process no 31911, OS thread id 2459982736
mysql tables in use 2, locked 2
MySQL thread id 60467, query id 74961524 localhost root Table lock
LOAD DATA INFILE '/tmp/.SQL-wtKG5T'
IGNORE
INTO TABLE table_log_http
FIELDS
TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\''
ESCAPED BY '\\'
LINES
STARTING BY ''
TERMINATED BY '\n'
(time,object_user_id,source_ip,source_port,dest_ip,dest_port,url,site_id,accept,up_data_size,down_data_size,m_serial,source_mac,dest_mac,local_ip,serious_level)
---TRANSACTION 0 66287318, not started, process no 31911, OS thread id 2413030288
MySQL thread id 1, query id 74981710 localhost root
---TRANSACTION 0 66287825, not started, process no 31911, OS thread id 2412628880
MySQL thread id 69, query id 74982242 localhost root
---TRANSACTION 0 66287320, ACTIVE 50 sec, process no 31911, OS thread id 2459782032 setting table lock
mysql tables in use 59, locked 59
LOCK WAIT 48 lock struct(s), heap size 2496, 0 row lock(s)
MySQL thread id 60421, query id 74981712 localhost root System lock
LOCK TABLES
table_anonymous_lan WRITE,
table_app_userdef WRITE,
table_authtype_lan WRITE,
table_black_list_ip WRITE,
table_black_list_url WRITE,
net_manager__system.table_constant WRITE,
table_ability_config WRITE,
table_user_ability_rank WRITE,
table_system_ability_limit WRITE,
table_user_ability_limit WRITE,
table_filter WRITE,
table_filter_app_rule WRITE,
table_filter_rule_param WRITE,
table_exception_url WRITE,
t
------- TRX HAS BEEN WAITING 50 SEC FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `net_manager__log`.`table_log_http` trx id 0 66287320 lock mode X waiting
------------------
---TRANSACTION 0 66285291, ACTIVE 273 sec, process no 31911, OS thread id 2465463184 inserting, thread declared inside InnoDB 263
mysql tables in use 2, locked 2
2077 lock struct(s), heap size 126272, 149235 row lock(s), undo log entries 147163
MySQL thread id 41886, query id 74273645 localhost root copy to tmp table
optimize table table_log_http
--------
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: doing file i/o (read thread) ev set
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 4, 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
1159244 OS file reads, 2316629 OS file writes, 255016 OS fsyncs
2 pending preads, 0 pending pwrites
3.47 reads/s, 48046 avg bytes/read, 11.70 writes/s, 1.79 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1141, free list len 789, seg size 1931,
7698482 inserts, 7475806 merged recs, 281130 merges
Hash table size 2212699, node heap has 697 buffer(s)
5757.79 hash searches/s, 996.04 non-hash searches/s
---
LOG
---
Log sequence number 111 169998468
Log flushed up to 111 169981725
Last checkpoint at 110 4271753671
0 pending log writes, 0 pending chkp writes
117334 log i/o's done, 0.70 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 595480198; in additional pool allocated 4084736
Dictionary memory allocated 743672
Buffer pool size 32768
Free buffers 0
Database pages 32064
Modified db pages 16145
Pending reads 5
Pending writes: LRU 0, flush list 0, single page 0
Pages read 2606714, created 1615231, written 4617266
10.15 reads/s, 15.74 creates/s, 34.13 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
1 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 31911, id 2421623696, state: doing insert buffer merge
Number of rows inserted 57214121, updated 58304, deleted 7994496, read 583968345
546.88 inserts/s, 0.02 updates/s, 0.00 deletes/s, 628.31 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
阅读(1869) | 评论(1) | 转发(0) |
