Re: [PERFORM] Multicore Postgres 9.0.1 issue - single transaction problem.
On Thu, Dec 2, 2010 at 2:04 PM, Piotr Czekalski 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? -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] 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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] SELECT INTO large FKyed table is slow
Mario Splivalo wrote: > It is OT, but, could you please shead just some light on that? > Part of my next project is to test performance of pg9 on both > windows and linux systems so I'd appreciate any data/info you both > may have. I don't know how much was the filesystem, but with both tuned to the best of our ability Linux on xfs ran much faster than Windows on NTFS. The lack of atomic operations and a lockfile utility on Windows/NTFS was something of a handicap. I have found Linux to be much more reliable and (once I got my bash scripting knowledge of common Linux utilities to a certain level), much easier to administer. Getting my head around xargs was, I think, the tipping point. ;-) -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] SELECT INTO large FKyed table is slow
On 12/01/2010 10:43 PM, Pierre C wrote: On Wed, 01 Dec 2010 18:24:35 +0100, Kevin Grittner wrote: Mladen Gogala wrote: There is a operating system which comes with a very decent extent based file system and a defragmentation tool, included in the OS. The file system is called "NTFS" Been there, done that. Not only was performance quite poor compared to Linux, but reliability and staff time to manage things suffered in comparison to Linux. Please don't start with NTFS. It is the worst excuse for a filesystem I've ever seen. It is OT, but, could you please shead just some light on that? Part of my next project is to test performance of pg9 on both windows and linux systems so I'd appreciate any data/info you both may have. Mario -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] SELECT INTO large FKyed table is slow
On 12/01/2010 09:43 AM, Pierre C wrote: Note that in both cases postgres reports that the FK checks take 92-120 milliseconds... which is a normal time for about 4000 rows. Inserting 4000 lines with just a few fields like you got should take quite much less than 1 s... Where the rest of the time goes, I have no idea. Disk thrashing ? Locks ? Gremlins ? - try it on a fresh copy of all your tables (CREATE TABLE, INSERT INTO SELECT) - try to put the WAL on a separate physical disk (or do a check with fsync=off) - try it on another computer - try it on another harddisk - run oprofile on a debug compile of postgres - it could even be the process title updates (I don't think so but...) - try a ramdisk tablespace I'm allready running it with fsync=off. The funny thing is, as I add new realm it runs fine until the history table grows around 5M rows. After that the slowdown is huge. I'm trying this on new hardware this weekend, I'll post here the results. Mario -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
