Обсуждение: Multicore Postgres 9.0.1 issue - single transaction problem.
Hello Postgres Users. Last days I've installed and configured new x64 release of PostgreSQL running Windows 2008 R2 with dual XEON 5530 processors (2x4xHT = 16 working units). Previously the database was running on Fedora 12 x86_64 under Microsoft hypervisor (HyperV) thus because of the network card driver limitation there was only one core available. The problem I'm facing is a very long, single transaction lasting about 12hours or even more (as it doesn't exist Pragma Autonomous Transaction like Oracle has) that consist of tones of PLPGSQL code, processing a lot of data, causing huge CPU load and disk drive transfers. When moved to the x64 system as described above, the shared memory size is not a problem anymore, the disk channel is running very smoothly, the only suprising think is that the transaction above utilizes only one core of the machine - is it possible to parallelize it without rewriting all the code from scratch? Is there any configuration parameter limiting number of CPUs? The release is a standard/public x64 binary of PostgreSQL 9.0.1, taken following official site. Thanks in advance for any help. Piotr Czekalski -- -------------------------------------------------------------- "TECHBAZA.PL" Sp. z o.o. Technologie WEB, eDB& eCommerce Oddział Gliwice ul. Chorzowska 50 44-100 Gliwice tel. (+4832) 7186081 fax. (+4832) 7003289
On Thu, Dec 2, 2010 at 2:04 PM, Piotr Czekalski <pczekalski@techbaza.pl> wrote: > Hello Postgres Users. > > Last days I've installed and configured new x64 release of PostgreSQL > running Windows 2008 R2 with dual XEON 5530 processors (2x4xHT = 16 working > units). Previously the database was running on Fedora 12 x86_64 under > Microsoft hypervisor (HyperV) thus because of the network card driver > limitation there was only one core available. > > The problem I'm facing is a very long, single transaction lasting about > 12hours or even more (as it doesn't exist Pragma Autonomous Transaction like > Oracle has) that consist of tones of PLPGSQL code, processing a lot of data, > causing huge CPU load and disk drive transfers. > When moved to the x64 system as described above, the shared memory size is > not a problem anymore, the disk channel is running very smoothly, the only > suprising think is that the transaction above utilizes only one core of the > machine - is it possible to parallelize it without rewriting all the code > from scratch? A single connection uses a single CPU. While there's been some talk of parallelizing some part of pgsql, nothing I know of has been done. Is it possible you're doing parts in plpgsql that should really be done externally / with a batch processing system or hadoop or something?