degrading inser performance

From: Eildert Groeneveld
Subject: degrading inser performance
Date: ,
Msg-id: 1442489550.4959.12.camel@fli.bund.de
(view: Whole thread, Raw)
Responses: Re: degrading inser performance  (Ladislav Lenart)
List: pgsql-performance

Tree view

degrading inser performance  (Eildert Groeneveld, )
 Re: degrading inser performance  (Ladislav Lenart, )
  Re: degrading inser performance  (Eildert Groeneveld, )
   Re: degrading inser performance  (Matheus de Oliveira, )
    Re: degrading inser performance  (Eildert Groeneveld, )
     Re: degrading inser performance  (Dave Cramer, )

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?

cheers

Eildert






pgsql-performance by date:

From: Eildert Groeneveld
Date:
Subject: Re: degrading inser performance
From: Dave Stibrany
Date:
Subject: Occasional Really Slow Running Updates/Inserts