Your message was dropped into my Spam lable :S 2011/3/30 Strange, John W <john.w.strange@xxxxxxxxxxxx>: > Just some information on our setup: > > - HP DL585 G6 > - 4 x AMD Opteron 8435 (24 cores) > - 256GB RAM > - 2 FusionIO 640GB PCI-SSD (RAID0) > - dual 10GB ethernet. > > - we have several tables that we store calculated values in. > - these are inserted by a compute farm that calculates the results and stores them into a partitioned schema (schema listed below) > - whenever we do a lot of inserts we seem to get exclusive locks. > > Is there something we can do to improve the performance around locking when doing a lot of parallel inserts with COPY into? ÂWe are not IO bound, what happens is that the copies start to slow down and continue to come in and cause the client to swap, we had hit over 800+ COPYS were in a waiting state, which forced us to start paging heavily creating an issue. ÂIf we can figure out the locking issue the copys should clear faster requiring less memory in use. > > [ 2011-03-30 15:54:55.886 EDT ] 14405 [local] asgprod:4d938288.3845 LOG: Âprocess 14405 still waiting for ExclusiveLock on extension of relation 470273 of database 16384 after 5001.894 ms > [ 2011-03-30 15:54:55.886 EDT ] 14405 [local] asgprod:4d938288.3845 CONTEXT: ÂCOPY reportvalues_part_1931, line 1: "660250   Â41977959    Â11917  584573.43642105709" > [ 2011-03-30 15:54:55.886 EDT ] 14405 [local] asgprod:4d938288.3845 STATEMENT: ÂCOPY reportvalues_part_1931 FROM stdin USING DELIMITERS '    ' > [ 2011-03-30 15:54:56.015 EDT ] 7294 [local] asgprod:4d938939.1c7e LOG: Âprocess 7294 still waiting for ExclusiveLock on extension of relation 470606 of database 16384 after 5062.968 ms > [ 2011-03-30 15:54:56.015 EDT ] 7294 [local] asgprod:4d938939.1c7e CONTEXT: ÂCOPY reportvalues_part_1932, line 158: "660729   41998839    Â887   45000.0" > [ 2011-03-30 15:54:56.015 EDT ] 7294 [local] asgprod:4d938939.1c7e STATEMENT: ÂCOPY reportvalues_part_1932 FROM stdin USING DELIMITERS '    Â' > [ 2011-03-30 15:54:56.077 EDT ] 25781 [local] asgprod:4d938556.64b5 LOG: Âprocess 25781 still waiting for ExclusiveLock on extension of relation 470606 of database 16384 after 5124.463 ms > But you are using stdin for COPY! The best way is use files. Maybe you must review postgresql.conf configuration, especially the WAL configuration. How many times you do this procedure? which is the amount of data involved? -- --        Emanuel Calvo        Helpame.com -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
