Awesome

There can be cases where we need to save large text data into a database tables. This data can take lots of space and may introduce new challenges which needs to be addressed. Backups for example may take longer and if uncompressed, these will take more space. In this article we explore several possibilities available when dealing with large data and compare the results obtained.

In this article we make use of the MySQL database (<a href="https://www.mysql.com/" target="_blank">Homepage</a>) and the SQL statements used may not work as expected with different databases.

This article is divided into three sections. It starts by introducing the database structure and some common code which are used by the following section. The second section compares several approaches and highlights their strengths and weaknesses. In the last section is compares the results obtained in the previous section and provides a conclusion.

In this section we go through the database and the tables used together with some common code. Some changes may be required depending environment where this is executed. The article flags all areas where changed may be required.

In this article we discuss how to compress large text data and saved this into a database table. The examples used in this article make use of one of the following two tables which can be created using the following SQL code.

We have two test tables one holds the plain text data while the second holds the binary data. The above SQL was executed against a MySQL database and uses the MyISAM (<a href="https://en.wikipedia.org/wiki/MyISAM" target="_blank">Wiki</a>) table structure which is considerable faster than the alternative option InnoDB (<a href="https://en.wikipedia.org/wiki/InnoDB" target="_blank">Wiki</a>). Given that we do not need transactional support, we opted for this database engine to minimise the database overheads. Some alterations may be required if a different database is used.

The data source is provided by the following utilities class, which for simplicity hard codes the database settings.

Kindly update the above database configuration as required.

In this article we will see how we can compress text data to reduce the space used and also possibly protect such data using encryption algorithms. In order to facilitate the tests three classes were added which performs the following

These three classes are used by our tests, where each test defines the way data is written into the test database table. For example when writing plain-text data we simply set the original text data to the prepared statement using the prepared statements <code>setString()</code> (<a href="https://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html#setString(int,%20java.lang.String)" target="_blank">Java Doc</a>). On the other hand, when dealing with compressed data, we cannot use the aforementioned method but instead we need to use <code>setBinaryStream()</code> (<a href="https://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html#setBinaryStream(int,%20java.io.InputStream)" target="_blank">Java Doc</a>) version.

Let us start with the write data to table class which is shown next.

The class shown above may seem long and overwhelming but it is quite straightforward. In a nutshell, the class reads a long text file and inserts the same text data several times into the same database table. This class abstracts the part where the prepare statement sets the value to be passed to the database. This will vary between test to test and is left for the implementation to deal with. The rest of the code is the same for all tests implementations. The <code>insert()</code> method is described in some details next.

The Apache IO Common <code>IOUtils</code> class (<a href="https://commons.apache.org/proper/commons-io/apidocs/org/apache/commons/io/IOUtils.html" target="_blank">Java Doc</a>) is used to ready this play as a string.

<strong>The table needs to be empty as the validation process reads all records from this table and compares them with the original text to make sure that what is written can also be correctly read</strong>.

A small note about the <code>clearTable()</code> method. This method makes use of the <code>TRUNCATE TABLE</code> SQL statement which may behave differently on different databases. For example, in MySQL the <code>TRUNCATE TABLE</code> SQL also resets the auto generated ids while in H2 Database (<a href="http://www.h2database.com/html/main.html" target="_blank">Homepage</a>) it just empties the table.

Kindly change this method to accommodate your database requirements.

Smaller samples may be easily affected by some anomalies caused by the underlying systems, such as the OS or DB, and it is always recommended to run with a decent sample size. The decent sample varies from one problem to another and in this case we used a size of thousands records. We do so as this sample size is large enough to absorb any discrepancies caused by the underlying systems .

The above code makes use of an abstract method, which method is implemented at a later stage by each test class.

This method will update the prepared statement with the correct data type, such as uncompressed text or compressed text (as a stream). As hinted above, the write to table process is similar for all tests with the exception of one this, that is, what is written to the database. This is defined by this method and each test will implement its approach.

As described above, this class provides all common code required to write data into a test table. The next class reads the data back from the test table and makes sure that the correct data is found in this table. There is no point if the example corrupts the data and write unreadable data.

The next class reads all data saved in test table and compare the value read with the original text. Like before, the <code>ReadDataFromTable</code> is abstract and leaves the actual reading to be implemented by each individual test. This allows for each test to determine how the data is read from the table.

The <code>verify()</code> method is described in detail next.

This will be used to compare each row read and makes sure that the read value is the same as the original text.

The actual retrieval of the information is delegated to the subclass.

Each test can then implement the appropriate method to retrieve the data from the <code>ResultSet</code> (<a href="http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html" target="_blank">Java Doc</a>) and convert it back to string.

For convenience, these two class are grouped into one class which is shown next.

This class is quite simple. It has two abstract methods, one responsible from writing the data and the other from reading the data. These have the same method signature as those found in <code>WriteDataToTable</code> and <code>ReadDataFromTable</code>.

The tests shown in the following sections make use this class and override these two methods with the appropriate implementation.

In this section we carry out fours tests and compare their results.

The first test writes the data as it comes. It does not alter the data and simply write and reads the data as text.

Inserting thousand records of the long text play, Shakespeare's Hamlet, into the table without modifying it take almost 8 seconds. Reading such data is quite fast as it takes less than half of a second.

In the second example, we compress the data using the lossless compression Java implementation of GZIP (<a href="https://docs.oracle.com/javase/7/docs/api/java/util/zip/package-summary.html" target="_blank">Package Summary</a>) as shown next.

The data is compressed before written to the database as shown next.

The data is compressed every time to simulate a production environment, where the data is compressed before writing it. One understands that compression will increase in the time required as additional processing is required. On the other hand, such approach writes less data to the disk which should improve the write time and compensate for the time lost during compression. Unfortunately this approach is quite slow and the compression takes far longer than what is saved from writing less data to the disk. Inserting thousand records of the long text play, Shakespeare's Hamlet, into the table after compressing each instance takes more than 19 seconds. Reading such data is slower too as it need more that 2 seconds. This approach reduced the space required by more than half, but it takes more to process. In summary, this approach trades time with space. You need less space on disk but requires further processing time.

LZ4 (<a href="http://cyan4973.github.io/lz4/" target="_blank">Homepage</a>) is an other lossless compression algorithm, similar to GZIP, which is considerably faster than GZIP as we will see in this test.

Instead of <code>GZIPOutputStream</code> (<a href="http://docs.oracle.com/javase/7/docs/api/java/util/zip/GZIPOutputStream.html" target="_blank">Java Doc</a>), in this example we use <code>LZ4BlockOutputStream</code> (<a href="https://github.com/jpountz/lz4-java/blob/master/src/java/net/jpountz/lz4/LZ4BlockOutputStream.java" target="_blank">Source Code</a>) as shown next.

LZ4 is faster compared to GZIP and faster to write data even when compared to the plain text approach. Compressing and inserting thousand records takes about 5 seconds. Reading data is faster than GZIP as it needs about 800 milliseconds, but slightly slower when compared to the plain text method. Therefore, this method is faster when it comes writing but a bit slower when it comes to reading. It is important to note that this algorithm performs worse when it comes to size required.

LZ4 can be tuned to compress the data further at a cost of performance, but in this example we used the default settings. Furthermore, we can reduce the compression ration and obtain faster performance too.

As a final test, we encrypt the data using AES (<a href="https://en.wikipedia.org/wiki/Advanced_Encryption_Standard" target="_blank">Wiki</a>) after this is compressed with the LZ4 compression algorithm with the default settings as above. As one would expect encryption will add an overhead and this this example will obtain worse results when compared with the previous test.

As expected this approach is somewhat slower than the previous one. To compress and then encrypt thousand records take almost 10 seconds which is almost twice as much as the version that does not use encryption. Reading and decryption is considerably slower too when compared to the previous approach. The overhead added by the decryption process makes this the worse approach from all tests. This approach requires the same size as the one before as the encryption does not inflate or deflate the message.

In the previous section we described fours tests, where each test inserts a thousand records and then reads and verify these records. The following stacked graph compares the time required to write and then read these records.

<a href="http://www.javacreed.com/wp-content/uploads/2015/11/Write-and-Reed-Performance.png" class="preload" rel="prettyphoto" title="Write and Reed Performance" ><img src="http://www.javacreed.com/wp-content/uploads/2015/11/Write-and-Reed-Performance.png" alt="Write and Reed Performance" width="595" height="370" class="size-full wp-image-5363" /></a>

In all cases, the write was slower than the read. The next bar graph compares the space required on the disk.

<a href="http://www.javacreed.com/wp-content/uploads/2015/11/Tables-Sizes.png" class="preload" rel="prettyphoto" title="Tables Sizes" ><img src="http://www.javacreed.com/wp-content/uploads/2015/11/Tables-Sizes.png" alt="Tables Sizes" width="598" height="368" class="size-full wp-image-5364" /></a>

As expected, the plain text test requires most space while GZIP requires the least space.

Compressing data makes the data smaller and reduces the required space, but this come at a cost of performance. While database backups may run faster, processing such data will be hindered. It is imperative to measure the changes before applying these to a production environment.