Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1ZcaOm-0006Zy-T4 for pgsql-performance@arkaria.postgresql.org; Thu, 17 Sep 2015 14:42:56 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1ZcaOm-0008Vb-CN for pgsql-performance@arkaria.postgresql.org; Thu, 17 Sep 2015 14:42:56 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1ZcaN9-0006ey-4f for pgsql-performance@postgresql.org; Thu, 17 Sep 2015 14:41:15 +0000 Received: from mail.tzv.fal.de ([192.108.34.20]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1ZcaN5-00018A-Ul for pgsql-performance@postgresql.org; Thu, 17 Sep 2015 14:41:14 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.tzv.fal.de (Postfix) with ESMTP id A65C61000A26C; Thu, 17 Sep 2015 16:41:10 +0200 (CEST) X-Virus-Scanned: Debian amavisd-new at mail.tzv.fal.de Received: from mail.tzv.fal.de ([127.0.0.1]) by localhost (mail.tzv.fal.de [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id OsSL4Q0GnN3P; Thu, 17 Sep 2015 16:41:10 +0200 (CEST) Received: from eno (unknown [46.21.0.103]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by mail.tzv.fal.de (Postfix) with ESMTPSA id 8BFD810001196; Thu, 17 Sep 2015 16:41:09 +0200 (CEST) Message-ID: <1442500868.4959.35.camel@fli.bund.de> Subject: Re: degrading inser performance From: Eildert Groeneveld Reply-To: eildert.groeneveld@fli.bund.de To: Matheus de Oliveira Cc: Ladislav Lenart , postgres performance list Date: Thu, 17 Sep 2015 16:41:08 +0200 In-Reply-To: References: <1442489550.4959.12.camel@fli.bund.de> <55FAAE0B.9020506@volny.cz> <1442492365.4959.18.camel@fli.bund.de> Organization: FLI Content-Type: multipart/alternative; boundary="=-XfGqpXAHsfq0MQ2U8ji7" X-Mailer: Evolution 3.16.5-1ubuntu1 Mime-Version: 1.0 X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --=-XfGqpXAHsfq0MQ2U8ji7 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: 7bit Thanks for your input! On Do, 2015-09-17 at 11:21 -0300, Matheus de Oliveira wrote: > > On Thu, Sep 17, 2015 at 9:19 AM, Eildert Groeneveld < > eildert.groeneveld@fli.bund.de> wrote: > > > * one COPY per bulk (20 000 rows) > > copy does not fit so well, as it is not only initial populating. > > > Why do you say COPY doesn't fit? It seems to me that COPY fits > perfectly for your case, and would certainly make the load faster. well, more than one table needs to get populated and data is not really available in one file. > I suspect (not sure though) that the degradation is most because you are inserting one row at a time, and, it needs to verify FSM (Free Space Map) for each tuple inserted, when the table start to get more populated, this verification starts to become slower. If that is really the case, COPY would certainly improve that, or even INSERT with many rows at once. allright, sounds reasonable. But what is your experience: is it possible that inserting the first 20000 records takes 29 seconds while inserting lot 20 (i.e. 9*20000 later) takes 186.9 sec? after all we are talking only about 200000 records? That take 6 times longer!! odd, anyone has an idea? greetings Eildert > > Regards,> -- > Matheus de Oliveira> > > > -- 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 ================================================== --=-XfGqpXAHsfq0MQ2U8ji7 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable
Thanks for your input!
On Do, 2015-= 09-17 at 11:21 -0300, Matheus de Oliveira wrote:

On Thu, Sep 17, 2015 at 9:19 AM, Eildert Groeneveld = <eil= dert.groeneveld@fli.bund.de> wrote:
>  * one COPY per bulk (20 000 rows)
copy does not fit so well, as it is not only initial populating.

<= /blockquote>

Why do you say COPY = doesn't fit? It seems to me that COPY fits perfectly for your case, and wou= ld certainly make the load faster.
well, m= ore than one table needs to get populated and data is not really available = in one file.

I suspect (not sure though) that the degradation is most b= ecause you are inserting one row at a time, and, it needs to verify FSM (Fr= ee Space Map) for each tuple inserted, when the table start to get more pop= ulated, this verification starts to become slower. If that is really the ca= se, COPY would certainly improve that, or even INSERT with many rows at onc= e.
allright, sounds reasonable. 

But what is your experience: is it possible that =
inserting the first 20000 records takes 29 seconds while inserti= ng lot 20 (i.e. 9*20000 later) takes
186.9 sec? after all we are = talking only about 200000 records? That take 6 times longer!!
odd, anyone has an idea?

greetings

Eildert


Regards,
--
Matheus de Oliveira
<= br>
--=20
Eildert Groeneveld
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D
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=20
web:    http://vce.tzv.fal.de
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
--=-XfGqpXAHsfq0MQ2U8ji7--