[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-bugs
Subject:    [BUGS] BUG #6136: Perfomance dies when using PK on 64-bit field
From:       "Robert" <robert.ayrapetyan () gmail ! com>
Date:       2011-07-31 13:47:56
Message-ID: 201107311347.p6VDlu0f017165 () wwwmaster ! postgresql ! org
[Download RAW message or body]


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.

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[prev in list] [next in list] [prev in thread] [next in thread]