Re: degrading inser performance

Nobody has asked what kind of machine this is ???

Hard disks, memory, etc.

What are your relevant settings in postgresql.conf ? Shared buffers,
checkpoints, etc.

Also how big are the inserts ? What else is this machine doing ? Is it bare
hardware, or a VM ?

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 17 September 2015 at 10:41, Eildert Groeneveld <
eildert(dot)groeneveld(at)fli(dot)bund(dot)de> wrote:

> 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(dot)groeneveld(at)fli(dot)bund(dot)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(dot)groeneveld(at)fli(dot)bund(dot)de
> web: http://vce.tzv.fal.de
> ==================================================
>
>