Message-ID: <43340BFE.1070503@tweakers.net>
Date: Fri, 23 Sep 2005 16:06:54 +0200
From: Arjen van der Meijden <acmmailing@tweakers.net>
User-Agent: Mozilla Thunderbird 1.0RC1 (Windows/20041201)
MIME-Version: 1.0
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
Cc: Michael Stone <mstone+postgres@mathom.us>,
 pgsql-performance@postgresql.org
Subject: Re: How to determine cause of performance problem?
References: <1127458167.2475.105.camel@Panoramix>	
	<20050923095517.GW14918@mathom.us>
	<1127470875.3524.17.camel@Panoramix>	
	<20050923110500.GX14918@mathom.us> <4333E4DB.4020206@tweakers.net>
	<1127482558.3524.23.camel@Panoramix>
In-Reply-To: <1127482558.3524.23.camel@Panoramix>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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