From: Ladislav Lenart <lenartlad@volny.cz>
To: eildert.groeneveld@fli.bund.de, postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: degrading inser performance
Date: Thu, 17 Sep 2015 14:11:55 +0200
Message-ID: <55FAAE0B.9020506@volny.cz> (raw)
In-Reply-To: <1442489550.4959.12.camel@fli.bund.de>
References: <1442489550.4959.12.camel@fli.bund.de>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lenartlad@volny.cz
Subject: Re: degrading inser performance
In-Reply-To: <55FAAE0B.9020506@volny.cz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox