Using Pentaho Data Integration (community edition) with MySQL

Greetings!

Pentaho Data Integration is a free ETL tool that is versatile and easy to learn.  While it is very easy to get started with the tool, mastering the tool takes time, and knowing the “tricks” to make it work takes practice.  Having been said, online resources can be very helpful to solve some problems you may be experiencing, but not all of them.

Juilien Hofstede has a blog entry about how to improve MySQL output for table output or update steps:  http://julienhofstede.blogspot.com/2014/02/increase-mysql-output-to-80k-rowssecond.html

In his post, he recommends using the following options for your database connections:

useServerPrepStmts=false
rewriteBatchedStatements=true
useCompression=true

While this will work nicely with native JDBC connections, it will not increase performance for JNDI connections.  For JNDI connections, you will need to code these options into the JNDI URL.  To do so, simply add these options to the jdbc url in your jdbc.properties file like so:

mydb/type=javax.sql.DataSource
mydb/driver=org.gjt.mm.mysql.Driver
mydb/url=jdbc:mysql://localhost:3306/report?rewriteBatchedStatements=true&useServerPrepStmts=false&useCompression=true
mydb/user=root
mydb/password=admin

This will ensure that these options are indeed used by your JNDI connections.