You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@jmeter.apache.org by Felix Schumacher <fe...@internetallee.de> on 2016/09/06 18:08:17 UTC
JDBC PreparedStatement Cache
Hi all,
we have a pool for PreparedStatements in AbstractJDBCTestElement, which
has a problem with the wrapped Connections, that are returned by our
jdbc pool. We could fix it, by unwrapping the pool-connection and using
that as key for the cache-map "perConnCache". But this unwrapping would
be special to the used pool implementation and we wanted to be
independent :(
Another thing is, that since jdbc 3.0 there seems to be support for
pooling/cacheing of PreparedStatements in the jdbc driver itself. It
seems, that most major vendors have implemented that cache (see
https://github.com/brettwooldridge/HikariCP/issues/488).
Given these two bits of information I would opt to remove the
perConnCache in order to solve bug 60085.
What do you think?
Felix
Re: JDBC PreparedStatement Cache
Posted by sebb <se...@gmail.com>.
On 8 September 2016 at 06:38, Felix Schumacher
<fe...@internetallee.de> wrote:
>
>
> Am 8. September 2016 01:17:52 MESZ, schrieb sebb <se...@gmail.com>:
>>It seems to me that it ought to be possible to re-use a
>>PreparedStatement somehow in JMeter.
>>Surely re-use is one of the functions of a PreparedStatement?
>>
>>However I've always thought that pooling across threads (or indeed
>>caching across threads) does not make sense given that threads are
>>supposed to represent independent users.
>>That sort of re-use just reduces the load on the database server, when
>>the main point is surely to test the server.
>
> I am not sure I can follow you. I read it as: first, you are in favour to remove or cache as it made connections between the threads. Second, you want a new way to keep a connection (and prepared statements) for the whole test run for each thread.
>
> Right?
Yes. A cache that shares statements (or connections) between threads
does not make sense to me.
However it should be possible to re-use a prepared statement.
> My concern was about the statements that are not reachable by the client and are not closed, which again will lead to a memory leak. So I think we should close the statements in the current implementation.
Agreed.
I think prepared statements should operate more like HTTP connections.
An HTTP connection is reused unless JMeter (or the server) explicitly
closes the connection.
[However HTTP connections are not shared between threads.]
This is not an exact analogy, but is done for the same reason: threads
need to be independent.
Re: JDBC PreparedStatement Cache
Posted by Felix Schumacher <fe...@internetallee.de>.
Am 8. September 2016 01:17:52 MESZ, schrieb sebb <se...@gmail.com>:
>It seems to me that it ought to be possible to re-use a
>PreparedStatement somehow in JMeter.
>Surely re-use is one of the functions of a PreparedStatement?
>
>However I've always thought that pooling across threads (or indeed
>caching across threads) does not make sense given that threads are
>supposed to represent independent users.
>That sort of re-use just reduces the load on the database server, when
>the main point is surely to test the server.
I am not sure I can follow you. I read it as: first, you are in favour to remove or cache as it made connections between the threads. Second, you want a new way to keep a connection (and prepared statements) for the whole test run for each thread.
Right?
My concern was about the statements that are not reachable by the client and are not closed, which again will lead to a memory leak. So I think we should close the statements in the current implementation.
Regards,
Felix
>
>
>On 7 September 2016 at 21:00, Felix Schumacher
><fe...@internetallee.de> wrote:
>> Am 06.09.2016 um 21:24 schrieb Vladimir Sitnikov:
>>>
>>> TL;DR: +1 for removing caching at JMeter side.
>>
>> Thanks for the detailed test. I believe we are missing the close
>statements
>> for the prepared statements. Will have a look at that tomorrow.
>>
>> Regards,
>> Felix
>>
>>>
>>> I believe most applications never reuse prepared statements (I mean
>they
>>> never reuse PreparedStatement java objects).
>>> They just follow ps=con.prepareStatement(..);...ps.close(); pattern.
>>>
>>> So, if JMeter used "always go through con.prepareStatement" mode,
>then it
>>> would accurately model the performance for those applications.
>>>
>>>
>>> Just in case, I've did a single-threaded JMH benchmark (see [1])
>against
>>> localhost on OSX 10.11 for PostgreSQL jdbc driver (pgjdbc) to check
>what
>>> is
>>> the impact for "reused vs non-reused" statement execution.
>>> The statement is "select 1" that produces 1 row and 1 column.
>Resultset is
>>> processed as "while(re.next()) rs.getInt(1)".
>>>
>>> Here are the results:
>>> Benchmark
>(reuseStatement)
>>> Mode Cnt Score Error Units
>>> ProcessResultSet.bindExecuteFetch
>true
>>> avgt 10 38,899 � 0,508 us/op
>>> ProcessResultSet.bindExecuteFetch:�gc.alloc.rate.norm
>true
>>> avgt 10 464,069 � 0,228 B/op
>>> ProcessResultSet.bindExecuteFetch
>false
>>> avgt 10 39,724 � 0,454 us/op
>>> ProcessResultSet.bindExecuteFetch:�gc.alloc.rate.norm
>false
>>> avgt 10 752,070 � 0,232 B/op
>>>
>>> The response time difference is 38.9us vs 39.7us, and the number of
>>> allocated bytes is 464 vs 752. Well, I think JMeter is not supposed
>to
>>> measure that level of details, so it is perfectly fine to ignore
>that at
>>> JMeter side.
>>>
>>> PS. If you think 464 vs 752 is significant, then remember that the
>case is
>>> sending just 4 bytes (1 int). For bigger queries the resultset size
>would
>>> greatly exceed that (752-464) difference.
>>>
>>> PPS. Bonus point for those who read this: I wonder what do you thing
>of
>>> adding "number of bytes allocated" as one more measurement done by
>JMeter
>>> (in addition to "response time")?
>>> It would make great sense, especially for Java samplers. OpenJDK
>1.6u26+
>>> allows easy way to capture "number of bytes allocated" from a
>>> ThreadMXBean.
>>>
>>> [1]:
>>>
>>>
>https://github.com/pgjdbc/pgjdbc/blob/master/ubenchmark/src/main/java/org/postgresql/benchmark/statement/ProcessResultSet.java
>>>
>>>
>>> Vladimir
>>>
>>
Re: JDBC PreparedStatement Cache
Posted by sebb <se...@gmail.com>.
It seems to me that it ought to be possible to re-use a
PreparedStatement somehow in JMeter.
Surely re-use is one of the functions of a PreparedStatement?
However I've always thought that pooling across threads (or indeed
caching across threads) does not make sense given that threads are
supposed to represent independent users.
That sort of re-use just reduces the load on the database server, when
the main point is surely to test the server.
On 7 September 2016 at 21:00, Felix Schumacher
<fe...@internetallee.de> wrote:
> Am 06.09.2016 um 21:24 schrieb Vladimir Sitnikov:
>>
>> TL;DR: +1 for removing caching at JMeter side.
>
> Thanks for the detailed test. I believe we are missing the close statements
> for the prepared statements. Will have a look at that tomorrow.
>
> Regards,
> Felix
>
>>
>> I believe most applications never reuse prepared statements (I mean they
>> never reuse PreparedStatement java objects).
>> They just follow ps=con.prepareStatement(..);...ps.close(); pattern.
>>
>> So, if JMeter used "always go through con.prepareStatement" mode, then it
>> would accurately model the performance for those applications.
>>
>>
>> Just in case, I've did a single-threaded JMH benchmark (see [1]) against
>> localhost on OSX 10.11 for PostgreSQL jdbc driver (pgjdbc) to check what
>> is
>> the impact for "reused vs non-reused" statement execution.
>> The statement is "select 1" that produces 1 row and 1 column. Resultset is
>> processed as "while(re.next()) rs.getInt(1)".
>>
>> Here are the results:
>> Benchmark (reuseStatement)
>> Mode Cnt Score Error Units
>> ProcessResultSet.bindExecuteFetch true
>> avgt 10 38,899 ± 0,508 us/op
>> ProcessResultSet.bindExecuteFetch:·gc.alloc.rate.norm true
>> avgt 10 464,069 ± 0,228 B/op
>> ProcessResultSet.bindExecuteFetch false
>> avgt 10 39,724 ± 0,454 us/op
>> ProcessResultSet.bindExecuteFetch:·gc.alloc.rate.norm false
>> avgt 10 752,070 ± 0,232 B/op
>>
>> The response time difference is 38.9us vs 39.7us, and the number of
>> allocated bytes is 464 vs 752. Well, I think JMeter is not supposed to
>> measure that level of details, so it is perfectly fine to ignore that at
>> JMeter side.
>>
>> PS. If you think 464 vs 752 is significant, then remember that the case is
>> sending just 4 bytes (1 int). For bigger queries the resultset size would
>> greatly exceed that (752-464) difference.
>>
>> PPS. Bonus point for those who read this: I wonder what do you thing of
>> adding "number of bytes allocated" as one more measurement done by JMeter
>> (in addition to "response time")?
>> It would make great sense, especially for Java samplers. OpenJDK 1.6u26+
>> allows easy way to capture "number of bytes allocated" from a
>> ThreadMXBean.
>>
>> [1]:
>>
>> https://github.com/pgjdbc/pgjdbc/blob/master/ubenchmark/src/main/java/org/postgresql/benchmark/statement/ProcessResultSet.java
>>
>>
>> Vladimir
>>
>
Re: JDBC PreparedStatement Cache
Posted by Felix Schumacher <fe...@internetallee.de>.
Am 06.09.2016 um 21:24 schrieb Vladimir Sitnikov:
> TL;DR: +1 for removing caching at JMeter side.
Thanks for the detailed test. I believe we are missing the close
statements for the prepared statements. Will have a look at that tomorrow.
Regards,
Felix
>
> I believe most applications never reuse prepared statements (I mean they
> never reuse PreparedStatement java objects).
> They just follow ps=con.prepareStatement(..);...ps.close(); pattern.
>
> So, if JMeter used "always go through con.prepareStatement" mode, then it
> would accurately model the performance for those applications.
>
>
> Just in case, I've did a single-threaded JMH benchmark (see [1]) against
> localhost on OSX 10.11 for PostgreSQL jdbc driver (pgjdbc) to check what is
> the impact for "reused vs non-reused" statement execution.
> The statement is "select 1" that produces 1 row and 1 column. Resultset is
> processed as "while(re.next()) rs.getInt(1)".
>
> Here are the results:
> Benchmark (reuseStatement)
> Mode Cnt Score Error Units
> ProcessResultSet.bindExecuteFetch true
> avgt 10 38,899 � 0,508 us/op
> ProcessResultSet.bindExecuteFetch:�gc.alloc.rate.norm true
> avgt 10 464,069 � 0,228 B/op
> ProcessResultSet.bindExecuteFetch false
> avgt 10 39,724 � 0,454 us/op
> ProcessResultSet.bindExecuteFetch:�gc.alloc.rate.norm false
> avgt 10 752,070 � 0,232 B/op
>
> The response time difference is 38.9us vs 39.7us, and the number of
> allocated bytes is 464 vs 752. Well, I think JMeter is not supposed to
> measure that level of details, so it is perfectly fine to ignore that at
> JMeter side.
>
> PS. If you think 464 vs 752 is significant, then remember that the case is
> sending just 4 bytes (1 int). For bigger queries the resultset size would
> greatly exceed that (752-464) difference.
>
> PPS. Bonus point for those who read this: I wonder what do you thing of
> adding "number of bytes allocated" as one more measurement done by JMeter
> (in addition to "response time")?
> It would make great sense, especially for Java samplers. OpenJDK 1.6u26+
> allows easy way to capture "number of bytes allocated" from a ThreadMXBean.
>
> [1]:
> https://github.com/pgjdbc/pgjdbc/blob/master/ubenchmark/src/main/java/org/postgresql/benchmark/statement/ProcessResultSet.java
>
>
> Vladimir
>
Re: JDBC PreparedStatement Cache
Posted by Vladimir Sitnikov <si...@gmail.com>.
TL;DR: +1 for removing caching at JMeter side.
I believe most applications never reuse prepared statements (I mean they
never reuse PreparedStatement java objects).
They just follow ps=con.prepareStatement(..);...ps.close(); pattern.
So, if JMeter used "always go through con.prepareStatement" mode, then it
would accurately model the performance for those applications.
Just in case, I've did a single-threaded JMH benchmark (see [1]) against
localhost on OSX 10.11 for PostgreSQL jdbc driver (pgjdbc) to check what is
the impact for "reused vs non-reused" statement execution.
The statement is "select 1" that produces 1 row and 1 column. Resultset is
processed as "while(re.next()) rs.getInt(1)".
Here are the results:
Benchmark (reuseStatement)
Mode Cnt Score Error Units
ProcessResultSet.bindExecuteFetch true
avgt 10 38,899 ± 0,508 us/op
ProcessResultSet.bindExecuteFetch:·gc.alloc.rate.norm true
avgt 10 464,069 ± 0,228 B/op
ProcessResultSet.bindExecuteFetch false
avgt 10 39,724 ± 0,454 us/op
ProcessResultSet.bindExecuteFetch:·gc.alloc.rate.norm false
avgt 10 752,070 ± 0,232 B/op
The response time difference is 38.9us vs 39.7us, and the number of
allocated bytes is 464 vs 752. Well, I think JMeter is not supposed to
measure that level of details, so it is perfectly fine to ignore that at
JMeter side.
PS. If you think 464 vs 752 is significant, then remember that the case is
sending just 4 bytes (1 int). For bigger queries the resultset size would
greatly exceed that (752-464) difference.
PPS. Bonus point for those who read this: I wonder what do you thing of
adding "number of bytes allocated" as one more measurement done by JMeter
(in addition to "response time")?
It would make great sense, especially for Java samplers. OpenJDK 1.6u26+
allows easy way to capture "number of bytes allocated" from a ThreadMXBean.
[1]:
https://github.com/pgjdbc/pgjdbc/blob/master/ubenchmark/src/main/java/org/postgresql/benchmark/statement/ProcessResultSet.java
Vladimir
Re: JDBC PreparedStatement Cache
Posted by Felix Schumacher <fe...@internetallee.de>.
Am 06.09.2016 um 20:22 schrieb Philippe Mouawad:
> Hi Felix,
> My answer below.
> Regards
>
> On Tuesday, September 6, 2016, Felix Schumacher <
> felix.schumacher@internetallee.de> wrote:
>
>> Hi all,
>>
>> we have a pool for PreparedStatements in AbstractJDBCTestElement, which
>> has a problem with the wrapped Connections, that are returned by our jdbc
>> pool. We could fix it, by unwrapping the pool-connection and using that as
>> key for the cache-map "perConnCache". But this unwrapping would be special
>> to the used pool implementation and we wanted to be independent :(
>>
>> Another thing is, that since jdbc 3.0 there seems to be support for
>> pooling/cacheing of PreparedStatements in the jdbc driver itself. It seems,
>> that most major vendors have implemented that cache (see
>> https://github.com/brettwooldridge/HikariCP/issues/488).
>>
>> Given these two bits of information I would opt to remove the perConnCache
>> in order to solve bug 60085.
>>
>> What do you think?
> +1 for me.
> Do you know if sqlserver has now a prepared statement pool in it driver ?
Looking at
https://social.msdn.microsoft.com/Forums/en-US/3922b86a-867f-4c48-84d7-611258a3d3be/jdbc-driver-support-statement-caching?forum=sqldataaccess
it doesn't need to support it, since the server will support it out of
the box.
Felix
>
>> Felix
>>
>>
Re: JDBC PreparedStatement Cache
Posted by Philippe Mouawad <ph...@gmail.com>.
Hi Felix,
My answer below.
Regards
On Tuesday, September 6, 2016, Felix Schumacher <
felix.schumacher@internetallee.de> wrote:
> Hi all,
>
> we have a pool for PreparedStatements in AbstractJDBCTestElement, which
> has a problem with the wrapped Connections, that are returned by our jdbc
> pool. We could fix it, by unwrapping the pool-connection and using that as
> key for the cache-map "perConnCache". But this unwrapping would be special
> to the used pool implementation and we wanted to be independent :(
>
> Another thing is, that since jdbc 3.0 there seems to be support for
> pooling/cacheing of PreparedStatements in the jdbc driver itself. It seems,
> that most major vendors have implemented that cache (see
> https://github.com/brettwooldridge/HikariCP/issues/488).
>
> Given these two bits of information I would opt to remove the perConnCache
> in order to solve bug 60085.
>
> What do you think?
+1 for me.
Do you know if sqlserver has now a prepared statement pool in it driver ?
>
> Felix
>
>
--
Cordialement.
Philippe Mouawad.