Pentaho PDI (Spoon): MySQL table output very slow (~2000 rows/s)

Question

My table output step is terribly slow (~2.000 rows/second), compared to the input (100.000-200.000 rows/second). The MySQL server is not the problem, using native MySQL, e.g. with the "Execute SQL script" step, I get something in the 100thousands/second. I already tried (without success) the common solution of extending the SQL options by:

useServerPrepStmts=false
rewriteBatchedStatements=true
useCompression=true

I also varied the commit size parameter (100, 1.000, 10.000) and Use batch updates for inserts is enabled, also without success. What else can I do? I have tables with ~10.000.000 rows and Pentaho runs on a very potent machine, so this is not acceptable.

Are you using any filters in the WHERE clause? – shzyincu Aug 02 '16 at 12:51 — shzyincu, Aug 02 '16 at 12:51

score 0 · Answer 1 · answered Jul 29 '16 at 18:41

0

For this I think the ideal step is MySQL Bulk Loader step which is listed under Bulk loading section. Along with that use the said

useServerPrepStmts=false
rewriteBatchedStatements=true
useCompression=true

in JDBC options in the connection. These useCompression will compress the traffic between the client and the MySQL server where as other two will form INSERT INTO tbl (a,b) VALUES (1,'x'),(2,'y'),(3,'z'); without using separate insert statements for each.

answered Jul 29 '16 at 18:41

Marlon Abeykoon

11,927
4
54
75

Unfortunately, my development machine is a Windows Server 2012 R2, so I'm getting [this error](http://jira.pentaho.com/browse/PDI-2756). – Juergen Jul 29 '16 at 19:01

score 0 · Answer 2 · answered Jan 04 '17 at 11:00

0

Follow these steps:

Increase the RAM Size for PDI a.k.a Spoon.
Using the Command line utility such as ( Kitchen or Pan) run your Job or Transformation.

Well Now compare the speed.

Cheers!

answered Jan 04 '17 at 11:00

MAX

1,562
4
17
25

Pentaho PDI (Spoon): MySQL table output very slow (~2000 rows/s)

2 Answers2