Output of: show innodb status;
| InnoDB | |
=====================================
061207 4:56:43 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 17 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 11349, signal count 11191
Mutex spin waits 236607, rounds 3540022, OS waits 8138
RW-shared spins 2947, OS waits 1347; RW-excl spins 3323, OS waits 1071
------------
TRANSACTIONS
------------
Trx id counter 0 23288266
Purge done for trx's n:o < 0 22593464 undo n:o < 0 0
History list length 0
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 31226, OS thread id 1201588560
MySQL thread id 1695, query id 3078085 localhost root
show innodb status
---TRANSACTION 0 23286033, not started, process no 31226, OS thread id
1181354320
mysql tables in use 1, locked 1
MySQL thread id 1627, query id 3071366 192.168.16.5 jneff update
INSERT INTO data_archive (batchid, claimid, memberid, raw_data)
VALUES ( NAME_CONST('batchID',14),
NAME_CONST('claimID',_latin1'2001012345'),
NAME_CONST('memberID',_latin1'0123456'), COMPRESS(
NAME_CONST('rawData',_latin1'A|12345|E||01|0987|GRD|44556|<|<|<|<|<|<|<|111|19900101|12345678|0K|M|010112001|DOE,JOHN|A||123||||||||||||||||||||||||||}|}|}|}|}|}|}|}|}|AA1234|AA|072582642|AA1234|AA
---TRANSACTION 0 23288265, ACTIVE 6 sec, process no 31226, OS thread id
1210906960 setting auto-inc lock
mysql tables in use 1, locked 1
LOCK WAIT 1 lock struct(s), heap size 3680 row lock(s)
MySQL thread id 1504, query id 3078084 james.kochbenefitservices.local
192.168.16.25 jneff update
INSERT INTO claim (number, line_number, first_dos, last_dos, admit_diag,
primary_diag, secondary_diag, procedure_code, procedure_modifier,
prescription_ndc,
prescription_date, dob, member_id, paid_amount, place_of_service,
batchid, recordid)
VALUES ( NAME_CONST('number',_latin1'2001012345'),
NAME_CONST('line_number',2),
NAME_CONST('first_dos',_latin1'20010101'),
NAME_CONST('last_dos',_latin1'20010101'),
NAME_CONST('admit_diag',_latin1''),
NAME_CONST('primary_diag',_latin1'01234'),
NAME_CONST('secondary_diag',_latin1''),
NAME_CONST('procedure_code',_latin1'123'),
NAME_CONST('p
Trx has approximately 0 row locks
------- TRX HAS BEEN WAITING 6 SEC FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `edi-process`.`claim` trx id 0 23288265 lock mode
AUTO-INC waiting
------------------
---TRANSACTION 0 23288264, ACTIVE 49 sec, process no 31226, OS thread id
1179224400 setting auto-inc lock
mysql tables in use 1, locked 1
LOCK WAIT 1 lock struct(s), heap size 3680 row lock(s)
MySQL thread id 1587, query id 3078078 192.168.16.3 jneff update
INSERT INTO data_archive (batchid, claimid, memberid, raw_data)
VALUES ( NAME_CONST('batchID',14),
NAME_CONST('claimID',_latin1'200102345'),
NAME_CONST('memberID',_latin1'987654321'), COMPRESS(
NAME_CONST('rawData',_latin1'A|200102345|A||01|0011223|ALL|ABC123|<|<|<|<|<|<|<|111|19000101
Trx has approximately 0 row locks
------- TRX HAS BEEN WAITING 49 SEC FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `edi-process`.`data_archive` trx id 0 23288264 lock
mode AUTO-INC waiting
------------------
---TRANSACTION 0 23286035, ACTIVE 56938 sec, process no 31226, OS thread
id 1159522640, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 3680 row lock(s), undo log entries 1
MySQL thread id 1545, query id 3071372 appserver2 192.168.16.4 jneff update
INSERT INTO data_archive (batchid, claimid, memberid, raw_data)
VALUES ( NAME_CONST('batchID',14),
NAME_CONST('claimID',_latin1'2005011407407359'),
NAME_CONST('memberID',_latin1'04832343901'), COMPRESS(
NAME_CONST('rawData',_latin1'A|20010114|A||01|001234ALL|65432|<|<|<|<|<|<|<||19010102|56789|0A|B|098766|DOE,JOHN|S||09876||||||||||||||||||||||||||}|}|}|}|}|}|}|}|}|AB65432|0
Trx has approximately 0 row locks
---TRANSACTION 0 23286032, ACTIVE (PREPARED) 56938 sec, process no
31226, OS thread id 1201056080
mysql tables in use 1, locked 1
2 lock struct(s), heap size 3680 row lock(s), undo log entries 1
MySQL thread id 1460, query id 3071363 appserver 192.168.16.9 jneff update
INSERT INTO claim (number, line_number, first_dos, last_dos, admit_diag,
primary_diag, secondary_diag, procedure_code, procedure_modifier,
prescription_ndc,
prescription_date, dob, member_id, paid_amount, place_of_service,
batchid, recordid)
VALUES ( NAME_CONST('number',_latin1'0123445'),
NAME_CONST('line_number',3),
NAME_CONST('first_dos',_latin1'20010102'),
NAME_CONST('last_dos',_latin1'20010102'),
NAME_CONST('admit_diag',_latin1''),
NAME_CONST('primary_diag',_latin1'1234'),
NAME_CONST('secondary_diag',_latin1'12345'),
NAME_CONST('procedure_code',_latin1'54321'),
NAME_CONST('p
Trx has approximately 0 row locks
--------
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
7515 OS file reads, 1661069 OS file writes, 1653447 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 0, seg size 2,
21 inserts, 21 merged recs, 2 merges
Hash table size 17393, used cells 4, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 4 2760569219
Log flushed up to 4 2760569219
Last checkpoint at 4 2760569219
0 pending log writes, 0 pending chkp writes
1650904 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 22615268; in additional pool allocated 1045760
Dictionary memory allocated 273640
Buffer pool size 512
Free buffers 1
Database pages 510
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 45294, created 16839, written 66895
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
--------------
ROW OPERATIONS
--------------
1 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 31226, id 1157658960, state: sleeping
Number of rows inserted 823704, updated 0, deleted 1146493, read 26198146
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
|
Ben de Luca wrote:
What does a `show innodb status show`?
On 07/12/2006, at 2:44 AM, James Neff wrote:
Greetings,
I've got 6 Java client applications (running on 6 different PC's,
including one of them on the database server) reading data from a
flat file and inserting data into a table on my database:
mysqld Ver 5.1.12-beta-log for unknown-linux-gnu on x86_64 (MySQL
Community Server (GPL))
There are total about 12 million lines of data to read from these
flat files and INSERT into a Innodb table on the database. Each
client application has 20 threads running which run some validation
code on the record and then perform the insert. I have not exceeded
my max_connections.
Around insert number 450,000 some of the clients started getting the
following error:
Exception in Processor.DataArchiveInsert, trying to insert into
data_archve, e:
java.sql.SQLException: Lock wait timeout exceeded; try restarting
transaction
The 4 clients which did not recieve the error have stopped (but have
not thrown errors) and when I view the connections from MySQL
Administrator I see 4 INSERT statements to the same table just which
have been running for 40926 seconds.
The code for this is a simple INSERT statement in Java :
mysqlStatement.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
ResultSet rsRecordsInsert =
mysqlStatement.getGeneratedKeys();
while (rsRecordsInsert.next())
{
recordID = rsRecordsInsert.getInt(1);
}
rsRecordsInsert.close();
There are no errors in the databases error log nor anything in the
slow log.
I don't even know what exactly is wrong here other than "it ain't
working."
Can someone lead me down the path to diagnose this problem and also
fix it?
Thanks in advance,
--
James Neff
--MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
James Neff
Technology Specialist
Tethys Health Ventures
4 North Park Drive, Suite 203
Hunt Valley, MD 21030
office: 410.771.0692 x103
cell: 443.865.7874
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
