BUG #6136: Perfomance dies when using PK on 64-bit field

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 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.

Sounds weird. Looking at this now.

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

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL 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.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL 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.