innodb update locks table...

[Anda Zhao]

Hi,

I am fairly new to MySQL and I am having some problem with MySQL using innodb. We have an application that will do very simple select/insert/update/delete. Each time the query only affects one row at a time:

Here is the table structure:

+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| MIN | varchar(30) | NO | PRI | | |
| OBJECT_DATA | longblob | YES | | NULL | |
| DATE_CREATED | datetime | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+

And here are the quesies:

select object_data from x where min = ?;
update x set object_data = ? where min = ?;
insert into x values (?, ?, now());
delete from x where min = ?;

we have auto_commit set to true in the application.

We are using connector/J with tomcate DBCP connection pooing and here is the setting in server.xml:

<Resource name="jdbc/mysqlds" auth="Container" type="javax.sql.DataSource" />
<ResourceParams name="jdbc/mysqlrts">
<parameter>
<name>factory</name>
<value>com.televigation.db.TeleNavDatasourceFactory</value>
</parameter>
<parameter>
<name>driverClassName</name>
<value>com.mysql.jdbc.Driver</value>
</parameter>
<parameter>
<name>url</name>
<value>jdbc:mysql://mysqlserver:3306/rts</value>
</parameter>
<parameter>
<name>username</name>
<value>mysql</value>
</parameter>
<parameter>
<name>password</name>
<value>mysql</value>
</parameter>
<parameter>
<name>maxActive</name>
<value>30</value>
</parameter>
<parameter>
<name>maxIdle</name>
<value>20</value>
</parameter>
<parameter>
<name>maxWait</name>
<value>1000</value>
</parameter>
<parameter>
<name>removeAbandoned</name>
<value>true</value>
</parameter>
<parameter>
<name>removeAbandonedTimeout</name>
<value>30</value>
</parameter>
<parameter>
<name>logAbandoned</name>
<value>true</value>
</parameter>
</ResourceParams>

I was running test with 50 threads. Each thread will work with ona and only one record (dedicated min) for the entire test doing select, update. There is 1 second think time between each iteration. What we found is that when the test got started, everything working great with good response time. max_used_connections from myusql show status is about 15-20. After a while, everything becomes slower, the max_used_connections gets to 30 (which is the max setting for connection pool) and eventually, everything stops. I used show engine innodb status\g to get the status (please see below). I also tried to do an insert with mysql client to tn_data_repository table, it hangs. Can someone help us to figure out what the problem is? Really appreciate.

Status:
=====================================
060226 16:15:04 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 6 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 7435, signal count 6845
Mutex spin waits 193901, rounds 795501, OS waits 2805
RW-shared spins 8385, OS waits 4190; RW-excl spins 10, OS waits 6
------------
TRANSACTIONS
------------
Trx id counter 0 16906521
Purge done for trx's n:o < 0 12175647 undo n:o < 0 0
History list length 17
Total number of lock structs in row lock hash table 10
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 19752, OS thread id 708950960
MySQL thread id 168, query id 31856712 localhost root
show ENGINE INNODB STATUS
---TRANSACTION 0 0, not started, process no 19752, OS thread id 764873648 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 166, query id 31856709 localhost rts_user update
insert into tn_data_repository values (2000, 'test', now())
---TRANSACTION 0 0, not started, process no 19752, OS thread id 710130608 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 163, query id 31856693 eng56.telenav.com 192.168.101.139 rts_user Updating
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1018'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 711310256 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 162, query id 31856683 eng56.telenav.com 192.168.101.139 rts_user Updating
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1030'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 712489904 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 161, query id 31856673 eng56.telenav.com 192.168.101.139 rts_user Updating
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1003'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 713669552 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 160, query id 31856663 eng56.telenav.com 192.168.101.139 rts_user Updating
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1005'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 714849200 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 159, query id 31856653 eng56.telenav.com 192.168.101.139 rts_user Updating
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1013'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 716028848 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 158, query id 31856643 eng56.telenav.com 192.168.101.139 rts_user Updating
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1035'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 717208496 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 157, query id 31856633 eng56.telenav.com 192.168.101.139 rts_user Updating
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1025'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 753077168 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 156, query id 31856623 eng56.telenav.com 192.168.101.139 rts_user Updating
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1045'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 8448944 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 155, query id 31856613 eng56.telenav.com 192.168.101.139 rts_user Updating
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1044'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 9190320 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 154, query id 31856603 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1011'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 11508656 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 153, query id 31856593 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1015'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 10316720 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 152, query id 31856587 eng56.telenav.com 192.168.101.139 rts_user Updating
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1027'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 749538224 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 151, query id 31856577 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1032'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 8575920 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 150, query id 31856563 eng56.telenav.com 192.168.101.139 rts_user Updating
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1021'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 747178928 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 149, query id 31856557 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1028'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 8936368 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 148, query id 31856547 eng56.telenav.com 192.168.101.139 rts_user Updating
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1047'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 757795760 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 146, query id 31856533 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1031'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 12270512 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 147, query id 31856532 eng56.telenav.com 192.168.101.139 rts_user Updating
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1041'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 769592240 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 145, query id 31856513 eng56.telenav.com 192.168.101.139 rts_user Updating
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1039'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 763694000 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 144, query id 31856503 eng56.telenav.com 192.168.101.139 rts_user Updating
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1024'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 761334704 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 143, query id 31856473 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1037'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 10697648 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 142, query id 31856463 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1010'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 10570672 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 141, query id 31856453 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1048'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 11930544 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 140, query id 31856443 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1044'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 5868464 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 139, query id 31856433 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1023'
---TRANSACTION 0 16528038, not started, process no 19752, OS thread id 9919408 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 138, query id 31856417 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1029'
---TRANSACTION 0 16528032, not started, process no 19752, OS thread id 9718704 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 137, query id 31856407 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1033'
---TRANSACTION 0 16528786, not started, process no 19752, OS thread id 8199088 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 135, query id 31856387 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1012'
---TRANSACTION 0 16906437, not started, process no 19752, OS thread id 7945136 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 134, query id 31856359 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1035'
---TRANSACTION 0 16528405, not started, process no 19752, OS thread id 762514352 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 128, query id 31856399 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1026'
---TRANSACTION 0 16528962, not started, process no 19752, OS thread id 9444272 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 127, query id 31856379 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1001'
---TRANSACTION 0 16906457, not started, process no 19752, OS thread id 5995440 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 125, query id 31856351 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1002'
---TRANSACTION 0 16530155, not started, process no 19752, OS thread id 7674800 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 124, query id 31856377 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1041'
---TRANSACTION 0 16527875, not started, process no 19752, OS thread id 8072112 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 122, query id 31856419 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1024'
---TRANSACTION 0 16527832, not started, process no 19752, OS thread id 11635632 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 121, query id 31856411 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1007'
---TRANSACTION 0 16527874, not started, process no 19752, OS thread id 758975408 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 119, query id 31856423 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1003'
---TRANSACTION 0 16528040, not started, process no 19752, OS thread id 11381680 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 118, query id 31856404 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1005'
---TRANSACTION 0 16528588, not started, process no 19752, OS thread id 756616112 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 117, query id 31856392 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1034'
---TRANSACTION 0 16528428, not started, process no 19752, OS thread id 754256816 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 116, query id 31856395 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1038'
---TRANSACTION 0 16904322, not started, process no 19752, OS thread id 9317296 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 114, query id 31856367 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1006'
---TRANSACTION 0 16906441, not started, process no 19752, OS thread id 10824624 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 111, query id 31856363 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1009'
---TRANSACTION 0 16528766, not started, process no 19752, OS thread id 10046384 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 110, query id 31856383 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1000'
---TRANSACTION 0 16530156, not started, process no 19752, OS thread id 9063344 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 109, query id 31856371 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1045'
---TRANSACTION 0 15688502, not started, process no 19752, OS thread id 10173360 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 88, query id 31856491 eng56.telenav.com 192.168.101.139 rts_user Updating
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1042'
---TRANSACTION 0 15689463, not started, process no 19752, OS thread id 134388656 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 87, query id 31856477 eng56.telenav.com 192.168.101.139 rts_user Updating
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1048'
---TRANSACTION 0 16427756, not started, process no 19752, OS thread id 768412592 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 84, query id 31856355 eng56.telenav.com 192.168.101.139 rts_user Updating
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1040'
---TRANSACTION 0 15688498, not started, process no 19752, OS thread id 751897520 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 81, query id 31856490 eng56.telenav.com 192.168.101.139 rts_user Updating
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1049'
---TRANSACTION 0 16906510, not started, process no 19752, OS thread id 8793008
mysql tables in use 1, locked 1
MySQL thread id 80, query id 31856310 eng56.telenav.com 192.168.101.139 rts_user end
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1012'
---TRANSACTION 0 15688500, not started, process no 19752, OS thread id 755436464 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 77, query id 31856493 eng56.telenav.com 192.168.101.139 rts_user Updating
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1028'
---TRANSACTION 0 15688501, not started, process no 19752, OS thread id 750717872 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 74, query id 31856492 eng56.telenav.com 192.168.101.139 rts_user Updating
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1023'
---TRANSACTION 0 16906520, ACTIVE 157674 sec, process no 19752, OS thread id 10443696, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 320
MySQL thread id 83, query id 31856347 eng56.telenav.com 192.168.101.139 rts_user end
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1036'
---TRANSACTION 0 16906519, ACTIVE 157674 sec, process no 19752, OS thread id 749411248, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 320
MySQL thread id 75, query id 31856346 eng56.telenav.com 192.168.101.139 rts_user end
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1000'
---TRANSACTION 0 16906518, ACTIVE 157674 sec, process no 19752, OS thread id 10951600, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 320
MySQL thread id 79, query id 31856345 eng56.telenav.com 192.168.101.139 rts_user end
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1032'
---TRANSACTION 0 16906517, ACTIVE 157674 sec, process no 19752, OS thread id 11803568, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 320
MySQL thread id 126, query id 31856335 eng46.telenav.com 192.168.100.146 rts_user end
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1049'
---TRANSACTION 0 16906516, ACTIVE 157674 sec, process no 19752, OS thread id 5741488, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 320
MySQL thread id 108, query id 31856331 eng46.telenav.com 192.168.100.146 rts_user end
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1039'
---TRANSACTION 0 16906515, ACTIVE 157674 sec, process no 19752, OS thread id 767232944, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 320
MySQL thread id 82, query id 31856328 eng56.telenav.com 192.168.101.139 rts_user end
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1004'
---TRANSACTION 0 16906514, ACTIVE 157674 sec, process no 19752, OS thread id 11254704, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 320
MySQL thread id 85, query id 31856326 eng56.telenav.com 192.168.101.139 rts_user end
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1034'
---TRANSACTION 0 16906513, ACTIVE 157674 sec, process no 19752, OS thread id 766053296, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 320
MySQL thread id 76, query id 31856314 eng56.telenav.com 192.168.101.139 rts_user end
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1026'
---TRANSACTION 0 16906512, ACTIVE 157674 sec, process no 19752, OS thread id 760155056, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 320
MySQL thread id 86, query id 31856312 eng56.telenav.com 192.168.101.139 rts_user end
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1001'
---TRANSACTION 0 16906511, ACTIVE 157674 sec, process no 19752, OS thread id 12397488, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 320
MySQL thread id 78, query id 31856311 eng56.telenav.com 192.168.101.139 rts_user end
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1010'
--------
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 (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
56 OS file reads, 31114 OS file writes, 31088 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 for space 0: size 1, free list len 0, seg size 2, is empty
Ibuf for space 0: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 7470761, used cells 175, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 3001026743
Log flushed up to 0 3001026743
Last checkpoint at 0 3001026743
0 pending log writes, 0 pending chkp writes
22707 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 2063312448; in additional pool allocated 5810176
Buffer pool size 115200
Free buffers 115112
Database pages 87
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 86, created 1, written 4221
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
--------------
ROW OPERATIONS
--------------
10 queries inside InnoDB, 50 queries in queue
1 read views open inside InnoDB
Main thread process no. 19752, id 1078094768, state: waiting for server activity
Number of rows inserted 44, updated 0, deleted 0, read 4730251
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================