Bug 55518

Summary:	Add ability to limit number of cached PreparedStatements per connection when "Prepared Select Statement", "Prepared Update Statement" or "Callable Statement" query type is selected
Product:	JMeter - Now in Github	Reporter:	sander hautvast <shautvast>
Component:	Main	Assignee:	JMeter issues mailing list <issues>
Status:	RESOLVED FIXED
Severity:	enhancement	CC:	p.mouawad
Priority:	P2
Version:	2.9
Target Milestone:	---
Hardware:	All
OS:	All

Description sander hautvast 2013-09-03 13:14:45 UTC

Please add a warning in the reference docs for the JDBC Sampler that not using bind variables in the query keeps the cursors in the database open (until end of test) 
Executing a query (without bind, ie question marks) too often in a test can cause an error on the database, because of the way JDBC Prepared/Callable-Statement objects are being cached in Jmeter.
There is no problem in JMeter (or in the jdbc lib) and it turned out te be in our script. But it took us a day to debug...

Comment 1 Sebb 2013-09-04 16:46:30 UTC

I'm not sure I follow why using bind should make a difference.
If it does matter, it seems to me that this may be a bug in JMeter.

Would you be able to provide sample a simple test plan that shows the difference?

Comment 2 Philippe Mouawad 2013-09-05 20:34:16 UTC

@sebb I don't think it's a bug of JMeter, it's more a feature and due to the PreparedStatement cache per connection. See perConnCache in AbstractJDBCTestElement.

If users don't use ? , then you will have one query per different value. As statements are closed only at end of test, this lead @sander test to saturate cursors.

So we could add this warning, but should we change this behaviour ?

Comment 3 Sebb 2013-09-05 22:36:44 UTC

Thanks for the explanation. Sorry I did not pick that up earlier.

It's certainly worth warning users that Prepared and Callable statements are cached, and so they should be used sparingly.

It does not make sense to drop the cache entirely, so the best we can do is either limit the number of entries somehow (drop the oldest one?) or add some way for the user to drop specific statements or perhaps limit how many times they are used.

All of this is a bit complicated, and this is the first report we have had, so I think clearer documentation may be enough unless we get more reports - and a use case as to how the strategy could be improved.

Comment 4 Philippe Mouawad 2013-09-08 21:02:02 UTC

Date: Sun Sep  8 21:00:28 2013
New Revision: 1520926

URL: http://svn.apache.org/r1520926
Log:
Bug 55518 - Add ability to limit number of cached PreparedStatements per connection when "Prepared Select Statement", "Prepared Update Statement" or "Callable Statement" query type is selected
Bugzilla Id: 55518

Modified:
    jmeter/trunk/bin/jmeter.properties
    jmeter/trunk/src/protocol/jdbc/org/apache/jmeter/protocol/jdbc/AbstractJDBCTestElement.java
    jmeter/trunk/xdocs/changes.xml


Date: Sun Sep  8 21:01:26 2013
New Revision: 1520927

URL: http://svn.apache.org/r1520927
Log:
Bug 55518 - Add ability to limit number of cached PreparedStatements per connection when "Prepared Select Statement", "Prepared Update Statement" or "Callable Statement" query type is selected
Documentation
Bugzilla Id: 55518

Modified:
    jmeter/trunk/xdocs/usermanual/component_reference.xml

Comment 5 Philippe Mouawad 2013-09-08 21:04:43 UTC

Hello Sander,
We fixed the issue by adding a LRU Map with a default size of 100 and a property to configure it jdbcsampler.maxopenpreparedstatements.
Could you give jenkins nightly build a try and give us feedback ?
Thanks

Comment 6 The ASF infrastructure team 2022-09-24 20:37:55 UTC

This issue has been migrated to GitHub: https://github.com/apache/jmeter/issues/3225