degrading inser performance

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux