Timeline for Loading data from Java to Postgresql very slow
Current License: CC BY-SA 3.0
Post Revisions
12 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Aug 20, 2014 at 18:11 | answer | added | Neil McGuigan | timeline score: 1 | |
| Aug 20, 2014 at 18:04 | answer | added | Tim Child | timeline score: 1 | |
| Aug 19, 2014 at 21:55 | comment | added | nickL | Have you tried PGs COPY cmd to see how quickly it can run once you have the data? postgresql.org/docs/9.2/static/sql-copy.html Also can you see how long it takes to grab the data WITHOUT sending to db to get an idea of how long the data read takes? I had a similar problem but not in Java and ended up writing 1 insert cmd with many sets of values (batching that way) rather than many inserts with one set of values...Nick. | |
| Aug 15, 2014 at 13:40 | comment | added | user2254180 | Thank you for the suggestion to disable autoCommit - that appears to have been the primary issue. With that off and not even batching, my application took 3 minutes 30 to run. With batching it went down to just under 2 minutes. I should also have mentioned my Postgesql instance is an Amazon Micro instance, so that probably explains it takes 2 minutes to run and not 30 seconds. | |
| Aug 15, 2014 at 0:49 | comment | added | Craig Ringer |
See stackoverflow.com/questions/12206600/… and stackoverflow.com/q/758945/398670 . Specifically for Java use the CopyManager.
|
|
| Aug 14, 2014 at 22:40 | comment | added | JB Nizet | Disable autocommit. Use a single transaction to insert everything. For the record, I insert more than 100000 rows, without even using batches, in 20 seconds. | |
| Aug 14, 2014 at 22:39 | comment | added | JB Nizet | This is awfully slow. Make sure the slowness is with the database and not with the download. Try executing 6000 insert queries from psql and see if that's slow as well (which would indicate a database or disk or network problem). If the performance is really with the Java code, then post it here (not just snippets). | |
| Aug 14, 2014 at 22:39 | comment | added | user2254180 | autoCommit is enabled | |
| Aug 14, 2014 at 22:38 | comment | added | user2254180 | Definitely not, I've set a timer on grabbing the files, the very biggest files take less than 5 seconds to download. | |
| Aug 14, 2014 at 22:38 | comment | added | MadProgrammer | Is autoCommit enabled? Do you commit after each batch or at the end? | |
| Aug 14, 2014 at 22:34 | comment | added | user3667171 | are you sure that the performance is not declining in the first part of the process, i.e. getting the files from the internet? | |
| Aug 14, 2014 at 22:26 | history | asked | user2254180 | CC BY-SA 3.0 |