MySQL Forums
Forum List  »  General

Re: Import of SQL file slow or freezes
Posted by: Craig Eidson
Date: December 28, 2018 12:29PM

As a consultant I have several irons in the fire at a given time. I cut my losses back in August and imported very small portions of my database to get by. I'm back to continue debugging this.


Yes, I reset the server after setting ibpsize. I typically reset the server after setting most variables just to make sure they have been applied persistently.

SET sql_log_bin = OFF improved my import speed from ~10 inserts/second to about ~25 inserts/second. So that's good. At 1M samples I can now import my table in 10 Hrs instead of 27 hrs.

IO Benchmark:

diskspd -c1M -w100 -Swu diskspd.dat

Input parameters:

timespan: 1
-------------
duration: 10s
warm up time: 5s
cool down time: 0s
random seed: 0
path: 'diskspd.dat'
think time: 0ms
burst size: 0
software cache disabled
hardware write cache disabled, writethrough on
performing write test
block size: 65536
using sequential I/O (stride: 65536)
number of outstanding I/O operations: 2
thread stride size: 0
threads per file: 1
using I/O Completion Ports
IO priority: normal

System information:

computer name: WINDOWS-31BGMID
start time: 2018/12/28 18:24:14 UTC

Results for timespan 1:
*******************************************************************************

actual test time: 10.01s
thread count: 1
proc count: 8

CPU | Usage | User | Kernel | Idle
-------------------------------------------
0| 14.69%| 0.78%| 13.91%| 85.31%
1| 0.16%| 0.00%| 0.16%| 99.84%
2| 0.16%| 0.00%| 0.16%| 99.84%
3| 0.16%| 0.00%| 0.16%| 99.84%
4| 0.00%| 0.00%| 0.00%| 100.00%
5| 0.00%| 0.00%| 0.00%| 100.00%
6| 0.16%| 0.16%| 0.00%| 99.84%
7| 1.25%| 1.25%| 0.00%| 98.75%
-------------------------------------------
avg.| 2.07%| 0.27%| 1.80%| 97.93%

Total IO
thread | bytes | I/Os | MiB/s | I/O per s | file
------------------------------------------------------------------------------
0 | 3933339648 | 60018 | 374.85 | 5997.63 | diskspd.dat (1024KiB)
------------------------------------------------------------------------------
total: 3933339648 | 60018 | 374.85 | 5997.63

Read IO
thread | bytes | I/Os | MiB/s | I/O per s | file
------------------------------------------------------------------------------
0 | 0 | 0 | 0.00 | 0.00 | diskspd.dat (1024KiB)
------------------------------------------------------------------------------
total: 0 | 0 | 0.00 | 0.00

Write IO
thread | bytes | I/Os | MiB/s | I/O per s | file
------------------------------------------------------------------------------
0 | 3933339648 | 60018 | 374.85 | 5997.63 | diskspd.dat (1024KiB)
------------------------------------------------------------------------------
total: 3933339648 | 60018 | 374.85 | 5997.63

Options: ReplyQuote




Sorry, only registered users may post in this forum.

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.