当前位置：网站首页>Kettle optimization to improve MySQL read and write speed

Kettle optimization to improve MySQL read and write speed

2022-07-21 04:39:00 【Drinking wine under the lone shadow moon】

1. background

background ： stay mysql Data migration in the database ( Migration between tables , Field conversion ), When you find a large amount of data in the table ,kettle The speed of reading and writing tables is very slow , And the speed will be particularly slow after reaching a certain amount of data , When the amount of data is small, the reading speed is very fast .

2.kettle Perform speed measurement

2.1 Read and write data speed test

5000 Data reading and writing speed test ：8min24s namely 504s

$[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-MTVia6sM-1629959050694)(C:\Users\hbwhx\Desktop\ Study \01_kettle\kettle Optimize it to improve the reading and writing speed .assets\image-20210825182632425.png)]$

20000 Data reading and writing speed test : 16min32s namely 992s

$[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-ZL9T6Cf5-1629959050695)(C:\Users\hbwhx\Desktop\ Study \01_kettle\kettle Optimize it to improve the reading and writing speed .assets\image-20210826100752586.png)]$

200000 Data reading and writing speed test , The task fails directly , Can't run .

3. To optimize the direction

3.1 Modify database connection parameters

Open the database connection of table input and table output and add the following parameters ： Table input or table output => Edit database connection => Options

Named parameters	value	Parameter description
useCompression	true	Turn on data compression during transmission , Improve transmission efficiency
rewriteBatchedStatements	true	Rearrange the database Insert sentence , Merge multiple insert statements into one , Submit insertion efficiency
useServerPrepStmts	false	Rearrange the database Insert sentence , Merge multiple insert statements into one , Submit insertion efficiency

$[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-ugvb7KNJ-1629959050696)(C:\Users\hbwhx\Desktop\ Study \01_kettle\kettle Optimize it to improve the reading and writing speed .assets\image-20210826105728059.png)]$

5000 Read and write results of pieces of data ：3.1s

$[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-0WZHoacZ-1629959050698)(C:\Users\hbwhx\Desktop\ Study \01_kettle\kettle Optimize it to improve the reading and writing speed .assets\image-20210826112404617.png)]$

20000 Data reading and writing speed test : 11.6s

$[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-458bdWsI-1629959050699)(C:\Users\hbwhx\Desktop\ Study \01_kettle\kettle Optimize it to improve the reading and writing speed .assets\image-20210826112656215.png)]$

200000 Data reading and writing speed test : 2min18s namely 138s

$[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-6xj7hu8L-1629959050700)(C:\Users\hbwhx\Desktop\ Study \01_kettle\kettle Optimize it to improve the reading and writing speed .assets\image-20210826113202179.png)]$

After the modification of database parameters , Reading and writing efficiency has been significantly improved 86% above .

3.2 kettle modify spoon.bat Running memory size of ( On the basis of modifying data parameters )

stay kettle Edit under the installation directory Spoon.bat file , Find the following parameters ：

#  The default value is 
if "%PENTAHO_DI_JAVA_OPTIONS%"=="" set PENTAHO_DI_JAVA_OPTIONS="-Xms1024m" "-Xmx2048m" "-XX:MaxPermSize=256m"
#  Modified value ( Modify according to your computer configuration )
"%PENTAHO_DI_JAVA_OPTIONS%"=="" set PENTAHO_DI_JAVA_OPTIONS= "-Xmx4096m" "-XX:MaxPermSize=4096m"
#  Parameters, 
# "-Xms4096m"： To configure java The size of the initial memory allocation in the virtual machine heap area   Not configured 
# "-Xmx4096m"： To configure java Maximum amount of virtual machine heap memory that can be allocated 
# "-XX:MaxPermSize=4096m"： To configure java The maximum limit of memory allocated to non heap areas

Be careful ：
Development process , Usually will -Xms And -Xmx The configuration of the two parameters is the same , The aim is to be able to java After the garbage collection mechanism cleans up the heap area, it does not need to re separate and calculate the size of the heap area to waste resources . Before configuration, you must carefully consider the size of non heap memory required by your software , Because the memory here will not be java Where the garbage collection mechanism handles it . And more importantly, the sum of the maximum heap memory and the maximum non heap memory must not exceed the available memory of the operating system .

$[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-IoJZcAHj-1629959050701)(C:\Users\hbwhx\Desktop\ Study \01_kettle\kettle Optimize it to improve the reading and writing speed .assets\image-20210826114757811.png)]$

The number of submitted records is changed to 10000, That is, each submission 10000 Bar record
$[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-bwQtInrf-1629959050702)(C:\Users\hbwhx\Desktop\ Study \01_kettle\kettle Optimize it to improve the reading and writing speed .assets\image-20210826134534557.png)]$

5000 Read and write results of pieces of data ：3.2s

Insert picture description here

20000 Data reading and writing speed test : 10.6s

$[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-9vuhI8dh-1629959050703)(C:\Users\hbwhx\Desktop\ Study \01_kettle\kettle Optimize it to improve the reading and writing speed .assets\image-20210826141839496.png)]$

200000 Data reading and writing speed test : 2min1s namely 121s

$[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-iZerXeoD-1629959050704)(C:\Users\hbwhx\Desktop\ Study \01_kettle\kettle Optimize it to improve the reading and writing speed .assets\image-20210826134859610.png)]$

Compare the results , With sufficient resources , The connection parameters of the database have a great impact on the reading speed .

3. The effect of optimization ( Read 840 Time consuming of 10000 data )

8488225 Data reading and writing speed test : 1h 55min 10s namely 6910s

$[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-L24dMaOj-1629959050704)(C:\Users\hbwhx\Desktop\ Study \01_kettle\kettle Optimize it to improve the reading and writing speed .assets\image-20210825151950584.png)]$

4. summary

Use kettle You must pay attention to ：

1、 Check the driver version of the connection database , To do it jar The package version and database version should be as close as possible ;

2、 Create a database connection url The following two parameters must be added to ：

?autoReconnect=true&failOverReadOnly=false

3、 The following three parameters must be added to the parameters for creating a database connection ：（ Used to increase data writing speed ）

useServerPrepStmts=false
rewriteBatchedStatements=true
useCompression=true

4、 Create database connection , If the connection pool used , You have to set maxIdl It's worth more ：

5、 Must be based on kettle The hardware where the software resides , modify spoon.bat(window) or spoon.sh(linux) The memory size of ：
Insert picture description here

原网站

版权声明
本文为[Drinking wine under the lone shadow moon]所创，转载请带上原文链接，感谢
https://yzsam.com/2022/202/202207200530411378.html