Yes, I've seen that bit, but wonder how that impacts the code which is
using/generating PreparedStatement calls?
Btw, of the 100 runs of the client only went through all 5 attempts and
ultimately failed, though many clients successfully resubmitted their
payload. You can see below (if you strectch your screen really wide)
the first client (later runtime) failed once then succeeded. (The
emergency landing worked, dumping a the json version of the data)
[u0138544@syslog camp]$ zcat yesterday/lonepeak9/messages.gz | grep
14a99861-fb7c-4c45-bed0-c12d0bd4ae15
2017-08-29T21:44:50-06:00 lonepeak9 [pool-1-thread-5]
edu.utah.camplab.server.PayloadThread Saving Private Ryan:
14a99861-fb7c-4c45-bed0-c12d0bd4ae15
2017-08-29T21:44:50-06:00 lonepeak9 [pool-1-thread-5]
edu.utah.camplab.jx.AbstractPayload sending
14a99861-fb7c-4c45-bed0-c12d0bd4ae15
2017-08-29T21:44:50-06:00 lonepeak9 [pool-1-thread-5]
edu.utah.camplab.jx.AbstractPayload
14a99861-fb7c-4c45-bed0-c12d0bd4ae15: start transaction
2017-08-29T21:44:50-06:00 lonepeak9 [pool-1-thread-5]
edu.utah.camplab.jx.AbstractPayload
14a99861-fb7c-4c45-bed0-c12d0bd4ae15: failed write attempt #1, SQL
[insert into "process_input" ("id", "process_id", "input_type",
"input_ref") values (?, ?, ?, ?)]; Batch entry 0 insert into
"process_input" ("id", "process_id", "input_type", "input_ref")
values ('e8a8f8cb-4b84-43b6-8c0d-8081a672deca'::uuid,
'c2c13b27-bc8a-4ece-aab2-036b93180b2e'::uuid, 'pedfile',
'a3a1326a-cd40-4777-903f-546621028e9f') was aborted: ERROR: prepared
statement "S_1" already exists Call getNextException to see other
errors in the batch.
2017-08-29T21:44:50-06:00 lonepeak9 [pool-1-thread-5]
edu.utah.camplab.jx.AbstractPayload
14a99861-fb7c-4c45-bed0-c12d0bd4ae15: start transaction
2017-08-29T21:44:50-06:00 lonepeak9 [pool-1-thread-5]
edu.utah.camplab.jx.AbstractPayload
14a99861-fb7c-4c45-bed0-c12d0bd4ae15: end transaction
[u0138544@syslog camp]$ zcat yesterday/lonepeak9/messages.gz | grep
d3d3a62f-64cf-4ffe-b7f8-927943af5c3e
2017-08-29T17:33:25-06:00 lonepeak9 [pool-1-thread-6]
edu.utah.camplab.server.PayloadThread Saving Private Ryan:
d3d3a62f-64cf-4ffe-b7f8-927943af5c3e
2017-08-29T17:33:25-06:00 lonepeak9 [pool-1-thread-6]
edu.utah.camplab.jx.AbstractPayload sending
d3d3a62f-64cf-4ffe-b7f8-927943af5c3e
2017-08-29T17:33:25-06:00 lonepeak9 [pool-1-thread-6]
edu.utah.camplab.jx.AbstractPayload
d3d3a62f-64cf-4ffe-b7f8-927943af5c3e: failed write attempt #1, SQL
[insert into "process_input" ("id", "process_id", "input_type",
"input_ref") values (?, ?, ?, ?)]; Batch entry 0 insert into
"process_input" ("id", "process_id", "input_type", "input_ref")
values ('05f9c2b7-b2a1-441b-aabe-686ed48e84c2'::uuid,
'40cd010d-804d-4963-ad06-eca57b795f74'::uuid, 'pedfile',
'a3a1326a-cd40-4777-903f-546621028e9f') was aborted: ERROR: prepared
statement "S_1" already exists Call getNextException to see other
errors in the batch.
2017-08-29T17:33:25-06:00 lonepeak9 [pool-1-thread-6]
edu.utah.camplab.jx.AbstractPayload
d3d3a62f-64cf-4ffe-b7f8-927943af5c3e: failed write attempt #2, SQL
[insert into "process_input" ("id", "process_id", "input_type",
"input_ref") values (?, ?, ?, ?)]; Batch entry 0 insert into
"process_input" ("id", "process_id", "input_type", "input_ref")
values ('d29a39fd-e952-4c6b-a016-b6ca72f0a546'::uuid,
'13083f28-a6c6-44a0-be14-e24f8add05f9'::uuid, 'pedfile',
'a3a1326a-cd40-4777-903f-546621028e9f') was aborted: ERROR: prepared
statement "S_1" already exists Call getNextException to see other
errors in the batch.
2017-08-29T17:33:25-06:00 lonepeak9 [pool-1-thread-6]
edu.utah.camplab.jx.AbstractPayload
d3d3a62f-64cf-4ffe-b7f8-927943af5c3e: failed write attempt #3, SQL
[insert into "process_input" ("id", "process_id", "input_type",
"input_ref") values (?, ?, ?, ?)]; Batch entry 0 insert into
"process_input" ("id", "process_id", "input_type", "input_ref")
values ('25831dcc-2cfe-4a54-a6e6-b5a4efec08da'::uuid,
'f150d753-822e-4aa2-8745-fc1ce6e99041'::uuid, 'pedfile',
'a3a1326a-cd40-4777-903f-546621028e9f') was aborted: ERROR: prepared
statement "S_1" already exists Call getNextException to see other
errors in the batch.
2017-08-29T17:33:25-06:00 lonepeak9 [pool-1-thread-6]
edu.utah.camplab.jx.AbstractPayload
d3d3a62f-64cf-4ffe-b7f8-927943af5c3e: failed write attempt #4, SQL
[insert into "process_input" ("id", "process_id", "input_type",
"input_ref") values (?, ?, ?, ?)]; Batch entry 0 insert into
"process_input" ("id", "process_id", "input_type", "input_ref")
values ('1ec5e560-35aa-4ea9-9c4b-22293a67b8bf'::uuid,
'2d05a7a9-625e-4dfe-9e72-ec4671cdd67c'::uuid, 'pedfile',
'a3a1326a-cd40-4777-903f-546621028e9f') was aborted: ERROR: prepared
statement "S_1" already exists Call getNextException to see other
errors in the batch.
2017-08-29T17:33:25-06:00 lonepeak9 [pool-1-thread-6]
edu.utah.camplab.jx.AbstractPayload
d3d3a62f-64cf-4ffe-b7f8-927943af5c3e: failed write attempt #5, SQL
[insert into "process_input" ("id", "process_id", "input_type",
"input_ref") values (?, ?, ?, ?)]; Batch entry 0 insert into
"process_input" ("id", "process_id", "input_type", "input_ref")
values ('11c0e3d1-59a5-469e-a77d-89dcc98f03e0'::uuid,
'14271c8d-31ea-4cd7-bcba-62b2436a9c21'::uuid, 'pedfile',
'a3a1326a-cd40-4777-903f-546621028e9f') was aborted: ERROR: prepared
statement "S_1" already exists Call getNextException to see other
errors in the batch.
2017-08-29T17:33:25-06:00 lonepeak9 [pool-1-thread-6]
edu.utah.camplab.server.PayloadThread Write attempt failed for
payload d3d3a62f-64cf-4ffe-b7f8-927943af5c3e. Trying emergency
landing at /tmp/SGS.crashpad
2017-08-29T17:33:25-06:00 lonepeak9 [pool-1-thread-6]
edu.utah.camplab.server.PayloadThread EMERGENCY LANDING: writing run
d3d3a62f-64cf-4ffe-b7f8-927943af5c3e as json to /tmp/SGS.crashpad
On 08/30/2017 11:21 AM, Omar Kilani wrote:
Hi Rob,
Please see the pgbouncer FAQ:
https://pgbouncer.github.io/faq.html
Specifically:
"Disabling prepared statements in JDBC"
Hope this helps!
Regards,
Omar
On Wed, Aug 30, 2017 at 9:20 AM, Rob Sargent <[email protected]> wrote:
This is a cross-post to both pgbouncer and jooq lists, as I am unsure where
in my stack I need to address this issue.
I am testing my throughput by intensionally swamping my systems. The stack
I am using consists of
- postgres 10
- pgbouncer 1.7.2 (running on db machine)
- collector (java Selector programme running on NOT db machine)
- clients (java analysis running on machine(s) NOT db NOT collector)
The clients are spawned from GNU parallel, one second delay each, 100 jobs
with max 16 concurrent (hyper-threaded processor count) and last for about
10 seconds. The clients all finish successfully and more quickly than I can
perform the rest of the stack. This is a temporary, testing situation -
ultimately the clients will take much longer as I do a more realistic amount
(one thousand fold more) of work per invocation.
Each client sends a payload to the collector; the collector has
ThreadPoolExecutor transforming payload into db calls via jOOQ.
The payload inserts 6 new records (over three tables) and updates one
record; the same record updated in all payloads.
I am NOT using PreparedStatment in my code.
pbBouncer is in "pool_mode = transaction"
The jOOQ transaction is
ctx.transaction(ltx -> {
startProcess(ctx); //three inserts, two tables
writeSegments(ctx); //update single record by PK (repeated
per client)
finishProcess(ctx); //three inserts, one table
});
If I correctly read the error message below (the fifth of 5 attempts for
same payload) I must be reusing a pooled db connection for a second payload.
If so, is this pbbouncer failing to close connection after transaction; jooq
not signaling end of transaction, an artifact of intentional overload;
other?
2017-08-29T17:33:25-06:00 lonepeak9 [pool-1-thread-6]
edu.utah.camplab.jx.AbstractPayload d3d3a62f-64cf-4ffe-b7f8-927943af5c3e:
failed write attempt #5, SQL [insert into "process_input" ("id",
"process_id", "input_type", "input_ref") values (?, ?, ?, ?)]; Batch entry 0
insert into "process_input" ("id", "process_id", "input_type", "input_ref")
values ('11c0e3d1-59a5-469e-a77d-89dcc98f03e0'::uuid,
'14271c8d-31ea-4cd7-bcba-62b2436a9c21'::uuid, 'pedfile',
'a3a1326a-cd40-4777-903f-546621028e9f') was aborted: ERROR: prepared
statement "S_1" already exists Call getNextException to see other errors in
the batch.
_______________________________________________
Pgbouncer-general mailing list
[email protected]
http://lists.pgfoundry.org/mailman/listinfo/pgbouncer-general
--
You received this message because you are subscribed to the Google Groups "jOOQ User
Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
