BUG #6136: Perfomance dies when using PK on 64-bit field
От | Robert |
---|---|
Тема | BUG #6136: Perfomance dies when using PK on 64-bit field |
Дата | |
Msg-id | 201107311347.p6VDlu0f017165@wwwmaster.postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #6136: Perfomance dies when using PK on 64-bit field
Re: BUG #6136: Perfomance dies when using PK on 64-bit field |
Список | pgsql-bugs |
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.
В списке pgsql-bugs по дате отправления: