Batch Processing - Autocommit
От | Ron |
---|---|
Тема | Batch Processing - Autocommit |
Дата | |
Msg-id | 3F68B21C.7000504@syscor.com обсуждение исходный текст |
Ответы |
Re: Batch Processing - Autocommit
|
Список | pgsql-jdbc |
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
В списке pgsql-jdbc по дате отправления: