On 23-9-2005 15:35, Joost Kraaijeveld wrote:
On Fri, 2005-09-23 at 13:19 +0200, Arjen van der Meijden wrote:
Drop all of them and recreate them once the table is filled. Of course that only works if you know your data will be ok (which is normal for imports of already conforming data like database dumps of existing tables). This will give major performance improvements, if you have indexes and such on the new table.


I will test this a for perfomance improvement, but still, I wonder if ~
100 inserts/second is a reasonable performance for my software/hardware
combination.

For the hardware: no, I don't think it is for such a simple table/small recordsize. I did a few batch-inserts with indexes on tables and was very disappointed about the time it took. But with no indexes and constraints left it flew and the table of 7 million records (of 3 ints and 2 bigints) was imported in 75 seconds, on a bit simpler hardware. That was done using a simple pg_dump-built sql-file which was then fed to psql as input. And of course that used the local unix socket, not the local network interface (I don't know which jdbc takes). But generating a single transaction (as you do) with inserts shouldn't be that much slower.

So I don't think its your hardware, nor your postgresql, although a bit extra maintenance_work_mem may help, if you haven't touched that. Leaving the queries, the application and the driver. But I don't have that much experience with jdbc and postgresql-performance. In php I wouldn't select all the 40M records at once, the resultset would be in the clients-memory and that may actually cause trouble. But I don't know how that is implemented in JDBC, it may of course be using cursors and it would be less of a problem than perhaps. You could try writing the inserts to file and see how long that takes, to eliminate the possibility of your application being slow on other parts than the inserting of data. If that is fast enough, a last resort may be to write a csv-file from java and use that with a copy-statement in psql ;)

Best regards,

Arjen

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Reply via email to