Infobright Blog

Infobright is built upon the MySQL architecture and, although we typically recommend the Infobright Loader (and/or the Distributed Load Processor, DLP, with Infobright Enterprise Edition), frequently questions arise as to what connectivity tools can be used in different circumstances for data access and efficient insertion of data into Infobright. As the MySQL website states:

"MySQL provides connectivity for client applications developed in the Java programming language through a JDBC driver, which is called MySQL Connector/J. MySQL Connector/J is a JDBC Type 4 driver. Different versions are available that are compatible with the JDBC 3.0 and JDBC 4.0 specifications. The Type 4 designation means that the driver is pure-Java implementation of the MySQL protocol and does not rely on the MySQL client libraries."

We recently had a customer who asked us a question about fetching larger sets of data via the MySQL JDBC driver for insertion into Infobright. They told us that this driver worked fine for smaller data sets but their business use case required handling at least one million rows of data.

Their specific inquiry was whether they could specify a larger fetch size of at least one thousand records. Alternatively, they asked us if we could provide a JDBC driver other than the MySQL driver or if we could recommend some other solution.

Initially, we told him that there were a few options: looking at alternate drivers that could be used such as Connector/J, Datadirect JDBC, and even the Drizzle JDBC Driver; streaming the data using JDBC (as opposed to Buffering); or, moving the Java service to another server to avoid memory contention. We also specified that there are known issues with JDBC and memory and that there was a good write-up about the JDBC driver from one of our former sales engineers which could be read at the following link:

http://www.infobright.org/Blog/Entry/infobright_mysql_and_jdbc/

However, this customer was emphatic about getting their Java application to connect to Infobright using the JDBC driver; specifically, J/Connector 5.1.18. A couple of weeks after their initial inquiry, they specified that they tested setting the fetch size to 1000, 100 or 10 (the latter containing a very large result set) and, in all three cases, getting an "out-of-memory" exception error.

They found a workaround to avoid the exceptions. This workaround is based on setting the fetch size to INTEGER.MIN; thereafter, the JDBC driver returns 1 row at a time from the existing result set. This works well as long as the result set is small; but this would be a concern if the result set is really large.

In response to this, I did some research on the INTEGER.MIN setting and whether there was a better "streaming" option. After my research was completed, I responded with the following:

"...the main options for avoiding memory contention between the database and JDBC connections are to either stream the results to the application rather than buffer, or isolate the Java service onto a separate VM. If you end up having a large result set, simply moving the Java service to another server has worked consistently so make sure to do that."

After further review, the problem was not inherently in the JDBC driver, but in the configuration.
A review of the issue can be found here: http://bugs.mysql.com/bug.php?id=18148

By default, MySQL + the Connector/J JDBC driver for MySQL will return the entire result set at once, filling the JVM memory even before stepping through the results set.

ResultSet rs=stmt.execute(queryString);
The variable "rs" now contains the entire result set at once. According to the JDBC API, setting the fetch size will provide a hint to the driver and database to return the results in blocks based on the fetch size so that you do not need to hold the entire result set in memory at once.

The work-around recommended was to setFetchSize(INTEGER.MIN) which will force the results to stream one row at a time. This impacts performance due to network latency for each fetch. It's better to retrieve in reasonably sized chunks to avoid network latency.

However, we found that an additional configuration setting is available and, if you use it, the JDBC driver will perform as expected and use the setFetchSize as intended by the JDBC API.

If you have Infobright and the MySQL Connector/J Driver 5.0.1 or higher this will work, but you need to add a setting in the JDBC URL when establishing the DB connection.

jdbc:mysql://SERVER:PORT/DBNAME?useCursorFetch=true&defaultFetchSize=1000

This tells MySQL to use a cursor to step through the results and sets the connection with a default fetch size. After that, you can actually use the setFetchSize and reset it as needed, or simply use the default you specify at the time the connection is made.

On a similar note, a different customer was experiencing very slow bulk load rates into a specific Infobright table which he described as follows:

"What we basically do is read the data from Oracle, and then load it to MySQL in bulk sets of 50,000 records. We are using 'ojdbc6.jar' to read from Oracle, and 'mysql-connector-java-5.1.18-bin.jar' to write to MySQL. We would appreciate any suggestions you might have to improve the loading time."

Initially, I thought that this customer could benefit from the first customer's experience in setting the default Fetch Size higher. However, this latter customer came back to me and explained that the problem was the insertion speeds into Infobright, not the fetch speeds from Oracle. Again, after researching why loading with the MySQL Connector/J might be slow, I came upon the following three recommendations:

cacheServerConfiguration=true

useLocalSessionState=true

rewriteBatchedStatements=true

The customer tried this last suggestion first and, believe it or not, it made a significant difference in load performance! Here is an explanation of how they tested this recommendation and analyzed the results:

"We were able to use the 'rewriteBatchedStatements=true' configuration with a smaller bulk set of 9000 records (originally we had tried 50,000). With a larger bulk set of greater than 10,000 records, we get the error 'java.sql.SQLException: Prepared statement contains too many placeholders'.

But, with a smaller batch size we see a great improvement when using the 'rewriteBatchedStatements=true' parameter. We saw that we can load 15,800 rows in a second to TABLE1 which is the table that took us the most time to load."

Previously, they were able to load only 2250 rows per second, so they're seeing roughly a 7X performance improvement with this change alone!

I hope these recommendations help if you encounter similar situations in your environment.

With Infobright 4.0 we delivered a number of unique “Big Data” features including Rough Query (link to video) and DomainExpert™ (link to video) to support querying large volumes of machine-generated data. At the same time we also introduced a companion product to Infobright Enterprise Edition, the Distributed Load Processor (DLP) with a Hadoop connector, to enable very fast data load and a simple method to extract data from a Hadoop cluster.  The ultimate goal is to extract and load data into Infobright as quickly as possible to allow for great data growth and “queryability”.  Customers can use these tools to load over 2 terabytes an hour into just one table.   In a previous blog post found here (http://www.infobright.org/Blog/Entry/loading_data_in_infobright_on_amazon_ec2_using_dlp/), we illustrated how to install DLP on an Amazon EC2 instance.  In this blog, we will discuss how to leverage our Hadoop connector in conjunction with DLP.

If you’re new to Hadoop, take a look at the Apache™ Hadoop project (http://hadoop.apache.org/).  Hadoop provides a highly-scalable approach to crunching through insane amounts of data.  The main use case for Hadoop is to deal with near-petabyte+ worth of data.  Hadoop provides a great resource for storing *all* of your detailed data for great lengths of time.  Plus it doesn’t require a schema.  So if your data is semi-structured or unstructured, Hadoop is a great asset in your arsenal.

Where Hadoop tends to fail is with respect to fast analytic response.  While there are technologies that sit atop Hadoop intended for analytics, they do require a level of overhead.  If you’re looking for blazing ad-hoc query speed, you won’t necessarily find it with Hadoop and MapReduce.  To that point, Infobright provides that capability.  By structuring and moving over a subset  of the most highly relevant or even aggregated data into Infobright, you can provide an easy and fast user experience. We are strong advocates of using the right tool for the right job, and the combination of Infobright and Hadoop is a good example. If you are interested, watch the video about LiveRail (link to video on Youtube), which explains how and why they are using both.

Jumping ahead to the specifics, I assume you have Hadoop installed and working properly.  Plus, I assume you’ve installed DLP. (You can download an Enterprise Edition free trial along with a DLP trial at http://www.infobright.com/Products/Product-Demo/ if you don't have a DLP license). Java is also required, so ensure the latest JDK is available.  To utilize our Hadoop connector, a few pre-requisites are required.

These steps were verified using:

Infobright Database: IB_4.0_r13151_13690 (iee-commercial)
Infobright DLP (this includes the Infobright Hadoop Connector): DLP 1.1.0 64Bit
Hadoop: hadoop-0.20.205
Operating System: CentOS 5

1. Setup your environment
   1. Within your environment (typically, ~/.bashrc), define:
      1. [ HADOOP_HOME ] – Location of your Hadoop installation. 
         1. Ex: export HADOOP_HOME=/hadoop/hadoop
      2. [ JAVA_HOME ] – Location of your JDK. 
         1. Ex: export JAVA_HOME=/usr/java/jdk1.6.0_30
      3. [ PATH ] – Include the installation directory of both DLP and the JDK binary.
         1. PATH=$PATH: /opt/infobright/tools/distributed-load-processor:/usr/java/jdk1.6.0_30/bin
         2. Your values will differ if you installed DLP/Java somewhere else
      4. Aliases to make life a little easier.  I set some for Hadoop, DLP, and Hadoop/Infobright plugin.  I use:
         1. alias had=/hadoop/hadoop/bin/hadoop
         2. alias dp=/opt/infobright/tools/distributed-load-processor/dataprocessor
         3. alias had-ib=/opt/infobright/tools/distributed-load-processor/plugins/hadoop-exp/hadoop-exp.sh
2. Ensure data is loaded within your Hadoop cluster
   1. Gutenberg example is popular.  Three books can be downloaded on this site http://wiki.apache.org/hadoop/Running_Hadoop_On_Ubuntu_Linux_(Single-Node_Cluster); search for “Project Gutenberg”.
      1. had dfs -copyFromLocal /hadoop/gutenberg/ gutenberg
   2. Run the Gutenberg Wordcount MapReduce job on the Gutenberg data:
      1. had jar /path/to/hadoop-examples-0.20.205.0.jar wordcount gutenberg /user/hadoop/gutenberg-output
   3. Verify data exists
      1. had fs -ls  /user/hadoop/gutenberg-output/part-r-00000
3. Setup your database in Infobright to hold the Gutenberg data.
   1. Create your database and tables
      1. Create database gutenberg;
      2. Use gutenberg;
      3. CREATE TABLE gutenberg.word_count ( word varchar(500), count mediumint ) ENGINE=BRIGHTHOUSE DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
4. Loading the data into Infobright
   1. I assume you’ll want to directly load it into Infobright, so execute:
      1. had-ib -v -i /user/hadoop/gutenberg-output/part-r-00000 -X --dp-exe /opt/infobright/tools/distributed-load-processor/dataprocessor -H 127.0.0.1 -P 5029 -D gutenberg -T word_count -L root -l /tmp/dataprocessor.log --fields-terminated-by '\t'
   2. The values I used within the Hadoop/Infobright plugin include:
      1. -v – Be verbose; be very verbose
      2. -i – The input file located on the hadoop file system
      3. -X – Execute right now.  Load directly into Infobright
      4. --dp-exe – The location of the distributed load processor binary/executable
      5. -H – The host of the database instance
      6. -P – the port number of the database instance
      7. -D – the database within the database instance
      8. -T – the table within the database
      9. -L – the username
      10. -l – The log file
      11. --fields-terminated-by – Delimiter used within the data

Once you execute the command, you should see your data in the database.  If you receive any errors, please consult the documentation for error codes.  The official Hadoop connector documentation can be downloaded on the support portal at infobright.com.