Hi Rob, It depends on the code -- if you're not explicitly using prepared statements from other pgsql sessions it should be fine.
There's no way to use 'pool = transaction' without 'prepareThreshold=0'. Even with 'prepareThreshold=0', pgjdbc still uses prepared statements. It just doesn't *reuse* them (which is where the problem comes in with transaction pooling mode). The performance improvement in high RPS environments from using transaction pooling mode far outweighs any benefits of reusing prepared statements. Otherwise you can switch to 'pool = session'. Regards, Omar On Wed, Aug 30, 2017 at 10:55 AM, Rob Sargent <[email protected]> wrote: > 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.
