Message-ID: <55FAAE0B.9020506@volny.cz>
Date: Thu, 17 Sep 2015 14:11:55 +0200
From: Ladislav Lenart <lenartlad@volny.cz>
User-Agent: Mozilla/5.0 (X11; Linux i686;
 rv:31.0) Gecko/20100101 Icedove/31.7.0
MIME-Version: 1.0
To: eildert.groeneveld@fli.bund.de,
 postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: degrading inser performance
References: <1442489550.4959.12.camel@fli.bund.de>
In-Reply-To: <1442489550.4959.12.camel@fli.bund.de>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 17.9.2015 13:32, Eildert Groeneveld wrote:
> Dear list
> 
> I am experiencing a rather severe degradation of insert performance
> starting from an empty database:
> 
> 
> 	   120.000 mio SNPs imported in    28.9 sec -    4.16 mio/sec
> 	   120.000 mio SNPs imported in    40.9 sec -    2.93 mio/sec
> 	   120.000 mio SNPs imported in    49.7 sec -    2.41 mio/sec
> 	   120.000 mio SNPs imported in    58.8 sec -    2.04 mio/sec
> 	   120.000 mio SNPs imported in    68.9 sec -    1.74 mio/sec
> 	   120.000 mio SNPs imported in    77.0 sec -    1.56 mio/sec
> 	   120.000 mio SNPs imported in    85.1 sec -    1.41 mio/sec
> 	   120.000 mio SNPs imported in    94.0 sec -    1.28 mio/sec
> 	   120.000 mio SNPs imported in   103.4 sec -    1.16 mio/sec
> 	   120.000 mio SNPs imported in   108.9 sec -    1.10 mio/sec
> 	   120.000 mio SNPs imported in   117.2 sec -    1.02 mio/sec
> 	   120.000 mio SNPs imported in   122.1 sec -    0.98 mio/sec
> 	   120.000 mio SNPs imported in   132.6 sec -    0.90 mio/sec
> 	   120.000 mio SNPs imported in   142.0 sec -    0.85 mio/sec
> 	   120.000 mio SNPs imported in   147.3 sec -    0.81 mio/sec
> 	   120.000 mio SNPs imported in   154.4 sec -    0.78 mio/sec
> 	   120.000 mio SNPs imported in   163.9 sec -    0.73 mio/sec
> 	   120.000 mio SNPs imported in   170.1 sec -    0.71 mio/sec
> 	   120.000 mio SNPs imported in   179.1 sec -    0.67 mio/sec
> 	   120.000 mio SNPs imported in   186.1 sec -    0.64 mio/sec
> 
> each line represents the insertion of 20000 records in two tables which is
> not really a whole lot. Also, these 20000 get inserted in one program run.
> The following lines are then again each the execution of that program.
> The insert are a text string in one table and a bit varying of length packed
> 24000 bits, also no big deal.
> 
> As can be seen the degradation is severe going from 29 sec up to 186 sec
> for the same amount of data inserted.
> 
> I have dropped the indices and primary keys, but that did not change the 
> picture. Made commits every 100 records: also no effect.
> I have also played around with postgresql.conf but also this had no real 
> effect (which is actually not surprising considering the small size of the 
> database).
> 
> At this stage the who database has a size of around 1GB.
> 
> I am using pg 9.4
> 
> any idea of what might be going on?


Hello.

Just a couple of questions...

You talk about two tables; have you also dropped FKs (you only mention indices
and PK)?

What SQL do you use for inserting the data:
 * one INSERT per row with autocommit
 * one INSERT per row inside BEGIN...COMMIT
 * one INSERT per bulk (20 000 rows)
 * one COPY per bulk (20 000 rows)
?

Is the loading of data the only activity on the server?

See also:
http://www.postgresql.org/docs/9.4/static/populate.html


HTH,

Ladislav Lenart


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance