3.1.7 Not Using Server-Side Prep Stmt?
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?
Jason Winnebeck
Center for Integrated Manufacturing Studies
Rochester Institute of Technology, Rochester NY