Thread: Batch Processing - Autocommit
I am running postgres 7.4 and want the database to process a large
number of transactions at once from a java application. I tried to set
autocommit = false, but as of postgres 7.4 that is not possible.
java.sql.SQLException: ERROR: SET AUTOCOMMIT TO OFF is no longer
supported
I then tried using BEGIN and COMMIT hoping that might work, but
according to the system log (/var/log/messages) each one is being
committed separately:
[382-1] LOG: query: BEGIN WORK;
[383-1] LOG: query: INSERT INTO e_dat3 (S33RM_PT_sig_val, prog,
update_date) VALUES (0.0, 'V8N', '2002-08-19');
[384-1] LOG: query: COMMIT WORK;
[382-1] LOG: query: BEGIN WORK;
[383-1] LOG: query: INSERT INTO e_dat3 (S2033RM_PT_sig_val, prog,
update_date) VALUES (-1.0, 'V8N', '2002-08-19');
[384-1] LOG: query: COMMIT WORK;
[382-1] LOG: query: BEGIN WORK;
[383-1] LOG: query: INSERT INTO e_dat3 (S433RM_PT_sig_val, prog,
update_date) VALUES (-1.0, 'V8N', '2002-08-19');
[384-1] LOG: query: COMMIT WORK;
I do about 7300 SELECTS from the database, and for each I perform 60
calculations and write the results to the database (once per
calculation). Each of the 7300 takes approximately 3 seconds to process,
the whole operation about 6 hours.
top shows java memory usage about 40 - 50%, with postgres usage usually
very low. vmstat shows lots of IO. So I'm assuming that the bottleneck
is in writing the transactions to the database. How can I send large
transactions to the database? (eg for one item, perform one of the 60
calculations on 200 rows, write the 200 rows at once to the database).
OS is debian stable, processor 2GH +, 120 GB 10,000 RPM HD.
Any help / comments / suggestions appreciated.
Thanks
Ron
On Wed, 17 Sep 2003, Ron wrote: > I am running postgres 7.4 and want the database to process a large > number of transactions at once from a java application. I tried to set > autocommit = false, but as of postgres 7.4 that is not possible. > > java.sql.SQLException: ERROR: SET AUTOCOMMIT TO OFF is no longer > supported You need to use the Connection's setAutoCommit method instead of issuing the above SQL directly. Auto commit support has been moved from the backend into the various client libraries in 7.4. Kris Jurka
Kris Jurka wrote:
>On Wed, 17 Sep 2003, Ron wrote:
>
>
>>I am running postgres 7.4 and want the database to process a large
>>number of transactions at once from a java application. I tried to set
>>autocommit = false, but as of postgres 7.4 that is not possible.
>>
>> java.sql.SQLException: ERROR: SET AUTOCOMMIT TO OFF is no longer
>>supported
>>
>>
>
>You need to use the Connection's setAutoCommit method instead of issuing
>the above SQL directly. Auto commit support has been moved from the
>backend into the various client libraries in 7.4.
>
>Kris Jurka
>
>
Sorry I didn't make it clearer, but I was trying to set it in the java app:
conn = DriverManager.getConnection(
"jdbc:postgresql://imo:5432/lytic",
"postgres",
"postgres");
stmt = conn.createStatement();
conn.setAutoCommit(false);
The error message above was generated when I tried to run the java code.
Ron
Are you using the 7.4 version of the jdbc driver? It sound's like you are using the 7.3 driver against a 7.4 database. thanks, --Barry Ron wrote: > Kris Jurka wrote: > >> On Wed, 17 Sep 2003, Ron wrote: >> >> >>> I am running postgres 7.4 and want the database to process a large >>> number of transactions at once from a java application. I tried to set >>> autocommit = false, but as of postgres 7.4 that is not possible. >>> >>> java.sql.SQLException: ERROR: SET AUTOCOMMIT TO OFF is no longer >>> supported >>> >> >> >> You need to use the Connection's setAutoCommit method instead of issuing >> the above SQL directly. Auto commit support has been moved from the >> backend into the various client libraries in 7.4. >> >> Kris Jurka >> >> > Sorry I didn't make it clearer, but I was trying to set it in the java app: > conn = DriverManager.getConnection( > "jdbc:postgresql://imo:5432/lytic", > "postgres", > "postgres"); > stmt = conn.createStatement(); > conn.setAutoCommit(false); > > The error message above was generated when I tried to run the java code. > > Ron > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
Thanks Barry, that was the problem. Batch statements are working now, I'll re-run the app and see how long the run takes now. Aside from using batches, are there any other java/jdbc major performance boosters that anyone cares to suggest? I'm about to tweak the posgtres configuration files and see what gains I can get from there. Thanks Ron Barry Lind wrote: > Are you using the 7.4 version of the jdbc driver? It sound's like you > are using the 7.3 driver against a 7.4 database. > > thanks, > --Barry > > > Ron wrote: > >> Kris Jurka wrote: >> >>> On Wed, 17 Sep 2003, Ron wrote: >>> >>> >>>> I am running postgres 7.4 and want the database to process a large >>>> number of transactions at once from a java application. I tried to set >>>> autocommit = false, but as of postgres 7.4 that is not possible. >>>> >>>> java.sql.SQLException: ERROR: SET AUTOCOMMIT TO OFF is no longer >>>> supported >>>> >>> >>> >>> >>> You need to use the Connection's setAutoCommit method instead of >>> issuing >>> the above SQL directly. Auto commit support has been moved from the >>> backend into the various client libraries in 7.4. >>> >>> Kris Jurka >>> >>> >> Sorry I didn't make it clearer, but I was trying to set it in the >> java app: >> conn = DriverManager.getConnection( >> "jdbc:postgresql://imo:5432/lytic", >> "postgres", >> "postgres"); >> stmt = conn.createStatement(); >> conn.setAutoCommit(false); >> >> The error message above was generated when I tried to run the java code. >> >> Ron >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 4: Don't 'kill -9' the postmaster >> > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > >