|
|
C:\Documents and Settings\Skyman.Man>mysql -h172.16.24.43 -uskyman.man -pmanchungood
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10908873
Server version: 5.5.8-log Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show engine innodb status\G;
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
111025 1:34:01 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 45 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1258266 1_second, 1257889 sleeps, 125825 10_second, 17 background, 17 flush
srv_master_thread log flush and writes: 1262101
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1768412, signal count 3322456
Mutex spin waits 170786003, rounds 159137200, OS waits 533095
RW-shared spins 1310647, rounds 26826730, OS waits 746069
RW-excl spins 755764, rounds 18662671, OS waits 399233
Spin rounds per wait: 0.93 mutex, 20.47 RW-shared, 24.69 RW-excl
------------------------
LATEST DETECTED DEADLOCK
------------------------
111012 2:44:44
*** (1) TRANSACTION:
TRANSACTION 9A1B2BE, ACTIVE 44 sec, process no 18205, OS thread id 1087220032 fetching rows
mysql tables in use 3, locked 3
LOCK WAIT 17252 lock struct(s), heap size 1767864, 1861141 row lock(s), undo log entries 6797
MySQL thread id 1279639, query id 43574721 192.168.0.90 skyman.man Sending data
create table test_cpny.fin_metric_data
SELECT dat.*
FROM cdp.excel_stock es
inner join web_cdp.fin_metric_data dat
on (es.stock_id = dat.stock_id)
where es.upd_stmp between '2011-10-12 01:12:22' and '2011-10-12 02:40:21'
and es.last_pub_excel_id is not null
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1886 page no 30715 n bits 160 index `PRIMARY` of table `cdp`.`fin_metric_data` trx id 9A1B2BE lock mode S waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 26; compact format; info bits 0
0: len 4; hex 000002c9; asc ;;
1: len 7; hex 3030312e303137; asc 001.017;;
2: len 4; hex 000007d4; asc ;;
3: len 1; hex 01; asc ;;
4: len 3; hex 435552; asc CUR;;
5: len 6; hex 000009a1ae16; asc ;;
6: len 7; hex 6f0000636305f8; asc o cc ;;
7: len 4; hex 00000a2f; asc /;;
8: len 4; hex 0000005d; asc ];;
9: len 1; hex 08; asc ;;
10: len 1; hex 01; asc ;;
11: len 1; hex 01; asc ;;
12: len 4; hex 0000001b; asc ;;
13: len 4; hex 00000045; asc E;;
14: len 1; hex 83; asc ;;
15: len 3; hex 8fa87f; asc ;;
16: len 8; hex 5265706f72746564; asc Reported;;
17: len 4; hex 00000001; asc ;;
18: len 2; hex 5448; asc TH;;
19: len 3; hex 434e59; asc CNY;;
20: len 1; hex 01; asc ;;
21: len 14; hex 80000000000000026766002ab980; asc gf * ;;
22: len 14; hex 8000000000000000000267660118; asc gf ;;
23: SQL NULL;
24: len 1; hex 80; asc ;;
25: len 4; hex 4e94fee8; asc N ;;
*** (2) TRANSACTION:
TRANSACTION 9A1AE16, ACTIVE 52 sec, process no 18205, OS thread id 1239828800 inserting
mysql tables in use 4, locked 4
1203 lock struct(s), heap size 129464, 83075 row lock(s), undo log entries 180
MySQL thread id 1347644, query id 43572001 172.16.24.42 csfdp Sending data
REPLACE INTO cdp.fin_metric_data (stock_id,metric_cd,prd_year,prd_qtr,accumulate, excel_id,rpt_id,acc_std,biz_cd,acc_cd,row_num,col_num, month_num, bal_sht_dt, rep
fs.prd_year, lcrfs.prd_qtr, lcrfs.accumulate, lcrfs.excel_id,lcrfs.rpt_id,lcrfs.acc_std,lcrfs.biz_cd,lcrfs.acc_cd,lcrfs.row_num,lcrfs.col_num, lcfrp.month_num, lcf
NA' then 2 when 'NM' then 3 else 1 end as data_tp, case lcrfs.data_val when 'NA' then null when 'NM' then null else lcrfs.data_val end as data_val, case lcrfs.data
FROM cdp.listed_cpny_rpt_fin_style_5 lcrfs INNER
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1886 page no 30715 n bits 160 index `PRIMARY` of table `cdp`.`fin_metric_data` trx id 9A1AE16 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 26; compact format; info bits 0
0: len 4; hex 000002c9; asc ;;
1: len 7; hex 3030312e303137; asc 001.017;;
2: len 4; hex 000007d4; asc ;;
3: len 1; hex 01; asc ;;
4: len 3; hex 435552; asc CUR;;
5: len 6; hex 000009a1ae16; asc ;;
6: len 7; hex 6f0000636305f8; asc o cc ;;
7: len 4; hex 00000a2f; asc /;;
8: len 4; hex 0000005d; asc ];;
9: len 1; hex 08; asc ;;
10: len 1; hex 01; asc ;;
11: len 1; hex 01; asc ;;
12: len 4; hex 0000001b; asc ;;
13: len 4; hex 00000045; asc E;;
14: len 1; hex 83; asc ;;
15: len 3; hex 8fa87f; asc ;;
16: len 8; hex 5265706f72746564; asc Reported;;
17: len 4; hex 00000001; asc ;;
18: len 2; hex 5448; asc TH;;
19: len 3; hex 434e59; asc CNY;;
20: len 1; hex 01; asc ;;
21: len 14; hex 80000000000000026766002ab980; asc gf * ;;
22: len 14; hex 8000000000000000000267660118; asc gf ;;
23: SQL NULL;
24: len 1; hex 80; asc ;;
25: len 4; hex 4e94fee8; asc N ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1886 page no 36296 n bits 192 index `PRIMARY` of table `cdp`.`fin_metric_data` trx id 9A1AE16 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 26; compact format; info bits 0
0: len 4; hex 000002c9; asc ;;
1: len 7; hex 3030312e303031; asc 001.001;;
2: len 4; hex 000007d4; asc ;;
3: len 1; hex 01; asc ;;
4: len 3; hex 435552; asc CUR;;
5: len 6; hex 000005a9994e; asc N;;
6: len 7; hex 090002033d0e9f; asc = ;;
7: len 4; hex 0000072b; asc +;;
8: len 4; hex 00000054; asc T;;
9: len 1; hex 08; asc ;;
10: len 1; hex 01; asc ;;
11: len 1; hex 01; asc ;;
12: len 4; hex 0000001c; asc ;;
13: len 4; hex 00000045; asc E;;
14: len 1; hex 83; asc ;;
15: len 3; hex 8fa87f; asc ;;
16: len 8; hex 5265706f72746564; asc Reported;;
17: len 4; hex 00000002; asc ;;
18: len 2; hex 5448; asc TH;;
19: len 3; hex 434e59; asc CNY;;
20: len 1; hex 01; asc ;;
21: len 14; hex 800000000000001b989204e95300; asc S ;;
22: len 14; hex 80000000000000000d060e9c01c1; asc ;;
23: SQL NULL;
24: len 1; hex 80; asc ;;
25: len 4; hex 4e8afe4e; asc N N;;
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter C001881
Purge done for trx's n < C001834 undo n < 0
History list length 129
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started, process no 18205, OS thread id 1242491200
MySQL thread id 10908873, query id 292561834 192.168.0.90 skyman.man
show engine innodb status
---TRANSACTION 0, not started, process no 18205, OS thread id 1231042880
MySQL thread id 10909592, query id 292557993 csfdpws.chinascopefinancial.com 172.16.24.41 fdp
---TRANSACTION C001614, not started, process no 18205, OS thread id 1241958720
MySQL thread id 10909330, query id 292542993 172.16.24.42 csfdp
---TRANSACTION 0, not started, process no 18205, OS thread id 1240627520
MySQL thread id 10908466, query id 292500131 csfdpws.chinascopefinancial.com 172.16.24.41 cdp
---TRANSACTION 0, not started, process no 18205, OS thread id 1241692480
MySQL thread id 10908317, query id 292490585 csfdpws.chinascopefinancial.com 172.16.24.41 cdp
---TRANSACTION 0, not started, process no 18205, OS thread id 1237432640
MySQL thread id 10906566, query id 292386323 csfdpws.chinascopefinancial.com 172.16.24.41 cdp
---TRANSACTION BFD0A54, not started, process no 18205, OS thread id 1232640320
MySQL thread id 10873377, query id 292288418 192.168.0.62 sun.shang
---TRANSACTION BFCEF25, not started, process no 18205, OS thread id 1227848000
MySQL thread id 10860886, query id 292543778 hqdb01.chinascopefinancial.com 172.16.24.43 sym
---TRANSACTION BFFFEEC, not started, process no 18205, OS thread id 1241160000
MySQL thread id 10833830, query id 292559054 hqdb01.chinascopefinancial.com 172.16.24.43 sym
---TRANSACTION BFCA0B2, not started, process no 18205, OS thread id 1233439040
MySQL thread id 10826593, query id 292543775 hqdb01.chinascopefinancial.com 172.16.24.43 sym
---TRANSACTION C001876, ACTIVE 0 sec, process no 18205, OS thread id 1241426240 inserting
mysql tables in use 1, locked 1
295 lock struct(s), heap size 31160, 307 row lock(s), undo log entries 307
MySQL thread id 10909671, query id 292561835 csfdpws.chinascopefinancial.com 172.16.24.41 fdp update
replace into reuters_rkd_cr (opinion_orig, opinion_set, xref,
prod_dt, event_cd, event_asof,
opinion_meanrating, opinion_meanlabel, opinion_startdate,
opinion_expir, upd_stmp)
values ('2011-03-25 04:00:00', 'STD', 200022999,
'2011-10-22 00:00:00', 'Refresh', '2011-10-21 23:56:40',
3.1905, 'HOLD', '2011-07-25 14:46:52',
'2011-03-29 20:00:01', now())
--------
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 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
44331917 OS file reads, 90797626 OS file writes, 1920988 OS fsyncs
0.82 reads/s, 16384 avg bytes/read, 378.06 writes/s, 39.71 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 27452, seg size 27454, 937651 merges
merged operations:
insert 9098534, delete mark 3567904, delete 446221
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 6375023, node heap has 18422 buffer(s)
5416.32 hash searches/s, 124.22 non-hash searches/s
---
LOG
---
Log sequence number 790780341691
Log flushed up to 790780231294
Last checkpoint at 790773317068
0 pending log writes, 0 pending chkp writes
80279090 log i/o's done, 110.18 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 3296722944; in additional pool allocated 0
Dictionary memory allocated 1681691
Buffer pool size 196607
Free buffers 2
Database pages 178182
Old database pages 65761
Modified db pages 12369
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 59635727, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 49117475, created 4146559, written 24304656
0.82 reads/s, 0.00 creates/s, 1198.71 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: 178182, unzip_LRU len: 0
I/O sum[58580]: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. 18205, id 1195845952, state: sleeping
Number of rows inserted 226886319, updated 412483665, deleted 3081984, read 260362182863
1.20 inserts/s, 2752.98 updates/s, 0.00 deletes/s, 93734.47 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.30 sec)
ERROR:
No query specified
mysql>
|
|
楼主: mchdba
IP卡
狗仔卡
发表于 2011-10-21 16:47
显身卡
楼主
发表于 2011-10-24 22:37