Re: Poor performance vs. MSSQL with JDBC
Posted by: David Wynter
Date: April 05, 2005 11:39AM

By dropping the number of queries in the string to just 20, about 21K bytes long the Statement execute no longer hangs. However after the first successful execute I build the next string with the next 20 queries and execute this, it hangs?

Here is the content of the SHOW INNODB STATUS


=====================================
050405 18:33:30 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 22 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 100, signal count 100
Mutex spin waits 74, rounds 633, OS waits 17
RW-shared spins 92, OS waits 46; RW-excl spins 39, OS waits 35
------------
TRANSACTIONS
------------
Trx id counter 0 1340631
Purge done for trx's n:o < 0 1340613 undo n:o < 0 0
History list length 12
Total number of lock structs in row lock hash table 11
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, OS thread id 1032
MySQL thread id 94, query id 15846 localhost 127.0.0.1 root
show innodb status
---TRANSACTION 0 1340611, not started, OS thread id 1932
MySQL thread id 90, query id 15645 ROAMWARE-2EG62K 192.168.0.6 tLoader
---TRANSACTION 0 1340451, not started, OS thread id 2188
MySQL thread id 88, query id 15381 ROAMWARE-2EG62K 192.168.0.6 tLoader
---TRANSACTION 0 1340324, not started, OS thread id 2196
MySQL thread id 87, query id 15093 ROAMWARE-2EG62K 192.168.0.6 tLoader
---TRANSACTION 0 1340630, not started, OS thread id 2204
MySQL thread id 85, query id 15841 ROAMWARE-2EG62K 192.168.0.6 tLoader
---TRANSACTION 0 0, not started, OS thread id 1024
MySQL thread id 1, query id 5007 localhost 127.0.0.1 root
---TRANSACTION 0 0, not started, OS thread id 1928
MySQL thread id 7, query id 10738 localhost 127.0.0.1 root
---TRANSACTION 0 1340613, ACTIVE 289 sec, OS thread id 2192
14 lock struct(s), heap size 1024, undo log entries 15
MySQL thread id 89, query id 15661 ROAMWARE-2EG62K 192.168.0.6 tLoader
--------
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 (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
785 OS file reads, 2897 OS file writes, 2122 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 684923, used cells 13673, node heap has 15 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 174323441
Log flushed up to 0 174323441
Last checkpoint at 0 174323441
0 pending log writes, 0 pending chkp writes
1980 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 189003944; in additional pool allocated 1141760
Buffer pool size 10560
Free buffers 9768
Database pages 777
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 775, created 2, written 846
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
Main thread id 1584, state: waiting for server activity
Number of rows inserted 160, updated 1862, deleted 80, read 36118
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.09 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

What in this gives me details of why it hangs? Assume it is this
14 lock struct(s), heap size 1024, undo log entries 15
MySQL thread id 89, query id 15661 ROAMWARE-2EG62K 192.168.0.6 tLoader

But I want to find put which tables is casuing this lock, how do I do that?

Thx.

David

Options: ReplyQuote


Subject
Written By
Posted
Re: Poor performance vs. MSSQL with JDBC
April 05, 2005 11:39AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.