From: | Robert Ayrapetyan <robert(dot)ayrapetyan(at)comodo(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Performance die when COPYing to table with bigint PK |
Date: | 2011-07-31 13:51:47 |
Message-ID: | CAAboi9sk0EnbS23-tr86X9i7LdHTfdbTP4aa7r2q7NpWKaHWPA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Hello.
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.
Is this known and expected behavior?
--
Ayrapetyan Robert,
Comodo Anti-Malware Data Processing Analysis and Management System (CAMDPAMS)
http://repo-qa.camdpams.odessa.office.comodo.net/mediawiki/index.php
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Klemme | 2011-08-01 07:18:49 | Re: Trigger or Function |
Previous Message | Filippos | 2011-07-31 12:17:36 | Re: heavy load-high cpu itilization |