Re: degrading inser performance - Mailing list pgsql-performance

From Eildert Groeneveld
Subject Re: degrading inser performance
Date
Msg-id 1442492365.4959.18.camel@fli.bund.de
Whole thread Raw
In response to Re: degrading inser performance  (Ladislav Lenart <lenartlad@volny.cz>)
Responses Re: degrading inser performance  (Matheus de Oliveira <matioli.matheus@gmail.com>)
List pgsql-performance
On Do, 2015-09-17 at 14:11 +0200, Ladislav Lenart wrote:
> 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)?
yes, they were all gone
>
> What SQL do you use for inserting the data:
I go through ecpg
>  * one INSERT per row with autocommit
yes
>  * one INSERT per row inside BEGIN...COMMIT
also this, same result as above
>  * one INSERT per bulk (20 000 rows)
>  * one COPY per bulk (20 000 rows)
copy does not fit so well, as it is not only initial populating.

> Is the loading of data the only activity on the server?
yes, it is. I have this "feature" on every machine

> See also:
> http://www.postgresql.org/docs/9.4/static/populate.html
Thanks, yes, I have been through this.

millions of records seem to be the staple diet of PG, here the
degradation starts already with the second 20000 record batch.
>
greetings

Eildert
> HTH,
>
> Ladislav Lenart
>
>
--
Eildert Groeneveld
===================================================
Institute of Farm Animal Genetics (FLI)
Mariensee 31535 Neustadt Germany
Tel : (+49)(0)5034 871155 Fax : (+49)(0)5034 871143
e-mail: eildert.groeneveld@fli.bund.de
web:    http://vce.tzv.fal.de
==================================================




pgsql-performance by date:

Previous
From: Ladislav Lenart
Date:
Subject: Re: degrading inser performance
Next
From: Matheus de Oliveira
Date:
Subject: Re: degrading inser performance