degrading inser performance

От: Eildert Groeneveld
Тема: degrading inser performance
Дата: ,
Msg-id: 1442489550.4959.12.camel@fli.bund.de
(см: обсуждение, исходный текст)
Ответы: Re: degrading inser performance  (Ladislav Lenart)
Список: pgsql-performance

Скрыть дерево обсуждения

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 по дате сообщения:

От: Eildert Groeneveld
Дата:
Сообщение: Re: degrading inser performance
От: Dave Stibrany
Дата:
Сообщение: Occasional Really Slow Running Updates/Inserts