On Sun, Jul 31, 2011 at 2:51 PM, Robert Ayrapetyan <robert.ayrapetyan@xxxxxxxxxx> 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. > > Is this known and expected behavior? This is a duplicate post with one on BUGS, being discussed there. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
