From: Greg Williamson <gwilliamson39@yahoo.com>
To: Stefan Keller <sfkeller@gmail.com>
To: Ivan Voras <ivoras@freebsd.org>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Inserts in 'big' table slowing down the database
Date: Mon, 1 Oct 2012 17:35:58 -0700 (PDT)
Message-ID: <1349138158.88943.YahooMailNeo@web125905.mail.ne1.yahoo.com> (raw)
In-Reply-To: <CAFcOn2_zpBQ1Tky_vHOkBGMLwjHJFdNSUgyZhkg-i3b8-WWMLA@mail.gmail.com>
References: <CAFcOn2_W6v_vqwomCf6DEtXk=N8iB5WU-7XEdcYgX-VFUE32+Q@mail.gmail.com>
<k223s0$e83$1@ger.gmane.org>
<CAFcOn2_zpBQ1Tky_vHOkBGMLwjHJFdNSUgyZhkg-i3b8-WWMLA@mail.gmail.com>
Stefan --
----- Original Message -----
> From: Stefan Keller <sfkeller@gmail.com>
> To: Ivan Voras <ivoras@freebsd.org>
> Cc: pgsql-performance@postgresql.org
> Sent: Monday, October 1, 2012 5:15 PM
> Subject: Re: [PERFORM] Inserts in 'big' table slowing down the database
>
> Sorry for the delay. I had to sort out the problem (among other things).
>
> It's mainly about swapping.
>
> The table nodes contains about 2^31 entries and occupies about 80GB on
> disk space plus index.
> If one would store the geom values in a big array (where id is the
> array index) it would only make up about 16GB, which means that the
> ids are dense (with few deletes).
> Then updates come in every hour as bulk insert statements with entries
> having ids in sorted manner.
> Now PG becomes slower and slower!
> CLUSTER could help - but obviously this operation needs a table lock.
> And if this operation takes longer than an hour, it delays the next
> update.
>
> Any ideas? Partitioning?
pg_reorg if you have the space might be useful in doing a cluster-like action:
<http://reorg.projects.postgresql.org/;
Haven't followed the thread so I hope this isn't redundant.
Partitioning might work if you can create clusters that are bigger than 1 hour -- too many partitions doesn't help.
Greg Williamson
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: gwilliamson39@yahoo.com, sfkeller@gmail.com, ivoras@freebsd.org
Subject: Re: Inserts in 'big' table slowing down the database
In-Reply-To: <1349138158.88943.YahooMailNeo@web125905.mail.ne1.yahoo.com>
* 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