Hi
On Sat, 2017-06-10 at 09:17 -0700, Steven Grimm wrote:
> On Sat, Jun 10, 2017 at 1:46 AM, Alban Hertroys <haramrae@gmail.com>
> wrote:
> > I notice that you're declaring your ResultSet variable inside the
> loop, which means that you create and destroy it frequently. I've
> been told that this is a pattern that the GC has trouble keeping up
> with (although that was around the Java 5 era), so you might be
> seeing the effects of memory churn in your client instead of in the
> database.
>
> Sadly, no, it doesn't help. I'd be pretty surprised if that was it,
> though, given that it consistently happens with the bind variable and
> never happens without; surely the result set's GC behavior would be
> the same in either case?
>
> For grins, I tried running with the -verbosegc option to log GC
> activity:
>
> Two bind variables 48 16 13 8 6 5 6 7 5 115 110
> 109 132 108 110 113 109 113 108 108
> Equality bind variable 5 6 6 5 6 6 6 6 7 6 8
> 8 5 6 4 5 5 5 5 5
> [GC (Allocation Failure) 65536K->2293K(251392K), 0.0035075 secs]
> LIKE bind variable 5 5 6 5 6 5 5 6 6 110 107
> 112 116 118 107 112 115 105 104 104
> No bind variables 5 5 4 5 5 4 5 5 4 6 5
> 6 5 8 4 4 4 4 5 4
>
> So there's only one GC run and it takes about 3 milliseconds. That
> result is the same whether the ResultSet is declared inside or
> outside the loop.
>
> -Steve
I ran your test and here are my results:-
:~/softdev/java_apps$ java BindTest
Two bind
variables 402 15 13 14 13 13 15 14 9 39 46 45 41 39
41 38 39 38 40 38
Equality bind
variable 9 15 9 10 12 13 10 13 10 9 9 9 7 8 7
8 8 10 8 7
LIKE bind
variable 9 9 9 8 10 8 8 8 8 40 40 41 37 38 3
9 39 39 42 38 38
No bind
variables 13 9 9 9 9 11 10 8 9 8 9 10 8 9
9 8 9 9 8 12
:~/softdev/java_apps$ java BindTest
Two bind
variables 57 17 17 15 15 14 13 14 13 38 47 42 40 39
39 41 37 39 38 37
Equality bind
variable 8 9 9 10 14 15 10 13 10 10 10 8 8 9 13
8 9 8 9 8
LIKE bind
variable 10 9 11 10 9 8 9 8 8 39 38 43 43 39 3
9 37 38 38 43 39
No bind
variables 9 9 11 9 9 9 8 9 9 8 9 10 9 15
10 9 9 9 9 13
Using Java version 1.8.0_131
postgresql-9.4.1212.jar
PostgreSQL 9.6.3 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-
18) 6.3.0 20170516, 64-bit
You'll notice that the first time it ran the database was "cold" and it
took 402, whereas the second time it dropped to 57.
If I have time today I might alter it to used named variables and see
if that makes a difference.
Cheers,
Rob
