sgrdb [(none)]> show engine innodb status\G *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 2018-04-27 19:08:56 7f6dd409d700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 8 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 124752 srv_active, 0 srv_shutdown, 36019 srv_idle srv_master_thread log flush and writes: 160770 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 24336633 --Thread 140082162812672 has waited at fsp0fsp.cc line 2755 for 0.0000 seconds the semaphore: X-lock on RW-latch at 0x7f6dd3ba4170 '&space->latch' a writer (thread id 140082201757440) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: fffffffffff00000 Last time read locked in file not yet reserved line 0 Last time write locked in file /root/sgrdb-10.0.27/sgrdb-10.0.27-src/storage/xtradb/fsp/fsp0fsp.cc line 2628 OS WAIT ARRAY INFO: signal count 27725646 Mutex spin waits 392887432, rounds 1807203519, OS waits 16492115 RW-shared spins 15081738, rounds 316096052, OS waits 6313283 RW-excl spins 14136006, rounds 99838367, OS waits 764724 Spin rounds per wait: 4.60 mutex, 20.96 RW-shared, 7.06 RW-excl ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 2018-04-26 11:32:56 7f678271a700 Error in foreign key constraint of table qywzx_dljy/pub_release_role: there is no index in the table which would contain the columns as the first columns, or the data types in the table do not match the ones in the referenced table or one of the ON ... SET NULL columns is declared NOT NULL. Constraint: , CONSTRAINT "pub_release_role_ibfk_1" FOREIGN KEY ("SUBJECTGUID") REFERENCES "pub_release" ("SUBJECTID") ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2018-04-27 11:14:28 7f67821af700 *** (1) TRANSACTION: TRANSACTION 13983118506, ACTIVE 3 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 6 lock struct(s), heap size 1184, 6 row lock(s), undo log entries 4 MySQL thread id 736, OS thread handle 0x7f6772a49700, query id 204640773 10.142.18.67 dbscale update INSERT INTO "qywzx_erp"."mbew" ("MANDT", "MATNR", "BWKEY", "BWTAR", "LVORM", "LBKUM", "SALK3", "VPRSV", "VERPR", "STPRS", "PEINH", "BKLAS", "SALKV", "VMKUM", "VMSAL", "VMVPR", "VMVER", "VMSTP", "VMPEI", "VMBKL", "VMSAV", "VJKUM", "VJSAL", "VJVPR", "VJVER", "VJSTP", "VJPEI", "VJBKL", "VJSAV", "LFGJA", "LFMON", "BWTTY", "STPRV", "LAEPR", "ZKPRS", "ZKDAT", "TIMESTAMP", "BWPRS", "BWPRH", "VJBWS", "VJBWH", "VVJSL", "VVJLB", "VVMLB", "VVSAL", "ZPLPR", "ZPLP1", "ZPLP2", "ZPLP3", "ZPLD1", "ZPLD2", "ZPLD3", "PPERZ", "PPERL", "PPERV", "KALKZ", "KALKL", "KALKV", "KALSC", "XLIFO", "MYPOL", "BWPH1", "BWPS1", "ABWKZ", "PSTAT", "KALN1", "KALNR", "BWVA1", "BWVA2", "BWVA3", "VERS1", "VERS2", "VERS3", "HRKFT", "KOSGR", "PPRDZ", "PPRDL", "PPRDV", "PDATZ", "PDATL", "PDATV", "EKALR", "VPLPR", "MLMAA", "MLAST", "LPLPR", "VKSAL", "HKMAT", "SPERW", "KZIWL", "WLINL", "ABCIW", "BWSPA", "LPLPX", "VPLPX", "FPLPX", "LBWST", "VBWST", "FBW *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 4105 page no 555587 n bits 96 index "PRIMARY" of table "qywzx_erp"."mbew" trx table locks 1 total table locks 2 trx id 13983118506 lock mode S locks rec but not gap waiting lock hold time 3 wait time before grant 0 *** (2) TRANSACTION: TRANSACTION 13976893289, ACTIVE 9053 sec fetching rows, thread declared inside InnoDB 4655 mysql tables in use 1, locked 1 402731 lock struct(s), heap size 42300968, 7910988 row lock(s), undo log entries 5863285 MySQL thread id 606, OS thread handle 0x7f67821af700, query id 198335086 10.142.18.67 dbscale updating DELETE FROM "qywzx_erp"."mbew" *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 4105 page no 555587 n bits 96 index "PRIMARY" of table "qywzx_erp"."mbew" trx table locks 1 total table locks 2 trx id 13976893289 lock_mode X lock hold time 3839 wait time before grant 0 *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 4105 page no 378341 n bits 104 index "PRIMARY" of table "qywzx_erp"."mbew" trx table locks 1 total table locks 2 trx id 13976893289 lock_mode X waiting lock hold time 1 wait time before grant 0 *** WE ROLL BACK TRANSACTION (1) ------------ TRANSACTIONS ------------ Trx id counter 13991061383 Purge done for trx's n:o < 13931404405 undo n:o < 0 state: running but idle History list length 3826453 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 971, OS thread handle 0x7f6dd409d700, query id 214169851 10.142.18.67 dbscale init show engine innodb status ---TRANSACTION 13991059372, not started MySQL thread id 991, OS thread handle 0x7f676c7b6700, query id 214167567 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991055989, not started MySQL thread id 994, OS thread handle 0x7f6771c4a700, query id 214164387 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991046260, not started MySQL thread id 908, OS thread handle 0x7f6781f1e700, query id 214160032 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991045877, not started MySQL thread id 981, OS thread handle 0x7f677004a700, query id 214159442 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991060932, not started MySQL thread id 907, OS thread handle 0x7f6dd4054700, query id 214169340 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991060101, not started MySQL thread id 970, OS thread handle 0x7f676384a700, query id 214168386 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991043172, not started MySQL thread id 870, OS thread handle 0x7f678196a700, query id 214156089 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991042813, not started MySQL thread id 867, OS thread handle 0x7f676d200700, query id 214151851 10.142.18.67 dbscale altering table alter table `D_IO_DET` add column `xh_inc` bigint not null auto_increment unique key ---TRANSACTION 13991061338, not started MySQL thread id 958, OS thread handle 0x7f6770c4a700, query id 214169800 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991042682, not started MySQL thread id 950, OS thread handle 0x7f6781cd6700, query id 214151695 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991044186, not started MySQL thread id 963, OS thread handle 0x7f67a59dd700, query id 214157888 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991046944, not started MySQL thread id 814, OS thread handle 0x7f67a5bdc700, query id 214160612 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991058563, not started MySQL thread id 957, OS thread handle 0x7f676bb24700, query id 214166626 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991061328, not started sleeping before entering InnoDB mysql tables in use 1, locked 1 MySQL thread id 962, OS thread handle 0x7f676d249700, query id 214169836 10.142.18.67 dbscale update INSERT INTO u_file_basic(DATA_ID,ORG_NO,CONS_TYPE,CONS_NO,TMNL_BAR_CODE,METER_BAR_CODE,COLL_OBJ_ID,TG_ID,LINE_ID,TMNL_FACTORY_CODE,METER_FACTORY_CODE,RC_RATIO,VOLT_RATIO,REC_TIME,ORG_NO_P,BEGIN_TIME,END_TIME,FLAG,ETL_TIME,TG_NO,LINE_NO,TERMINAL_ID,CP_NO,METER_ID,FMR_ID) VALUES ( '61346749', '354010201', '02', NULL, '010086442223', '010086442223', '1845275454', '1984603', '1772764', '105', '104', '400.00', '1.00', '2018-04-27 06:00:06', '35401', '2017-09-18 00:00:00', '2099-12-31 00:00:00', 'AC', '2018-04-27 06:00:06', '22256984', '3M80102000000919', '1060112427', '0002306634', '1060112446', ' ---TRANSACTION 13991061363, not started MySQL thread id 961, OS thread handle 0x7f676ebff700, query id 214169820 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991060862, not started MySQL thread id 956, OS thread handle 0x7f676d093700, query id 214169277 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991061050, not started sleeping before entering InnoDB mysql tables in use 1, locked 1 MySQL thread id 868, OS thread handle 0x7f676ea92700, query id 214169551 10.142.18.67 dbscale update INSERT INTO qywzx_pms2_dwzy.conf_codedefinition(OID,CODEDEFID,CODENAME,CODEID,CODETYPENAME,PROTYPE,CODETYPEID,ISSG,SGCODE,CODEORDER,STATE_ID,CODE,VERSIONID) VALUES ( '5.47000000000000000e+02', '5.00000000000000000e+00', '维操队', '1.99007000000000000e+05', '班组类别', NULL, NULL, '0.00000000000000000e+00', NULL, NULL, '0.00000000000000000e+00', '5.00000000000000000e+00', '0.00000000000000000e+00') ---TRANSACTION 13991034763, not started MySQL thread id 953, OS thread handle 0x7f6781ad7700, query id 214143013 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991034758, not started MySQL thread id 952, OS thread handle 0x7f676dc93700, query id 214143008 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991034994, not started MySQL thread id 902, OS thread handle 0x7f676dfff700, query id 214142968 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991061361, not started MySQL thread id 951, OS thread handle 0x7f6da996c700, query id 214169819 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991033135, not started MySQL thread id 842, OS thread handle 0x7f6770edb700, query id 214141603 10.142.18.67 dbscale altering table alter table `SDSRYSJGB` add column `xh_inc` bigint not null auto_increment unique key ---TRANSACTION 13991061373, not started MySQL thread id 901, OS thread handle 0x7f67a5a26700, query id 214169832 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991058982, not started MySQL thread id 891, OS thread handle 0x7f6dd5c5c700, query id 214167077 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991057199, not started MySQL thread id 885, OS thread handle 0x7f6da9923700, query id 214165595 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991041844, not started MySQL thread id 949, OS thread handle 0x7f6771fff700, query id 214150773 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991039822, not started MySQL thread id 847, OS thread handle 0x7f676e8dc700, query id 214148139 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991037062, not started MySQL thread id 948, OS thread handle 0x7f67736db700, query id 214145130 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991041747, not started MySQL thread id 895, OS thread handle 0x7f6781ed5700, query id 214150372 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991061333, not started MySQL thread id 840, OS thread handle 0x7f6771d25700, query id 214169797 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991028220, not started MySQL thread id 929, OS thread handle 0x7f6772bff700, query id 214136803 10.142.18.67 dbscale altering table alter table `T_SB_ZNYC_ZNYCDG` add column `xh_inc` bigint not null auto_increment unique key ---TRANSACTION 13991034897, not started MySQL thread id 890, OS thread handle 0x7f6dd5ca5700, query id 214159150 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991061346, not started MySQL thread id 884, OS thread handle 0x7f676d324700, query id 214169803 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991026423, not started MySQL thread id 882, OS thread handle 0x7f676ba00700, query id 214134516 10.142.18.67 dbscale altering table alter table `D_DEV_STORAGE` add column `xh_inc` bigint not null auto_increment unique key ---TRANSACTION 13991025527, not started MySQL thread id 888, OS thread handle 0x7f6781a45700, query id 214131898 10.142.18.67 dbscale altering table alter table `ZJYQZFXMDATA` add column `xh_inc` bigint not null auto_increment unique key ---TRANSACTION 13991060525, not started MySQL thread id 944, OS thread handle 0x7f6771db7700, query id 214168870 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991061378, not started MySQL thread id 942, OS thread handle 0x7f676dd25700, query id 214169835 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991022824, not started MySQL thread id 939, OS thread handle 0x7f67826d1700, query id 214127391 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991042300, not started MySQL thread id 936, OS thread handle 0x7f676b9b7700, query id 214151408 10.142.18.67 dbscale Waiting for table metadata lock alter table `T_DETECT_EQUIP` add column `xh_inc` bigint not null auto_increment unique key ---TRANSACTION 13991061058, not started MySQL thread id 813, OS thread handle 0x7f67703b6700, query id 214169510 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991038127, not started MySQL thread id 916, OS thread handle 0x7f67a5870700, query id 214146302 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991009404, not started MySQL thread id 844, OS thread handle 0x7f6781e8c700, query id 214111681 10.142.18.67 dbscale altering table alter table `GDCBYSJGB` add column `xh_inc` bigint not null auto_increment unique key ---TRANSACTION 13991041572, not started MySQL thread id 820, OS thread handle 0x7f67a5a6f700, query id 214150209 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991006000, not started MySQL thread id 937, OS thread handle 0x7f67821af700, query id 214106943 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991021963, not started MySQL thread id 827, OS thread handle 0x7f676f5b7700, query id 214126201 10.142.18.67 dbscale cleaning up ---TRANSACTION 13990997828, not started MySQL thread id 934, OS thread handle 0x7f676dcdc700, query id 214094280 10.142.18.67 dbscale cleaning up ---TRANSACTION 13990997833, not started MySQL thread id 932, OS thread handle 0x7f6da99b5700, query id 214094107 10.142.18.67 dbscale cleaning up ---TRANSACTION 13990988097, not started MySQL thread id 915, OS thread handle 0x7f67a5b01700, query id 214077145 10.142.18.67 dbscale altering table alter table `T_DETECT_EQUIP` add column `xh_inc` bigint not null auto_increment unique key ---TRANSACTION 13990986663, not started MySQL thread id 807, OS thread handle 0x7f67a594b700, query id 214072112 10.142.18.67 dbscale altering table alter table `YS_KMSJMX` add column `xh_inc` bigint not null auto_increment unique key ---TRANSACTION 13991061365, not started MySQL thread id 857, OS thread handle 0x7f6781d1f700, query id 214169822 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991061334, not started MySQL thread id 846, OS thread handle 0x7f676eb6d700, query id 214169798 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991028067, not started MySQL thread id 879, OS thread handle 0x7f676e96e700, query id 214136598 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991038568, not started MySQL thread id 831, OS thread handle 0x7f676de49700, query id 214146817 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991059051, not started MySQL thread id 894, OS thread handle 0x7f678188f700, query id 214167160 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991061066, not started MySQL thread id 862, OS thread handle 0x7f6770db7700, query id 214169523 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991061248, not started MySQL thread id 819, OS thread handle 0x7f67703ff700, query id 214169700 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991060288, not started MySQL thread id 806, OS thread handle 0x7f676c7ff700, query id 214168566 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991061371, not started MySQL thread id 889, OS thread handle 0x7f6770fff700, query id 214169831 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991061370, not started MySQL thread id 878, OS thread handle 0x7f6781c44700, query id 214169826 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991040552, not started MySQL thread id 826, OS thread handle 0x7f67700dc700, query id 214148933 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991059466, not started MySQL thread id 837, OS thread handle 0x7f6771f6d700, query id 214167664 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991061364, not started MySQL thread id 926, OS thread handle 0x7f676df24700, query id 214169821 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991043227, not started MySQL thread id 873, OS thread handle 0x7f676eb24700, query id 214156016 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991045142, not started MySQL thread id 830, OS thread handle 0x7f676e9b7700, query id 214158603 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991038583, not started MySQL thread id 836, OS thread handle 0x7f6763a00700, query id 214146880 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991058744, not started MySQL thread id 919, OS thread handle 0x7f676f7b6700, query id 214166855 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991061348, not started MySQL thread id 893, OS thread handle 0x7f6da98da700, query id 214169808 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991043597, not started MySQL thread id 839, OS thread handle 0x7f676bbb6700, query id 214156978 10.142.18.67 dbscale Waiting for table metadata lock alter table `D_DEV_STORAGE` add column `xh_inc` bigint not null auto_increment unique key ---TRANSACTION 13991061336, not started MySQL thread id 852, OS thread handle 0x7f6770c93700, query id 214169799 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991060529, not started MySQL thread id 829, OS thread handle 0x7f676eadb700, query id 214168876 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991061341, not started MySQL thread id 866, OS thread handle 0x7f6771c93700, query id 214169801 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991061329, not started MySQL thread id 822, OS thread handle 0x7f6773600700, query id 214169842 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991059145, not started MySQL thread id 825, OS thread handle 0x7f6770125700, query id 214167308 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991043367, not started MySQL thread id 918, OS thread handle 0x7f6772a00700, query id 214156474 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991061375, not started MySQL thread id 910, OS thread handle 0x7f6763b6d700, query id 214169833 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991061369, not started MySQL thread id 872, OS thread handle 0x7f676f6db700, query id 214169825 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991061349, not started MySQL thread id 904, OS thread handle 0x7f6dd4178700, query id 214169809 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991060351, not started MySQL thread id 898, OS thread handle 0x7f676ebb6700, query id 214168638 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991052232, not started returning various info to MySQL MySQL thread id 843, OS thread handle 0x7f676396e700, query id 214163349 10.142.18.67 dbscale After opening tables SHOW TABLE STATUS ---TRANSACTION 13991061357, not started MySQL thread id 811, OS thread handle 0x7f676b96e700, query id 214169812 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991061345, not started MySQL thread id 875, OS thread handle 0x7f676e84a700, query id 214169802 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991056764, not started MySQL thread id 914, OS thread handle 0x7f6782241700, query id 214165173 10.142.18.67 dbscale Waiting for table metadata lock alter table `T_SB_ZNYC_ZNYCDG` add column `xh_inc` bigint not null auto_increment unique key ---TRANSACTION 13991057391, not started MySQL thread id 881, OS thread handle 0x7f676c724700, query id 214165808 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991061217, not started sleeping before entering InnoDB mysql tables in use 1, locked 1 MySQL thread id 845, OS thread handle 0x7f6770292700, query id 214169740 10.142.18.67 dbscale update INSERT INTO qywzx_pms2_dwzy.T_TX_DYSB_DYBLQ(OID,SHAPE,SBMC,YXBH,DYDJ,SBZLX,SSZRQ,STATE_ID,FHJD,CONNECTION,SBID,YXDW,SSDZ,SFBZ,BZDX,BZYS,BZFW,PLFS,DHZS,X,Y,FHDX,SSJG,TYPE,BZXSZD,BZNR,SSDS,KZFHID,SSTQLX,SSTQ,APPTYPE,VERSIONID,SFYC,BZT_XSXX,BH_XSXX,TY_XSXX,GZQX_XSXX,LXFS_XSXX,CQ_XSXX,TSBF_XSXX,QT_XSXX,ZDH_XSXX,CHZ_XSXX,DZ_XSXX) VALUES ( '8.96470000000000000e+04', '2||0||NULL|NULL|NULL||1|2|1||119.275516575912|26.0653282143867|119.275516575912|26.0653261931774||', '锦绣滨城6#楼配电室(高)避雷器89647', '锦绣滨城6#楼配电室(高)避雷器', '8.00000000000000000e+00', '3.31200000000 ---TRANSACTION 13991061358, not started MySQL thread id 818, OS thread handle 0x7f67638dc700, query id 214169839 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991026828, not started MySQL thread id 817, OS thread handle 0x7f676ea00700, query id 214134989 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991060273, not started MySQL thread id 851, OS thread handle 0x7f6782564700, query id 214168550 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991061124, not started sleeping before entering InnoDB mysql tables in use 1, locked 1 MySQL thread id 808, OS thread handle 0x7f6da9848700, query id 214169608 10.142.18.67 dbscale update INSERT INTO u_day_vstat(ID,DATA_DATE,UA_UUP_TIME,UA_LLW_TIME,UA_UP_TIME,UA_LOW_TIME,UA_NML_TIME,UB_UUP_TIME,UB_LLW_TIME,UB_UP_TIME,UB_LOW_TIME,UB_NML_TIME,UC_UUP_TIME,UC_LLW_TIME,UC_UP_TIME,UC_LOW_TIME,UC_NML_TIME,UA_MAX,UA_MAX_TIME,UA_MIN,UA_MIN_TIME,UB_MAX,UB_MAX_TIME,UB_MIN,UB_MIN_TIME,UC_MAX,UC_MAX_TIME,UC_MIN,UC_MIN_TIME,UA_AVERAGE,UB_AVERAGE,UC_AVERAGE,UA_UP_RATIO,UA_LOW_RATIO,UA_NML_RATIO,UB_UP_RATIO,UB_LOW_RATIO,UB_NML_RATIO,UC_UP_RATIO,UC_LOW_RATIO,UC_NML_RATIO,ETL_TIME,ORG_NO_P) VALUES ( '1.90028280000000000e+07', '2018-04-19 00:00:00', '4.38000000000000000e+02', '0.0000000000000000 ---TRANSACTION 13991061368, not started MySQL thread id 861, OS thread handle 0x7f6771d6e700, query id 214169823 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991061376, not started MySQL thread id 917, OS thread handle 0x7f6773525700, query id 214169834 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991056736, not started MySQL thread id 849, OS thread handle 0x7f676e925700, query id 214165110 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991061331, not started MySQL thread id 828, OS thread handle 0x7f676f76d700, query id 214169792 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991059155, not started MySQL thread id 909, OS thread handle 0x7f6763bb6700, query id 214167326 10.142.18.67 dbscale cleaning up ---TRANSACTION 13990982371, not started MySQL thread id 858, OS thread handle 0x7f6770324700, query id 214067412 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991061119, not started MySQL thread id 855, OS thread handle 0x7f676f56e700, query id 214169590 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991060949, not started MySQL thread id 841, OS thread handle 0x7f6763a49700, query id 214169356 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991061360, not started MySQL thread id 824, OS thread handle 0x7f6772adb700, query id 214169818 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991058654, not started MySQL thread id 892, OS thread handle 0x7f677376d700, query id 214166752 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991059814, not started MySQL thread id 876, OS thread handle 0x7f6763bff700, query id 214168066 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991014174, not started MySQL thread id 874, OS thread handle 0x7f676f4dc700, query id 214116784 10.142.18.67 dbscale Waiting for table metadata lock truncate table base_table_sgrdb_fcm ---TRANSACTION 13991061067, not started sleeping before entering InnoDB mysql tables in use 1, locked 1 MySQL thread id 871, OS thread handle 0x7f6771cdc700, query id 214169593 10.142.18.67 dbscale update INSERT INTO u_day_energy(ID,DATA_DATE,COL_TIME,PAP_E,PAP_E1,PAP_E2,PAP_E3,PAP_E4,PRP_E,PRP_E1,PRP_E2,PRP_E3,PRP_E4,RAP_E,RAP_E1,RAP_E2,RAP_E3,RAP_E4,RRP_E,RRP_E1,RRP_E2,RRP_E3,RRP_E4,RP1_E,RP3_E,RP2_E,RP4_E,ETL_TIME,ORG_NO_P) VALUES ( '5.76507220000000000e+07', '2018-02-12 00:00:00', '2018-02-13 10:15:03', '1.17799999999999994e+01', '0.00000000000000000e+00', '3.87000000000000011e+00', '4.31000000000000050e+00', '3.60000000000000009e+00', '3.56000000000000005e+00', '0.00000000000000000e+00', '2.50000000000000000e-01', '8.39999999999999969e-01', '2.47000000000000020e+00', '0.00000000000000000e ---TRANSACTION 13991056748, not started MySQL thread id 821, OS thread handle 0x7f678231c700, query id 214165125 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991061354, not started MySQL thread id 804, OS thread handle 0x7f67822d3700, query id 214169811 10.142.18.67 dbscale cleaning up ---TRANSACTION 13991061352, not started MySQL thread id 913, OS thread handle 0x7f6dd34cd700, query id 214169810 10.142.18.67 dbscale cleaning up ---TRANSACTION 13810617603, not started MySQL thread id 1, OS thread handle 0x7f67827f5700, query id 0 Waiting for background binlog tasks ---TRANSACTION 13991060293, ACTIVE 635 sec inserting, thread declared inside InnoDB 5000 mysql tables in use 1, locked 1 104 lock struct(s), heap size 13864, 103 row lock(s), undo log entries 103 MySQL thread id 860, OS thread handle 0x7f677284a700, query id 214169850 10.142.18.67 dbscale update INSERT INTO "qywzx_erp"."mbew" ("MANDT", "MATNR", "BWKEY", "BWTAR", "LVORM", "LBKUM", "SALK3", "VPRSV", "VERPR", "STPRS", "PEINH", "BKLAS", "SALKV", "VMKUM", "VMSAL", "VMVPR", "VMVER", "VMSTP", "VMPEI", "VMBKL", "VMSAV", "VJKUM", "VJSAL", "VJVPR", "VJVER", "VJSTP", "VJPEI", "VJBKL", "VJSAV", "LFGJA", "LFMON", "BWTTY", "STPRV", "LAEPR", "ZKPRS", "ZKDAT", "TIMESTAMP", "BWPRS", "BWPRH", "VJBWS", "VJBWH", "VVJSL", "VVJLB", "VVMLB", "VVSAL", "ZPLPR", "ZPLP1", "ZPLP2", "ZPLP3", "ZPLD1", "ZPLD2", "ZPLD3", "PPERZ", "PPERL", "PPERV", "KALKZ", "KALKL", "KALKV", "KALSC", "XLIFO", "MYPOL", "BWPH1", "BWPS1 Trx #rec lock waits 2 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC ---TRANSACTION 13991042812, ACTIVE 6393 sec reading clustered index mysql tables in use 1, locked 1 1 lock struct(s), heap size 360, 0 row lock(s) MySQL thread id 867, OS thread handle 0x7f676d200700, query id 214151851 10.142.18.67 dbscale altering table alter table `D_IO_DET` add column `xh_inc` bigint not null auto_increment unique key Trx #rec lock waits 0 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC ---TRANSACTION 13991038801, ACTIVE 7741 sec sleeping before entering InnoDB mysql tables in use 1, locked 1 588 lock struct(s), heap size 63016, 837 row lock(s), undo log entries 837 MySQL thread id 815, OS thread handle 0x7f6770e92700, query id 214169553 10.142.18.67 dbscale update INSERT INTO "qywzx_erp"."mbew" ("MANDT", "MATNR", "BWKEY", "BWTAR", "LVORM", "LBKUM", "SALK3", "VPRSV", "VERPR", "STPRS", "PEINH", "BKLAS", "SALKV", "VMKUM", "VMSAL", "VMVPR", "VMVER", "VMSTP", "VMPEI", "VMBKL", "VMSAV", "VJKUM", "VJSAL", "VJVPR", "VJVER", "VJSTP", "VJPEI", "VJBKL", "VJSAV", "LFGJA", "LFMON", "BWTTY", "STPRV", "LAEPR", "ZKPRS", "ZKDAT", "TIMESTAMP", "BWPRS", "BWPRH", "VJBWS", "VJBWH", "VVJSL", "VVJLB", "VVMLB", "VVSAL", "ZPLPR", "ZPLP1", "ZPLP2", "ZPLP3", "ZPLD1", "ZPLD2", "ZPLD3", "PPERZ", "PPERL", "PPERV", "KALKZ", "KALKL", "KALKV", "KALSC", "XLIFO", "MYPOL", "BWPH1", "BWPS1 Trx #rec lock waits 2 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC ---TRANSACTION 13991033134, ACTIVE 8689 sec reading clustered index mysql tables in use 1, locked 1 1 lock struct(s), heap size 360, 0 row lock(s) MySQL thread id 842, OS thread handle 0x7f6770edb700, query id 214141603 10.142.18.67 dbscale altering table alter table `SDSRYSJGB` add column `xh_inc` bigint not null auto_increment unique key Trx #rec lock waits 0 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC ---TRANSACTION 13991028219, ACTIVE 9409 sec reading clustered index mysql tables in use 1, locked 1 1 lock struct(s), heap size 360, 0 row lock(s) MySQL thread id 929, OS thread handle 0x7f6772bff700, query id 214136803 10.142.18.67 dbscale altering table alter table `T_SB_ZNYC_ZNYCDG` add column `xh_inc` bigint not null auto_increment unique key Trx #rec lock waits 0 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC ---TRANSACTION 13991026422, ACTIVE 9690 sec reading clustered index mysql tables in use 1, locked 1 1 lock struct(s), heap size 360, 0 row lock(s) MySQL thread id 882, OS thread handle 0x7f676ba00700, query id 214134516 10.142.18.67 dbscale altering table alter table `D_DEV_STORAGE` add column `xh_inc` bigint not null auto_increment unique key Trx #rec lock waits 0 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC ---TRANSACTION 13991025526, ACTIVE 9984 sec mysql tables in use 1, locked 1 1 lock struct(s), heap size 360, 0 row lock(s) MySQL thread id 888, OS thread handle 0x7f6781a45700, query id 214131898 10.142.18.67 dbscale altering table alter table `ZJYQZFXMDATA` add column `xh_inc` bigint not null auto_increment unique key Trx #rec lock waits 0 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC ---TRANSACTION 13991009403, ACTIVE 12083 sec mysql tables in use 1, locked 1 1 lock struct(s), heap size 360, 0 row lock(s) MySQL thread id 844, OS thread handle 0x7f6781e8c700, query id 214111681 10.142.18.67 dbscale altering table alter table `GDCBYSJGB` add column `xh_inc` bigint not null auto_increment unique key Trx #rec lock waits 0 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC ---TRANSACTION 13991007379, ACTIVE 12206 sec fetching rows, thread declared inside InnoDB 589 mysql tables in use 1, locked 0 MySQL thread id 899, OS thread handle 0x7f6dd40e6700, query id 214108725 10.142.18.67 dbscale Sending data SELECT COUNT(*) FROM `qywzx_erp`.`FAGL_SPLINFO` Trx read view will not see trx with id >= 13991007380, sees < 13931403794 Trx #rec lock waits 0 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC ---TRANSACTION 13990988096, ACTIVE 13381 sec mysql tables in use 1, locked 1 1 lock struct(s), heap size 360, 0 row lock(s) MySQL thread id 915, OS thread handle 0x7f67a5b01700, query id 214077145 10.142.18.67 dbscale altering table alter table `T_DETECT_EQUIP` add column `xh_inc` bigint not null auto_increment unique key Trx #rec lock waits 0 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC ---TRANSACTION 13990986662, ACTIVE 13591 sec mysql tables in use 1, locked 1 1 lock struct(s), heap size 360, 0 row lock(s) MySQL thread id 807, OS thread handle 0x7f67a594b700, query id 214072112 10.142.18.67 dbscale altering table alter table `YS_KMSJMX` add column `xh_inc` bigint not null auto_increment unique key Trx #rec lock waits 2 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC ---TRANSACTION 13990975822, ACTIVE 14929 sec updating or deleting, thread declared inside InnoDB 76 mysql tables in use 1, locked 1 190100 lock struct(s), heap size 23082536, 30850814 row lock(s), undo log entries 6616661 MySQL thread id 880, OS thread handle 0x7f676de00700, query id 214046572 10.142.18.67 dbscale updating delete from U_DAY_DEMAND where date_format(date_sub(curdate(),INTERVAL 1 month),'%Y%m%01')<=etl_time Trx #rec lock waits 5 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC ---TRANSACTION 13990975810, ACTIVE 14933 sec fetching rows, thread declared inside InnoDB 3134 mysql tables in use 1, locked 1 3972482 lock struct(s), heap size 417216040, 139336414 row lock(s) MySQL thread id 810, OS thread handle 0x7f67824d2700, query id 214046592 10.142.18.67 dbscale updating delete FROM u_vol_curve where date_format(date_sub(curdate(),INTERVAL 2 day),'%Y%m%d')<=data_date Trx #rec lock waits 1 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC ---TRANSACTION 13990975732, ACTIVE 14936 sec updating or deleting, thread declared inside InnoDB 1553 mysql tables in use 1, locked 1 2916827 lock struct(s), heap size 306984488, 105732716 row lock(s), undo log entries 1318379 MySQL thread id 928, OS thread handle 0x7f676b8dc700, query id 214046337 10.142.18.67 dbscale updating delete from U_CUR_CURVE where date_format(date_sub(curdate(),INTERVAL 7 day),'%Y%m%d')<=ETL_TIME Trx #rec lock waits 0 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC ---TRANSACTION 13990975731, ACTIVE 14938 sec fetching rows, thread declared inside InnoDB 2578 mysql tables in use 1, locked 1 3577312 lock struct(s), heap size 433108520, 538381672 row lock(s) MySQL thread id 853, OS thread handle 0x7f676d2db700, query id 214046309 10.142.18.67 dbscale updating delete from U_DAY_READ where date_format(date_sub(curdate(),INTERVAL 0 day),'%Y%m%d')<=ETL_TIME Trx #rec lock waits 0 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC ---TRANSACTION 13967552028, ACTIVE 59991 sec fetching rows, thread declared inside InnoDB 4081 mysql tables in use 1, locked 1 7553134 lock struct(s), heap size 793277992, 18673316 row lock(s), undo log entries 11120183 MySQL thread id 693, OS thread handle 0x7f676d125700, query id 181266762 10.142.18.67 dbscale updating DELETE FROM "qywzx_erp"."stxl" Trx #rec lock waits 0 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC ---TRANSACTION 13949099981, ACTIVE 74820 sec fetching rows, thread declared inside InnoDB 3634 mysql tables in use 1, locked 0 MySQL thread id 682, OS thread handle 0x7f676c600700, query id 214122641 10.142.18.67 dbscale Sending data SELECT COUNT(*) FROM `qywzx_erp`.`COEPBR` Trx read view will not see trx with id >= 13949099982, sees < 13893314071 Trx #rec lock waits 0 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC ---TRANSACTION 13931539584, ACTIVE 85716 sec MySQL thread id 605, OS thread handle 0x7f67823f7700, query id 121048007 10.142.18.67 dbscale cleaning up Trx read view will not see trx with id >= 13931539585, sees < 13887561242 Trx #rec lock waits 0 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC ---TRANSACTION 13931484631, ACTIVE 85779 sec MySQL thread id 566, OS thread handle 0x7f676f724700, query id 120954509 10.142.18.67 dbscale cleaning up Trx read view will not see trx with id >= 13931484632, sees < 13887561242 Trx #rec lock waits 0 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC ---TRANSACTION 13931410819, ACTIVE 85832 sec MySQL thread id 629, OS thread handle 0x7f6dd3516700, query id 120840255 10.142.18.67 dbscale cleaning up Trx read view will not see trx with id >= 13931410820, sees < 13887561242 Trx #rec lock waits 0 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC ---TRANSACTION 13931403794, ACTIVE 85837 sec MySQL thread id 675, OS thread handle 0x7f6781ff9700, query id 132342028 10.142.18.67 dbscale cleaning up Trx read view will not see trx with id >= 13931403795, sees < 13887561242 Trx #rec lock waits 0 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: complete io for buf page (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: complete io for buf page (read thread) I/O thread 5 state: complete io for buf page (read thread) I/O thread 6 state: complete io for buf page (read thread) I/O thread 7 state: waiting for completed aio requests (read thread) I/O thread 8 state: waiting for completed aio requests (read thread) I/O thread 9 state: waiting for completed aio requests (read thread) I/O thread 10 state: waiting for completed aio requests (read thread) I/O thread 11 state: waiting for completed aio requests (read thread) I/O thread 12 state: waiting for completed aio requests (read thread) I/O thread 13 state: waiting for completed aio requests (read thread) I/O thread 14 state: complete io for buf page (read thread) I/O thread 15 state: complete io for buf page (read thread) I/O thread 16 state: waiting for completed aio requests (read thread) I/O thread 17 state: complete io for buf page (read thread) I/O thread 18 state: waiting for completed aio requests (write thread) I/O thread 19 state: waiting for completed aio requests (write thread) I/O thread 20 state: waiting for completed aio requests (write thread) I/O thread 21 state: waiting for completed aio requests (write thread) I/O thread 22 state: waiting for completed aio requests (write thread) I/O thread 23 state: waiting for completed aio requests (write thread) I/O thread 24 state: waiting for completed aio requests (write thread) I/O thread 25 state: waiting for completed aio requests (write thread) I/O thread 26 state: waiting for completed aio requests (write thread) I/O thread 27 state: waiting for completed aio requests (write thread) I/O thread 28 state: waiting for completed aio requests (write thread) I/O thread 29 state: waiting for completed aio requests (write thread) I/O thread 30 state: waiting for completed aio requests (write thread) I/O thread 31 state: waiting for completed aio requests (write thread) I/O thread 32 state: waiting for completed aio requests (write thread) I/O thread 33 state: waiting for completed aio requests (write thread) Pending normal aio reads: 96 [3, 0, 4, 5, 11, 0, 0, 0, 0, 0, 0, 0, 66, 0, 0, 7] , aio writes: 0 [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 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 137146165 OS file reads, 330248950 OS file writes, 3279553 OS fsyncs 108.76 reads/s, 18573 avg bytes/read, 195.37 writes/s, 14.08 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 17598, free list len 553752, seg size 571351, 5555868 merges merged operations: insert 39421320, delete mark 157487987, delete 355 discarded operations: insert 0, delete mark 0, delete 0 6.12 hash searches/s, 479.94 non-hash searches/s --- LOG --- Log sequence number 18231282605858 Log flushed up to 18231282558863 Pages flushed up to 18230476965043 Last checkpoint at 18230441589016 Max checkpoint age 6956654102 Checkpoint age target 6739258662 Modified age 805640991 Checkpoint age 841017018 0 pending log writes, 0 pending chkp writes 284762744 log i/o's done, 0.31 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 24616370176; in additional pool allocated 0 Total memory allocated by read views 29096 Internal hash tables (constant factor + variable factor) Adaptive hash index 682216480 (373996472 + 308220008) Page hash 23375608 (buffer pool 0 only) Dictionary cache 104643799 (93500944 + 11142855) File system 69835072 (812272 + 69022800) Lock system 2032227312 (58437656 + 1973789656) Recovery system 0 (0 + 0) Dictionary memory allocated 11142855 Buffer pool size 1441791 Buffer pool size, bytes 23622303744 Free buffers 0 Database pages 1302517 Old database pages 480825 Modified db pages 81780 Percent of dirty pages(LRU & free pages): 6.279 Max dirty pages percent: 75.000 Pending reads 102 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 5061757, not young 2902675955 3.54 youngs/s, 3271.83 non-youngs/s Pages read 136554136, created 8505383, written 43617055 102.92 reads/s, 15.31 creates/s, 190.06 writes/s Buffer pool hit rate 993 / 1000, young-making rate 0 / 1000 not 240 / 1000 Pages read ahead 88.61/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 1302517, unzip_LRU len: 0 I/O sum[3575]:cur[1002], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 7 queries inside InnoDB, 6 queries in queue 6 read views open inside InnoDB 21 RW transactions active inside InnoDB 0 RO transactions active inside InnoDB 21 out of 1000 descriptors used ---OLDEST VIEW--- Normal read view Read view low limit trx n:o 13931403795 Read view up limit trx id 13887561242 Read view low limit trx id 13931403795 Read view individually stored trx ids: Read view trx id 13887561242 Read view trx id 13893314071 Read view trx id 13899080336 Read view trx id 13900769360 Read view trx id 13928342061 Read view trx id 13930676452 Read view trx id 13931403341 Read view trx id 13931403787 Read view trx id 13931403792 Read view trx id 13931403793 ----------------- Main thread process no. 28626, id 140082610681600, state: doing insert buffer merge Number of rows inserted 234777933, updated 652004, deleted 204075303, read 5555754460 0.50 inserts/s, 0.00 updates/s, 93.86 deletes/s, 5448.19 reads/s Number of system rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ 1 row in set (4.88 sec) sgrdb [(none)]>