From: Eildert Groeneveld <eildert.groeneveld@fli.bund.de>
To: postgres performance list <pgsql-performance@postgresql.org>
Subject: degrading inser performance
Date: Thu, 17 Sep 2015 13:32:30 +0200
Message-ID: <1442489550.4959.12.camel@fli.bund.de> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
--
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: eildert.groeneveld@fli.bund.de
Subject: Re: degrading inser performance
In-Reply-To: <1442489550.4959.12.camel@fli.bund.de>
* 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