Poor performance vs. MSSQL with JDBC
Posted by: David Wynter
Date: April 04, 2005 09:49AM

I am adding support for MySQL to my ETL tool. It uses JDBC and loads a coupla millions records at a time. Before someone says use LOAD DATA INFILE, I have value substitution and data cleansing going on, so not suitable.

I have a record with a denormailised structure, parent, child and grandchild. Since this Db has ref integrity I do parent PreparedStatement insert with ON DUPLICATE KEY UPDATE, then commit, then child PreparedStatement insert with ON DUPLICATE KEY UPDATE, then commit, finally I did grandchild with batch insert commit, repeat.

But I found the performance was 2 orders of magnitude slower than MS SQL using the same strategy. Some queries taikng 150 seconds?. I am using logSlowQueries=true, useUsageAdvisor=true and gatherPerfMetrics=true. I use SHOW InnoDb status; when it is taking 150 seconds for a query. But I have to say the results don't give me the reason for the slow access, I assume some spurious lock, (given the atomic nature of each set of transactions).

So after a bit of searching came across Kevin Burtons experimentation with allowMultiQueries. Added support for that. For the test I build a StringBuffer with 500 queries each about 1000 char long. About 40 minutes go I stepped past the execute for the statement with the multi query String passed in, it still has not returned. Here is the results from the SHOW InnoDb Status;


=====================================
050404 16:14:48 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 1 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2514, signal count 2498
Mutex spin waits 3644, rounds 34625, OS waits 976
RW-shared spins 1547, OS waits 774; RW-excl spins 683, OS waits 683
------------
TRANSACTIONS
------------
Trx id counter 0 1333904
Purge done for trx's n:o < 0 1333808 undo n:o < 0 0
History list length 13
Total number of lock structs in row lock hash table 17
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, OS thread id 2448
MySQL thread id 188, query id 879767 localhost 127.0.0.1 root
SHOW INNODB STATUS
---TRANSACTION 0 1333806, not started, OS thread id 3404
MySQL thread id 183, query id 875216 ROAMWARE-2EG62K 192.168.0.6 tLoader
---TRANSACTION 0 1331806, not started, OS thread id 1704
MySQL thread id 181, query id 872119 ROAMWARE-2EG62K 192.168.0.6 tLoader
---TRANSACTION 0 1333903, not started, OS thread id 2344
MySQL thread id 179, query id 879762 ROAMWARE-2EG62K 192.168.0.6 tLoader
---TRANSACTION 0 0, not started, OS thread id 2456
MySQL thread id 96, query id 796146 localhost 127.0.0.1 root
---TRANSACTION 0 0, not started, OS thread id 2692
MySQL thread id 20, query id 879750 localhost 127.0.0.1 root
---TRANSACTION 0 1333808, ACTIVE 1723 sec, OS thread id 2544
20 lock struct(s), heap size 2496, undo log entries 35
MySQL thread id 182, query id 875272 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
2185 OS file reads, 147896 OS file writes, 124037 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 54102, node heap has 57 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 173933211
Log flushed up to 0 173933211
Last checkpoint at 0 173933211
0 pending log writes, 0 pending chkp writes
122367 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 189003944; in additional pool allocated 1045760
Buffer pool size 10560
Free buffers 5606
Database pages 4897
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 2175, created 2722, written 24583
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 1740, state: sleeping
Number of rows inserted 111013, updated 61013, deleted 54431, read 955195
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 2.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

Can someone explain what I am looking for here?

Another thing, looked into bugs list could not find the bug related to replication not working for when the Master is using multiQueries. Is it on the horizon to be fixed?

thx,

David

Options: ReplyQuote


Subject
Written By
Posted
Poor performance vs. MSSQL with JDBC
April 04, 2005 09:49AM


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.