Improving Pentaho Data Integration Performance on MySQL

Introduction

Pentaho Data Integration works well with MySQL, however, some adjustments will be necessary.

Background

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.

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

useServerPrepStmts=false
rewriteBatchedStatements=true
useCompression=true

Using the Code

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

You can find the original article here.