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