Обсуждение: BUG #6136: Perfomance dies when using PK on 64-bit field

The following bug has been logged online:

Bug reference:      6136
Logged by:          Robert
Email address:      robert.ayrapetyan@gmail.com
PostgreSQL version: 9.0.4
Operating system:   FreeBSD 8.2 64 bit
Description:        Perfomance dies when using PK on 64-bit field
Details:

I've found strange behavior of my pg installation (tested both 8.4 and 9.0 -
they behave same) on FreeBSD platform.
In short - when some table have PK on bigint field - COPY to that table from
file becomes slower and slower as table grows. When table reaches ~5GB -
COPY of 100k records may take up to 20 mins. I've experimented with all
params in configs, moved indexes to separate hdd etc - nothing made any
improvement. However, once I'm dropping 64 bit PK - COPY of 100k records
passes in seconds. Interesting thing - same table has other indexes,
including composite ones, but none of them include bigint fields, that's why
I reached decision that bug connected with indexes on bigint fields only.

In terms of IO picture is following: after copy started gstat shows 100%
load on index partition (as I mentioned above - I've tried separate hdd to
keep index tablespace), large queue (over 2k elements), and constant slow
write on speed of ~2MB\s. Hdd becomes completely unresponsive, even ls on
empty folder hangs for minute or so.

To avoid thoughts like "your hdd is slow, you haven't tuned postgresql.conf
etc" - all slowness dissapears with drop of bigint PK, same time other
indexes on same table remain alive. And yes - I've tried drop PK \ recreate
PK, vacuum full analyze and all other things - nothing helped, only drop
helps.

On Sun, Jul 31, 2011 at 2:47 PM, Robert <robert.ayrapetyan@gmail.com> wrote:

> I've found strange behavior of my pg installation (tested both 8.4 and 9.=
0 -
> they behave same) on FreeBSD platform.
> In short - when some table have PK on bigint field - COPY to that table f=
rom
> file becomes slower and slower as table grows. When table reaches ~5GB -
> COPY of 100k records may take up to 20 mins. I've experimented with all
> params in configs, moved indexes to separate hdd etc - nothing made any
> improvement. However, once I'm dropping 64 bit PK - COPY of 100k records
> passes in seconds. Interesting thing - same table has other indexes,
> including composite ones, but none of them include bigint fields, that's =
why
> I reached decision that bug connected with indexes on bigint fields only.
>
> In terms of IO picture is following: after copy started gstat shows 100%
> load on index partition (as I mentioned above - I've tried separate hdd to
> keep index tablespace), large queue (over 2k elements), and constant slow
> write on speed of ~2MB\s. Hdd becomes completely unresponsive, even ls on
> empty folder hangs for minute or so.
>
> To avoid thoughts like "your hdd is slow, you haven't tuned postgresql.co=
nf
> etc" - all slowness dissapears with drop of bigint PK, same time other
> indexes on same table remain alive. And yes - I've tried drop PK \ recrea=
te
> PK, vacuum full analyze and all other things - nothing helped, only drop
> helps.

Sounds weird. Looking at this now.

Could be that its storing the wrong kind of plan on the RI trigger for PK.

--=20
=A0Simon Riggs=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 http:/=
/www.2ndQuadrant.com/
=A0PostgreSQL Development, 24x7 Support, Training & Services

On Mon, Aug 1, 2011 at 8:25 AM, simon <simon@2ndQuadrant.com> wrote:

> Could be that its storing the wrong kind of plan on the RI trigger for PK.

No, not that. Will look elsewhere.

--=20
=A0Simon Riggs=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 http:/=
/www.2ndQuadrant.com/
=A0PostgreSQL Development, 24x7 Support, Training & Services

Hi,

What happens if you drop other indices with a similar size before starting the
copy? It might just be the additional io-load (causing more checkpoints, more
random io, etc) is causing the problems.

Could you share your config and hw info?

Greetings,

Andres

Hi.

My simple testcase is:

> Testcase may be as simple as:
>
> CREATE TABLESPACE tblsp_ix LOCATION '/some_dedicated_hdd'
>
> CREATE TABLE tp_req
> (
>    id bigint PRIMARY KEY USING INDEX TABLESPACE tblsp_ix,
>    arrived timestamp NOT NULL,
>    real_ip inet,
>    tp_id integer NOT NULL,
>    action_id smallint NOT NULL,
>    subject_id smallint NOT NULL
> );
> CREATE INDEX ix_tp_req ON trafd.tp_req
>    USING btree (arrived, tp_id, action_id, subject_id) TABLESPACE tblsp_ix;
>
> Then generate ~500 files every ~7-10Mb in size (every file contains
> 80k-100k rows)
> with specified columns.
>
> Copy them to table one by one in cycle:
> psql -d fake_db -c "COPY tp_req(id, arrived, real_ip, tp_id,
> action_id, subject_id) FROM '${f}'"
>
> Filesystem on tblsp_ix is ufs2.

To your question - I've tried to drop ix_tp_req alone leaving PK in
place - situation was same.
Only dropping PK helps.


On Mon, Aug 1, 2011 at 11:10 AM, Andres Freund <andres@anarazel.de> wrote:
> Hi,
>
> What happens if you drop other indices with a similar size before starting the
> copy? It might just be the additional io-load (causing more checkpoints, more
> random io, etc) is causing the problems.
>
> Could you share your config and hw info?
>
> Greetings,
>
> Andres
>