MySQL Bugs: #6419: missing options/tuning details for server side prepared statements

Bug #6419	missing options/tuning details for server side prepared statements
Submitted:	3 Nov 2004 23:50	Modified:	5 Apr 2006 19:45
Reporter:	Quartz 12h	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S4 (Feature request)
Version:	4.1.7	OS:	Linux (linux)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
We lack options and tuning details for server side prepared statements.

We are not getting better than 2x throughput increase with prepared statements.

We tuned most of the obvious on client app.

DB server obviously has memory buffers for prepared statements, but they are not documented.

How to repeat:
Do a lot of parallel server-side prepared statements with connector/J 3.1.4b (at least, and I recommend 3.1.4b nightly build too)
and compare with same insert/update for simple statements.

We got about 660 prepared statements opened uniformly across 60 connections (for 60 threads), 11 distinct types of prepared statement per connection. Those 11 ps types have a variable 2 to 40 value bindings (including some text and varchar fields) and works on a total of 6 uniques InnoDB tables.
(In case you ask: no, we don't close those statements! They are reused.)

Suggested fix:
Give tuning tips for mysql server 4.1.x (we use 4.1.7 ga).

Give benchmarks to assert the performance increase, and the order of that increase.

I will attach test code.

cfg file for test code

Attachment: test_3.1.4b.properties (application/octet-stream, text), 741 bytes.

test code: note that the 'workItBatch()' is disabled and not multithreaded yet.

Attachment: TestSQLStatementsMultiThreaded.java (text/plain), 18.47 KiB.

I actually requested server documentation, not connector/j documentation.
There must be settings on the server side for prepared statements. We want to know about tuning them.

We already know how to use prepared statements on the connector/j driver.
Thanks for answering to 'quickly'...
;-)

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

Given the absence of tuning parameters, I've merely
added a paragraph to the manual stating that you should
test whether prepared statements indeed yield a performance
boost.