Re: 3.1.7 Not Using Server-Side Prep Stmt?
Posted by: Mark Matthews
Date: February 24, 2005 04:03PM

Jason Winnebeck wrote:
> My organization has still been working on the
> MySQL system's performance in our embedded
> environment (Linux P166, 128MB of RAM). We were
> hoping to see some performance benefit to the new
> prepared statements, not only for pre-parsing
> reasons but also because of the binary encoding.
> However, when we moved from MySQL 4.0 and
> Connector/J 3.0.11 to MySQL 4.1.8 and CJ 3.1.6 we
> saw no difference at all, regardless of all of the
> driver param tweaking we tried (except
> useCompression), but from what I understand, 3.1.6
> is supposed to use the new binary encoding. We
> expected to see *some* sort of difference in speed
> or bytes transferred.
> Now here is where things get interesting: enter
> version 3.1.7, which added the new parameter
> "emulateUnsupportedPstmts", which defaults to true
> in 3.1.7 and effectively is false in 3.1.6 and
> earlier where it does not exist, if I understand
> correctly.
> But in 3.1.7 when we turn *off*
> emulateUnsupportedPstmts, the program gets 30%
> faster and less data is transferred. We are only
> using SELECT statements for preparation, so they
> are all supported and the "emulation" shouldn't be
> kicking in. We were very happy to see the
> performance gain we were hoping for, but I'm
> confused. I mention this because maybe we have
> found a performance bug in the new 3.1 driver
> series. We get fast performance only when the
> parameter is set to false in the JDBC URL.
> I include below a "table" of results from three
> test cases. I include the one with useCompression
> so you can see the bottleneck is CPU and not disk
> I/O or network access.
> (this forum *really* needs a preformatted markup)
> Case, Driver, data in, data out, MySQL Server
> size, MySQL CPU Use, time
> compression, 2-11 nightly, 2.5M, 274K, 4.38M, 97%,
> 74s
> old driver, 2-11 nightly, 8.6M, 290K, 3.908M, 94%,
> 46s (same results with 3.1.6)
> emu off, 3.1.7, 5.3M, 290K, 4.144M, 92%, 35s
> If the change in performance is due to server-side
> prepared statements, then this seems to suggest
> that server side prepared statements work only
> with emulateUnsupportedPstmts is false, but we are
> only using SELECT as I said earlier, which is
> quite obviously supported. Have I come across any
> bug or is this a big misunderstanding on my
> part?


We'd have to see your SELECTS ... Not all are supported (for example those with LIMIT clauses in them).

Also, if emulateUnsupportedPstmts=true, the driver needs to 'scan' every query which you send to Connection.prepareStatement(), which if you don't follow the 'normal' usage pattern of prepare once, execute many times, will cause some overhead.

Here's an example from the testsuite that comes with the driver that should shed some light on what's going on:

* Server doesn't accept everything as a server-side prepared statement,
* so by default we scan for stuff it can't handle.
* @throws SQLException
public void testBug4718() throws SQLException {
if (versionMeetsMinimum(4, 1, 0)) {
this.pstmt = this.conn.prepareStatement("SELECT 1 LIMIT ?");
assertTrue(this.pstmt instanceof com.mysql.jdbc.PreparedStatement);

try {
this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4718");
this.stmt.executeUpdate("CREATE TABLE testBug4718 (field1 INT)");

this.pstmt = this.conn.prepareStatement("ALTER TABLE testBug4718 ADD INDEX (field1)");
assertTrue(this.pstmt instanceof com.mysql.jdbc.PreparedStatement);

this.pstmt = this.conn.prepareStatement("SELECT 1");
assertTrue(this.pstmt instanceof ServerPreparedStatement);
} finally {
this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4718");


Mark Matthews
Consulting Member Technical Staff - MySQL Enterprise Tools

Options: ReplyQuote

Written By
Re: 3.1.7 Not Using Server-Side Prep Stmt?
February 24, 2005 04:03PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.