• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

load data local in file is very slow

 
Mittal Swati
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

My java application is reading data from one DB table and create CSV files and call LOAD DATA LOCAL INFILE to import into another table. This process is called inside a loop and LOAD DATA LOCAL INFILE is calling approx 50 times inside a loop in whole application.

My issue is LOAD DATA LOCAL INFILE command is very slow in starting (it took approx 10 min for few files even all CSV file size is almost same). Than this execution time is reduced to 2-3 sec for each CSV file. What is root cause for this? Is LOAD DATA LOCAL INFILE stores some information in mysql db cache and reuse it ?

Please help me how I can debug this problem.

Thanks !!
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Where is the file in relation to the database?
Is it having to be transferred over the network at all?

How big (in Mb) are the files?
 
Mittal Swati
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
All Files are created on Local computer and DB is also available in same PC.

Each file size is approx 12-15 MB. Only first 2-3 files processing is slow other files are executing within 2 second.
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sorry, that was my one guess.
Do the db logs show anything during the long uploads?
Or anything on SHOW ENGINE INNODB STATUS maybe?
 
Mittal Swati
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
will SHOW ENGINE INNODB STATUS help? As tables are using MYISAM engine.

No information given in slow log inside DB logs. If I am executing same query inside DB than its taking less than 1 sec. but same is taking 10 min first time inside java.
 
Mittal Swati
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Please see below status output of SHOW ENGINE INNODB STATUS -


=====================================
2014-10-28 10:39:12 196c INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 60 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 45892 srv_active, 0 srv_shutdown, 42978 srv_idle
srv_master_thread log flush and writes: 88870
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 846
OS WAIT ARRAY INFO: signal count 842
Mutex spin waits 480, rounds 10166, OS waits 328
RW-shared spins 563, rounds 16495, OS waits 505
RW-excl spins 1, rounds 388, OS waits 11
Spin rounds per wait: 21.18 mutex, 29.30 RW-shared, 388.00 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 1676826
Purge done for trx's n:o < 1672315 undo n:o < 0 state: running but idle
History list length 361
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 611, OS thread handle 0x196c, query id 156724624 localhost ::1 raj init
SHOW ENGINE INNODB STATUS
---TRANSACTION 0, not started
MySQL thread id 610, OS thread handle 0xa0c, query id 156724506 localhost ::1 raj cleaning up
---TRANSACTION 1676825, not started
MySQL thread id 605, OS thread handle 0x78, query id 156724191 localhost 127.0.0.1 raj cleaning up
---TRANSACTION 0, not started
MySQL thread id 581, OS thread handle 0x1ec8, query id 156724539 localhost 127.0.0.1 raj cleaning up
--------
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
1434 OS file reads, 10186 OS file writes, 6847 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, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 581149, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 2221392066
Log flushed up to 2221392066
Pages flushed up to 2221392066
Last checkpoint at 2221392066
0 pending log writes, 0 pending chkp writes
5634 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 300482560; in additional pool allocated 0
Dictionary memory allocated 218457
Buffer pool size 17920
Free buffers 16053
Database pages 1866
Old database pages 673
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 350, not young 28
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1192, created 2793, written 3826
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1866, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Main thread id 3260, state: sleeping
Number of rows inserted 955484, updated 0, deleted 0, read 111978401444
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic