WorkingWithMySQL

Table of Contents MySQL parameters MySQL Performance Tuning for Streaming Results and Cursors Cursors at server side Stream the ResultSets one row at a time Bibliography

MySQL parameters

For the benchmark tests we use as base the default values from the my-huge.cnf configuration that comes with MySQL server. Additionally we modified the following parameters:

max_heap_table_size = 2048M
tmp_table_size = 2048M

MySQL Performance Tuning for Streaming Results and Cursors

By default, the MySQL Protocol (also implemented in the JDBC driver) requires the ResultSets object to be completely retrieved after its calculation. This limits the way CURSORS can be implemented and used in MySQL. Using cursors is important for our benchmarks because a) we would like to measure the lowest time required to retrieve the first batch of answers from the DB, b) we don't want to measure the time required to transfer results from server to client and c) we don't want (at least in this case) to increase the complexity of the query being benchmarked with a COUNT(*) operation d) In some cases retrieving all the results is impossible due to memory limits. In MySQL, the two ways available to do this perform considerable bad in comparison with other DBMS. Here we explain both of them.

For the benchmarks of our SQL queries we used the server side cursors method. Initially, we tested the performance of the streaming results method and we found that, although the time to retrieve the first results was extremely fast (i.e., faster even than DB2), the time required to close any result set was ridiculously high. For example, a set of tests for a single combination of parameters (10000 tuples, 2500 constants, 1 iteration) that would take around 1.5 hours in Postgres or 1 hour in DB2, would require 1 full day in MySQL using streaming results. Instead, the same tests using cursors at server side would require X hours. For this tests we used max_heap_table_size=2048M, tmp_table_size=2048M to allow MySQL to put most of the results in memory and perform as good as posible.

Overall, we found that the streaming results method is not reliable in MySQL and should be avoided. We now explain the method in detail.

Cursors at server side

One way to use cursors in MySQL is at the server side level [1]. MySQL will compute the results and store them in a temporary table. First it will attempt to use a MEMORY table. If the table gets too large it will materialize in a MyISAM table.

Pro:

• No table locking.
• Flexible fetch_size

Cons:

• Requires an additional time to materialize the whole results in the server.

To enable this functionality with the Connector-J driver you need to add an extra parameter "useCursorFetch=true" in the JDBC URL string, for example:

jdbc:mysql://obdalin.inf.unibz.it:3306/TestDB?useCursorFetch=true

and you need to set the FetchSize of your statement object to a value greater than 0. For example:

final static int FETCH_AMOUNT = 10; // or other number

stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(FETCH_AMOUNT);

Stream the ResultSets one row at a time

A second option available is to use table locking and cursors. This method reassembles in a better way the usual behavior of streaming results expected form a DB engine. It is very fast to retrieve the first results. However, due to MySQL protocol limitations, it is only possible to get one tuple at a time during the streaming, which increases the network overhead and the overal time to retrieve ALL the results of the query. Additionally, the MySQL protocol requires the client to consume ALL results before releasing the locks on the tables used during query answering. This has the effect that calling the ResultSet.close() or Statement.close() or Connection.close() method will will not take a constant time, as would be expected from such an operation. Moreover, if the result set is big, the close() operations will take an extremely long time.

Pro:

• Initial results can be retrieved very fast. They start streaming after the first are computed.

Cons:

• The operation has to read (or close) all of the rows in the result set before you can issue any other queries on the connection.
• Involves locking the tables until it streams all the results.
• The fetch_size is fixed to 1

To enable this functionality, you need to create the Statement instance as follows:

stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);

Bibliography

[1] http://dev.mysql.com/doc/refman/5.5/en/cursor-restrictions.html

[2] http://dev.mysql.com/doc/refman/5.5/en/connector-j-reference-implementation-notes.html

[3] http://dev.mysql.com/doc/refman/5.5/en/connector-j-reference-configuration-properties.html