Re: Innodb configuration optimal for my workload?

[Fernando Perez]

Thank you for your answer Rick,

Here is the outpu of SHOW CREATE TABLE

eventdatalast | CREATE TABLE `eventdatalast` (
`accountID` varchar(32) NOT NULL,
`deviceID` varchar(32) NOT NULL,
`uniqueid` varchar(32) NOT NULL,
`timestamp` int(10) unsigned NOT NULL,
`statusCode` int(10) unsigned NOT NULL,
`entity` varchar(32) DEFAULT NULL,
`dataSource` varchar(32) DEFAULT NULL,
`rawData` varchar(512) DEFAULT NULL,
`latitude` double DEFAULT NULL,
`longitude` double DEFAULT NULL,
`gpsAge` int(10) unsigned DEFAULT NULL,
`horzAccuracy` double DEFAULT NULL,
`speedKPH` double DEFAULT NULL,
`heading` double DEFAULT NULL,
`altitude` double DEFAULT NULL,
`distanceKM` double DEFAULT NULL,
`odometerKM` double DEFAULT NULL,
`temperature` varchar(6) DEFAULT NULL,
`geozoneIndex` int(10) unsigned DEFAULT NULL,
`geozoneID` varchar(32) DEFAULT NULL,
`address` varchar(512) DEFAULT NULL,
`subdivision` varchar(32) DEFAULT NULL,
`creationTime` int(10) unsigned DEFAULT NULL,
`binstatus` varchar(36) DEFAULT NULL,
`fechalocal` varchar(10) DEFAULT NULL,
`horalocal` varchar(20) DEFAULT NULL,
`reporte` varchar(512) DEFAULT NULL,
`tipov` int(11) DEFAULT NULL,
`gpsequipment` varchar(36) DEFAULT NULL,
`ptcp` int(11) DEFAULT NULL,
`ID_distribuidor` int(11) DEFAULT NULL,
`tiemposolicitud` int(11) DEFAULT NULL,
`id_eventdatalast` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id_eventdatalast`),
UNIQUE KEY `dev_id_uni_id` (`accountID`,`deviceID`,`uniqueid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=44481985 DEFAULT CHARSET=latin1 |

This is the UPDATE STATEMENT that cause a deadlock, every time i receive new data of a device im doing and UPDATE, the problem maybe is that a unit can be out of cell coverage and when the device reconnects to the server its going to send all the events recorded in memory, so a device can send several data packets and every packet received its an update:

UPDATE eventdatalast set timestamp = 1317743882,statusCode =61472,latitude =19.2148133833333,longitude =-96.1600933833333,speedKPH =31,heading =255,address ='A 0.243 kms al NNE de Boulevard Fidel Velazquez Sanchez,91899,Pinitos,Veracruz,Veracruz,Veracruz,México',rawData ='####TSF9110315580211100470960960561912888801725500525538524681A1480?5216963011',binstatus ='00000001',fechalocal ='2011/10/04',horalocal ='10:58:02',reporte ='AUTOREPORTE',gpsequipment ='GTX',odometerKM = 0,ptcp = 4000,temperature = 'NA',tipov = 32,entity = '0' where accountid = 'tracomex' and uniqueid ='00525538524681' and timestamp <=1317743882



| InnoDB | |
=====================================
111004 12:18:57 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 2 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 331496 1_second, 331495 sleeps, 32166 10_second, 10495 background, 10495 flush
srv_master_thread log flush and writes: 338730
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 93016, signal count 93649
Mutex spin waits 8358342, rounds 20581967, OS waits 29813
RW-shared spins 63582, rounds 1700301, OS waits 51568
RW-excl spins 17292, rounds 396423, OS waits 8179
Spin rounds per wait: 2.46 mutex, 26.74 RW-shared, 22.93 RW-excl
------------------------
LATEST DETECTED DEADLOCK
------------------------
111004 12:09:40
*** (1) TRANSACTION:
TRANSACTION E2DD88, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 20 lock struct(s), heap size 3112, 4 row lock(s), undo log entries 1
MySQL thread id 115683, OS thread handle 0x1bd0, query id 8955629 localhost 127.0.0.1 gts Updating
UPDATE eventdatalast set timestamp = 1317743882,statusCode =61472,latitude =19.2148133833333,longitude =-96.1600933833333,speedKPH =31,heading =255,address ='A 0.243 kms al NNE de Boulevard Fidel Velazquez Sanchez,91899,Pinitos,Veracruz,Veracruz,Veracruz,México',rawData ='####TSF9110315580211100470960960561912888801725500525538524681A1480?5216963011',binstatus ='00000001',fechalocal ='2011/10/04',horalocal ='10:58:02',reporte ='AUTOREPORTE',gpsequipment ='GTX',odometerKM = 0,ptcp = 4000,temperature = 'NA',tipov = 32,entity = '0' where accountid = 'tracomex' and uniqueid ='00525538524681' and timestamp <=1317743882
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 100 page no 11 n bits 96 index `PRIMARY` of table `gts`.`eventdatalast` trx id E2DD88 lock_mode X locks rec but not gap waiting
Record lock, heap no 14 PHYSICAL RECORD: n_fields 35; compact format; info bits 0
0: len 4; hex 802714a5; asc ' ;;
1: len 6; hex 000000e2d576; asc v;;
2: len 7; hex 69000004ea0e49; asc i I;;
3: len 8; hex 747261636f6d6578; asc tracomex;;
4: len 9; hex 4d6f76696c20373537; asc Movil 757;;
5: len 14; hex 3030353235353339353532313136; asc 00525539552116;;
6: len 4; hex 4e8b2dd8; asc N - ;;
7: len 4; hex 0000f424; asc $;;
8: len 1; hex 30; asc 0;;
9: SQL NULL;
10: len 30; hex 232323235453394431313033313630313238313131303034373130313036; asc ####TS9D1103160128111004710106; (total 78 bytes);
11: len 8; hex a7b032887d453940; asc 2 }E9@;;
12: len 8; hex 14e40c6aa44659c0; asc j FY ;;
13: SQL NULL;
14: SQL NULL;
15: len 8; hex 0000000000000000; asc ;;
16: len 8; hex 0000000000000000; asc ;;
17: SQL NULL;
18: SQL NULL;
19: len 8; hex d9cef753e3e52640; asc S &@;;
20: len 2; hex 4e41; asc NA;;
21: SQL NULL;
22: SQL NULL;
23: len 30; hex 456e204d65782d353420612031392e373631206b6d7320616c2053535720; asc En Mex-54 a 19.761 kms al SSW ; (total 90 bytes);
24: SQL NULL;
25: SQL NULL;
26: len 8; hex 3030303030303031; asc 00000001;;
27: len 10; hex 323031312f31302f3034; asc 2011/10/04;;
28: len 8; hex 31313a30313a3238; asc 11:01:28;;
29: len 25; hex 4d6f646f2064652041686f72726f20646520456e6572676961; asc Modo de Ahorro de Energia;;
30: len 4; hex 80000020; asc ;;
31: len 3; hex 475458; asc GTX;;
32: len 4; hex 80000fa0; asc ;;
33: SQL NULL;
34: SQL NULL;

*** (2) TRANSACTION:
TRANSACTION E2DD89, ACTIVE 0 sec fetching rows, thread declared inside InnoDB 457
mysql tables in use 1, locked 1
24 lock struct(s), heap size 3112, 8 row lock(s), undo log entries 1
MySQL thread id 115638, OS thread handle 0x20b0, query id 8955641 localhost 127.0.0.1 gts Updating
UPDATE eventdatalast set timestamp = 1317744145,statusCode =63521,latitude =19.5328560776209,longitude =-96.9053052422489,speedKPH =0,heading =0,address ='Prolongación Zempoala,91180,Azteca,Xalapa,Jalapa de Enriquez,Veracruz,México',rawData ='4D43475000E7C902000004DF261A040000000001430000000068FFC5007414010000000000000224000402077041EBF501310802E123020000000000AC0D190210040ADB07CC',binstatus ='223',fechalocal ='2011/10/04',horalocal ='11:02:25',reporte ='AUTOREPORTE DEL SISTEMA',gpsequipment ='Cellocator',odometerKM = 70772,ptcp = 15510,temperature = 'NA',tipov = 12,entity = '0',subdivision = 'A',dataSource = '12.660128' where accountid = 'emovil' and uniqueid ='182759' and timestamp <=1317744145
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 100 page no 11 n bits 96 index `PRIMARY` of table `gts`.`eventdatalast` trx id E2DD89 lock_mode X locks rec but not gap
Record lock, heap no 14 PHYSICAL RECORD: n_fields 35; compact format; info bits 0
0: len 4; hex 802714a5; asc ' ;;
1: len 6; hex 000000e2d576; asc v;;
2: len 7; hex 69000004ea0e49; asc i I;;
3: len 8; hex 747261636f6d6578; asc tracomex;;
4: len 9; hex 4d6f76696c20373537; asc Movil 757;;
5: len 14; hex 3030353235353339353532313136; asc 00525539552116;;
6: len 4; hex 4e8b2dd8; asc N - ;;
7: len 4; hex 0000f424; asc $;;
8: len 1; hex 30; asc 0;;
9: SQL NULL;
10: len 30; hex 232323235453394431313033313630313238313131303034373130313036; asc ####TS9D1103160128111004710106; (total 78 bytes);
11: len 8; hex a7b032887d453940; asc 2 }E9@;;
12: len 8; hex 14e40c6aa44659c0; asc j FY ;;
13: SQL NULL;
14: SQL NULL;
15: len 8; hex 0000000000000000; asc ;;
16: len 8; hex 0000000000000000; asc ;;
17: SQL NULL;
18: SQL NULL;
19: len 8; hex d9cef753e3e52640; asc S &@;;
20: len 2; hex 4e41; asc NA;;
21: SQL NULL;
22: SQL NULL;
23: len 30; hex 456e204d65782d353420612031392e373631206b6d7320616c2053535720; asc En Mex-54 a 19.761 kms al SSW ; (total 90 bytes);
24: SQL NULL;
25: SQL NULL;
26: len 8; hex 3030303030303031; asc 00000001;;
27: len 10; hex 323031312f31302f3034; asc 2011/10/04;;
28: len 8; hex 31313a30313a3238; asc 11:01:28;;
29: len 25; hex 4d6f646f2064652041686f72726f20646520456e6572676961; asc Modo de Ahorro de Energia;;
30: len 4; hex 80000020; asc ;;
31: len 3; hex 475458; asc GTX;;
32: len 4; hex 80000fa0; asc ;;
33: SQL NULL;
34: SQL NULL;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 100 page no 22 n bits 112 index `PRIMARY` of table `gts`.`eventdatalast` trx id E2DD89 lock_mode X locks rec but not gap waiting
Record lock, heap no 27 PHYSICAL RECORD: n_fields 35; compact format; info bits 0
0: len 4; hex 80272906; asc ') ;;
1: len 6; hex 000000e2dd88; asc ;;
2: len 7; hex 45000004cc1a53; asc E S;;
3: len 8; hex 747261636f6d6578; asc tracomex;;
4: len 9; hex 4d6f76696c20343035; asc Movil 405;;
5: len 14; hex 3030353235353338353234363831; asc 00525538524681;;
6: len 4; hex 4e8b2d0a; asc N - ;;
7: len 4; hex 0000f020; asc ;;
8: len 1; hex 30; asc 0;;
9: SQL NULL;
10: len 30; hex 232323235453463931313033313535383032313131303034373039363039; asc ####TSF91103155802111004709609; (total 78 bytes);
11: len 8; hex de288802fe363340; asc ( 63@;;
12: len 8; hex 3e6e51f83e0a58c0; asc >nQ > X ;;
13: SQL NULL;
14: SQL NULL;
15: len 8; hex 0000000000003f40; asc ?@;;
16: len 8; hex 0000000000e06f40; asc o@;;
17: SQL NULL;
18: SQL NULL;
19: len 8; hex 0000000000000000; asc ;;
20: len 2; hex 4e41; asc NA;;
21: SQL NULL;
22: SQL NULL;
23: len 30; hex 4120302e323433206b6d7320616c204e4e4520646520426f756c65766172; asc A 0.243 kms al NNE de Boulevar; (total 103 bytes);
24: SQL NULL;
25: SQL NULL;
26: len 8; hex 3030303030303031; asc 00000001;;
27: len 10; hex 323031312f31302f3034; asc 2011/10/04;;
28: len 8; hex 31303a35383a3032; asc 10:58:02;;
29: len 11; hex 4155544f5245504f525445; asc AUTOREPORTE;;
30: len 4; hex 80000020; asc ;;
31: len 3; hex 475458; asc GTX;;
32: len 4; hex 80000fa0; asc ;;
33: SQL NULL;
34: SQL NULL;

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter E2FA59
Purge done for trx's n:o < E2FA34 undo n:o < 0
History list length 3436
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 117171, OS thread handle 0x2fd4, query id 8998662 201.145.12.199 root
show engine innodb status
---TRANSACTION E2FA58, not started
MySQL thread id 117472, OS thread handle 0x233c, query id 8998659 localhost 127.0.0.1 gts
---TRANSACTION E2FA54, not started
MySQL thread id 117464, OS thread handle 0x1e94, query id 8998580 localhost 127.0.0.1 gts
---TRANSACTION E2FA53, not started
MySQL thread id 117461, OS thread handle 0x21c8, query id 8998554 localhost 127.0.0.1 gts
---TRANSACTION E2FA50, not started
MySQL thread id 117456, OS thread handle 0x1184, query id 8998497 localhost 127.0.0.1 gts
---TRANSACTION E2FA4F, not started
MySQL thread id 117453, OS thread handle 0x1ea4, query id 8998471 localhost 127.0.0.1 gts
---TRANSACTION E2FA4C, not started
MySQL thread id 117447, OS thread handle 0x144c, query id 8998413 localhost 127.0.0.1 gts
---TRANSACTION E2FA4B, not started
MySQL thread id 117444, OS thread handle 0x2964, query id 8998387 localhost 127.0.0.1 gts
---TRANSACTION E2FA46, not started
MySQL thread id 117439, OS thread handle 0x4a4, query id 8998335 localhost 127.0.0.1 gts
---TRANSACTION E2FA45, not started
MySQL thread id 117436, OS thread handle 0x2ffc, query id 8998308 localhost 127.0.0.1 gts
---TRANSACTION E2FA44, not started
MySQL thread id 117430, OS thread handle 0x1b08, query id 8998255 localhost 127.0.0.1 gts
---TRANSACTION E2FA42, not started
MySQL thread id 117427, OS thread handle 0x1cb8, query id 8998226 localhost 127.0.0.1 gts
---TRANSACTION E2FA3D, not started
MySQL thread id 117420, OS thread handle 0x2840, query id 8998155 localhost 127.0.0.1 gts
---TRANSACTION E2FA3C, not started
MySQL thread id 117417, OS thread handle 0x1aa0, query id 8998135 localhost 127.0.0.1 gts
---TRANSACTION E2FA23, not started
MySQL thread id 117403, OS thread handle 0x2610, query id 8997895 localhost 127.0.0.1 gts
---TRANSACTION E2FA22, not started
MySQL thread id 117400, OS thread handle 0x2f80, query id 8997869 localhost 127.0.0.1 gts
---TRANSACTION E2FA17, not started
MySQL thread id 117389, OS thread handle 0x2ee8, query id 8997732 localhost 127.0.0.1 gts
---TRANSACTION E2FA16, not started
MySQL thread id 117388, OS thread handle 0x2494, query id 8997724 localhost 127.0.0.1 gts
---TRANSACTION E2FA0B, not started
MySQL thread id 117385, OS thread handle 0xba4, query id 8997658 localhost 127.0.0.1 gts
---TRANSACTION E2F9EE, not started
MySQL thread id 117379, OS thread handle 0xb38, query id 8997482 localhost 127.0.0.1 gts
---TRANSACTION E2F9EB, not started
MySQL thread id 117376, OS thread handle 0x1ccc, query id 8997451 localhost 127.0.0.1 gts
---TRANSACTION E2F9E7, not started
MySQL thread id 117369, OS thread handle 0x2ed4, query id 8997381 localhost 127.0.0.1 gts
---TRANSACTION E2F9DD, not started
MySQL thread id 117366, OS thread handle 0x134c, query id 8997317 localhost 127.0.0.1 gts
---TRANSACTION E2F9A4, not started
MySQL thread id 117356, OS thread handle 0x22bc, query id 8996998 localhost 127.0.0.1 gts
---TRANSACTION E2F9A3, not started
MySQL thread id 117353, OS thread handle 0x1fbc, query id 8996972 localhost 127.0.0.1 gts
---TRANSACTION E2F9A2, not started
MySQL thread id 117346, OS thread handle 0x1870, query id 8996909 localhost 127.0.0.1 gts
---TRANSACTION E2F9A1, not started
MySQL thread id 117343, OS thread handle 0x414, query id 8996883 localhost 127.0.0.1 gts
---TRANSACTION E2F9A0, not started
MySQL thread id 117337, OS thread handle 0x2f94, query id 8996829 localhost 127.0.0.1 gts
---TRANSACTION E2F983, not started
MySQL thread id 117329, OS thread handle 0x2360, query id 8996642 localhost 127.0.0.1 gts
---TRANSACTION E2F982, not started
MySQL thread id 117326, OS thread handle 0x2f8, query id 8996615 localhost 127.0.0.1 gts
---TRANSACTION E2F962, not started
MySQL thread id 117316, OS thread handle 0x23d8, query id 8996404 localhost 127.0.0.1 gts
---TRANSACTION E2F961, not started
MySQL thread id 117314, OS thread handle 0x1654, query id 8996387 localhost 127.0.0.1 gts
---TRANSACTION E2F960, not started
MySQL thread id 117311, OS thread handle 0x1310, query id 8996360 localhost 127.0.0.1 gts
---TRANSACTION E2F95F, not started
MySQL thread id 117300, OS thread handle 0x26d0, query id 8996261 localhost 127.0.0.1 gts
---TRANSACTION E2F95E, not started
MySQL thread id 117298, OS thread handle 0x28e4, query id 8996244 localhost 127.0.0.1 gts
---TRANSACTION E2F949, not started
MySQL thread id 117289, OS thread handle 0x2f64, query id 8996087 localhost 127.0.0.1 gts
---TRANSACTION E2F93A, not started
MySQL thread id 117285, OS thread handle 0x2e34, query id 8995981 localhost 127.0.0.1 gts
---TRANSACTION E2F939, not started
MySQL thread id 117284, OS thread handle 0x27e8, query id 8995973 localhost 127.0.0.1 gts
---TRANSACTION E2F938, not started
MySQL thread id 117283, OS thread handle 0xc08, query id 8995964 localhost 127.0.0.1 gts
---TRANSACTION E2F937, not started
MySQL thread id 117282, OS thread handle 0x18a8, query id 8995956 localhost 127.0.0.1 gts
---TRANSACTION E2F92F, not started
MySQL thread id 117281, OS thread handle 0x2254, query id 8995916 localhost 127.0.0.1 gts
---TRANSACTION E2F91A, not started
MySQL thread id 117280, OS thread handle 0x23c4, query id 8995812 localhost 127.0.0.1 gts
---TRANSACTION E2F90B, not started
MySQL thread id 117279, OS thread handle 0x20fc, query id 8995762 localhost 127.0.0.1 gts
---TRANSACTION E2F90A, not started
MySQL thread id 117278, OS thread handle 0x2258, query id 8995753 localhost 127.0.0.1 gts
---TRANSACTION E2F909, not started
MySQL thread id 117277, OS thread handle 0x18d0, query id 8995745 localhost 127.0.0.1 gts
---TRANSACTION E2F908, not started
MySQL thread id 117276, OS thread handle 0x13b4, query id 8995737 localhost 127.0.0.1 gts
---TRANSACTION E2F907, not started
MySQL thread id 117268, OS thread handle 0xf84, query id 8995664 localhost 127.0.0.1 gts
---TRANSACTION E2F906, not started
MySQL thread id 117266, OS thread handle 0x2e78, query id 8995646 localhost 127.0.0.1 gts
---TRANSACTION E2F905, not started
MySQL thread id 117253, OS thread handle 0x1d50, query id 8995529 localhost 127.0.0.1 gts
---TRANSACTION E2F904, not started
MySQL thread id 117251, OS thread handle 0x16f0, query id 8995511 localhost 127.0.0.1 gts
---TRANSACTION E2F903, not started
MySQL thread id 117248, OS thread handle 0x2c14, query id 8995485 localhost 127.0.0.1 gts
---TRANSACTION E2F8F8, not started
MySQL thread id 117243, OS thread handle 0x2274, query id 8995403 localhost 127.0.0.1 gts
---TRANSACTION E2F8E3, not started
MySQL thread id 117240, OS thread handle 0x25c8, query id 8995300 localhost 127.0.0.1 gts
---TRANSACTION E2F8BB, not started
MySQL thread id 117230, OS thread handle 0x1674, query id 8995023 localhost 127.0.0.1 gts
---TRANSACTION E2F8B4, not started
MySQL thread id 117227, OS thread handle 0x2734, query id 8994985 localhost 127.0.0.1 gts
---TRANSACTION E2F8A9, not started
MySQL thread id 117217, OS thread handle 0xc6c, query id 8994856 localhost 127.0.0.1 gts
---TRANSACTION E2F8A8, not started
MySQL thread id 117214, OS thread handle 0x1978, query id 8994830 localhost 127.0.0.1 gts
---TRANSACTION E2FA36, not started
MySQL thread id 117207, OS thread handle 0x5d4, query id 8997991 localhost 127.0.0.1 gts
---TRANSACTION E2FA51, not started
MySQL thread id 117206, OS thread handle 0x2648, query id 8998508 localhost 127.0.0.1 gts
---TRANSACTION E2F88E, not started
MySQL thread id 117205, OS thread handle 0x29dc, query id 8994662 localhost 127.0.0.1 gts
---TRANSACTION E2FA3E, not started
MySQL thread id 117187, OS thread handle 0x1cb4, query id 8998196 localhost 127.0.0.1 gts
---TRANSACTION E2F9C3, not started
MySQL thread id 117185, OS thread handle 0x1f70, query id 8997183 localhost 127.0.0.1 gts
---TRANSACTION E2F836, not started
MySQL thread id 117173, OS thread handle 0x2b58, query id 8993981 localhost 127.0.0.1 gts
---TRANSACTION E2F835, not started
MySQL thread id 117169, OS thread handle 0x6cc, query id 8993955 localhost 127.0.0.1 gts
---TRANSACTION E2F834, not started
MySQL thread id 117165, OS thread handle 0x160c, query id 8993920 localhost 127.0.0.1 gts
---TRANSACTION E2F833, not started
MySQL thread id 117163, OS thread handle 0x24d4, query id 8993903 localhost 127.0.0.1 gts
---TRANSACTION E2F830, not started
MySQL thread id 117161, OS thread handle 0x1950, query id 8993881 localhost 127.0.0.1 gts
---TRANSACTION E2F80B, not started
MySQL thread id 117149, OS thread handle 0xefc, query id 8993620 localhost 127.0.0.1 gts
---TRANSACTION E2F80A, not started
MySQL thread id 117146, OS thread handle 0x1340, query id 8993594 localhost 127.0.0.1 gts
---TRANSACTION E2F7EC, not started
MySQL thread id 117137, OS thread handle 0x1fb0, query id 8993398 localhost 127.0.0.1 gts
---TRANSACTION E2F7EB, not started
MySQL thread id 117135, OS thread handle 0x2e74, query id 8993380 localhost 127.0.0.1 gts
---TRANSACTION E2F7EA, not started
MySQL thread id 117129, OS thread handle 0x2c68, query id 8993321 localhost 127.0.0.1 gts
---TRANSACTION E2F7CA, not started
MySQL thread id 117124, OS thread handle 0x1248, query id 8993144 localhost 127.0.0.1 gts
---TRANSACTION E2F7BB, not started
MySQL thread id 117116, OS thread handle 0x26a8, query id 8993030 localhost 127.0.0.1 gts
---TRANSACTION E2F7BA, not started
MySQL thread id 117113, OS thread handle 0x23f8, query id 8992996 localhost 127.0.0.1 gts
---TRANSACTION E2F7AB, not started
MySQL thread id 117110, OS thread handle 0x2854, query id 8992916 localhost 127.0.0.1 gts
---TRANSACTION E2F796, not started
MySQL thread id 117094, OS thread handle 0x15fc, query id 8992692 localhost 127.0.0.1 gts
---TRANSACTION E2F783, not started
MySQL thread id 117088, OS thread handle 0x1d0c, query id 8992560 localhost 127.0.0.1 gts
---TRANSACTION E2F782, not started
MySQL thread id 117086, OS thread handle 0x1efc, query id 8992543 localhost 127.0.0.1 gts
---TRANSACTION E2F754, not started
MySQL thread id 117071, OS thread handle 0x1bd0, query id 8992213 localhost 127.0.0.1 gts
---TRANSACTION E2F71C, not started
MySQL thread id 117045, OS thread handle 0x2094, query id 8991745 localhost 127.0.0.1 gts
---TRANSACTION E2F71B, not started
MySQL thread id 117043, OS thread handle 0x1084, query id 8991724 localhost 127.0.0.1 gts
---TRANSACTION E2F71A, not started
MySQL thread id 117039, OS thread handle 0x1bc0, query id 8991689 localhost 127.0.0.1 gts
---TRANSACTION E2F703, not started
MySQL thread id 117024, OS thread handle 0x27b4, query id 8991471 localhost 127.0.0.1 gts
---TRANSACTION E2F6DE, not started
MySQL thread id 117021, OS thread handle 0x1038, query id 8991300 localhost 127.0.0.1 gts
---TRANSACTION E2F6DD, not started
MySQL thread id 117020, OS thread handle 0x1278, query id 8991291 localhost 127.0.0.1 gts
---TRANSACTION E2F6D8, not started
MySQL thread id 117019, OS thread handle 0x2e70, query id 8991274 localhost 127.0.0.1 gts
---TRANSACTION E2F6D7, not started
MySQL thread id 117018, OS thread handle 0x2aa8, query id 8991265 localhost 127.0.0.1 gts
---TRANSACTION E2F6D6, not started
MySQL thread id 117017, OS thread handle 0x1c94, query id 8991256 localhost 127.0.0.1 gts
---TRANSACTION E2F6D5, not started
MySQL thread id 117016, OS thread handle 0x23e4, query id 8991248 localhost 127.0.0.1 gts
---TRANSACTION E2F6D4, not started
MySQL thread id 117015, OS thread handle 0x296c, query id 8991240 localhost 127.0.0.1 gts
---TRANSACTION E2F7BD, not started
MySQL thread id 116944, OS thread handle 0x21cc, query id 8993085 localhost 127.0.0.1 root
---TRANSACTION E2FA52, not started
MySQL thread id 116898, OS thread handle 0x15bc, query id 8998510 localhost 127.0.0.1 gts
---TRANSACTION E2F8D9, not started
MySQL thread id 116894, OS thread handle 0x17dc, query id 8995201 localhost 127.0.0.1 gts
---TRANSACTION E2F92E, not started
MySQL thread id 116893, OS thread handle 0x238c, query id 8995905 localhost 127.0.0.1 gts
---TRANSACTION E2FA57, not started
MySQL thread id 116888, OS thread handle 0x1da8, query id 8998623 localhost 127.0.0.1 gts
---TRANSACTION E2F8DA, not started
MySQL thread id 116831, OS thread handle 0x2df4, query id 8995223 localhost 127.0.0.1 gts
---TRANSACTION E2F881, not started
MySQL thread id 116830, OS thread handle 0x2e5c, query id 8994596 localhost 127.0.0.1 gts
---TRANSACTION E2F9BA, not started
MySQL thread id 116826, OS thread handle 0x2b74, query id 8997149 localhost 127.0.0.1 gts
---TRANSACTION E2F9B8, not started
MySQL thread id 116813, OS thread handle 0x1584, query id 8997144 localhost 127.0.0.1 gts
---TRANSACTION E2FA55, not started
MySQL thread id 116782, OS thread handle 0x2588, query id 8998619 localhost 127.0.0.1 gts
---TRANSACTION 0, not started
MySQL thread id 7893, OS thread handle 0x2fcc, query id 8998528 localhost 127.0.0.1 root
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (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
958269 OS file reads, 4143262 OS file writes, 427705 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 12, seg size 14, 1 merges
merged operations:
insert 4, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 10375991, node heap has 5978 buffer(s)
0.00 hash searches/s, 8.50 non-hash searches/s
---
LOG
---
Log sequence number 30783509519
Log flushed up to 30783509519
Last checkpoint at 30783509519
0 pending log writes, 0 pending chkp writes
1506552 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 5365760000; in additional pool allocated 0
Dictionary memory allocated 182255
Buffer pool size 320000
Free buffers 1
Database pages 314021
Old database pages 115897
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1526650, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 958228, created 14346, written 2574312
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, Random read ahead 0.00/s
LRU len: 314021, unzip_LRU len: 0
I/O sum[769]: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 id 8516, state: sleeping
Number of rows inserted 407755, updated 366458, deleted 71, read 503502841
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 72.96 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================