Hi all! During my testing of large work_mem and maintainence_work_mem setting wrt to CREATE INDEX and sorting I encountered a number of things wrt to doing various operations on such a large table (about 106GB on disk with no dead tuples). I will summarize some of the just in case somebody is interested:
-> table used has 5 integer columns non-indexed during the loads -> hardware is a Dual Opteron 280 with 4 [EMAIL PROTECTED],4GHz and 16GB RAM, data is on a multipathed (busy) SAN with different (RAID 10) Arrays for WAL and data. 1. data loading - I'm using COPY with batches of 300M rows it takes *) with one copy running it takes about 20minutes/batch to load the data (~250k rows/sec) and virtually no context switches. *) with two copys running concurrently it takes a bit less then 30 minutes/batch and a steady 40k context switches/sec (~340k rows/sec overall) *) with three copy it takes about 40min/batch at 140k context switches/sec (380k rows/sec overall) the profiles for those runs look very similiar to: samples % symbol name 5065118 20.9607 XLogInsert 3496868 14.4709 DoCopy 2807313 11.6174 CopyReadLine 1373621 5.6844 PageAddItem 1227069 5.0779 heap_formtuple 1193319 4.9383 LWLockAcquire 894243 3.7006 hash_search 717427 2.9689 LWLockRelease 699359 2.8941 pg_atoi 691385 2.8611 FunctionCall3 640383 2.6501 heap_insert 579331 2.3974 int4in 411286 1.7020 AllocSetReset 376452 1.5579 hash_any 349220 1.4452 RelationGetBufferForTuple 261568 1.0824 AllocSetAlloc 257511 1.0656 ReadBuffer while the amount of IO going on is quite a lot it looks like we are still mostly CPU-bound for COPY. 2. updating all of the rows in the table: I updated all of the rows in the table with a simple UPDATE testtable set a=a+1; this took about 2,5 hours (~200rows/sec) with a profile looking like: samples % symbol name 27860285 26.5844 XLogInsert 4828077 4.6070 PageAddItem 4490535 4.2849 heap_update 4267647 4.0722 slot_deform_tuple 3996750 3.8137 LWLockAcquire 3716184 3.5460 slot_getattr 3454679 3.2965 hash_search 2998742 2.8614 hash_any 2909261 2.7760 heap_fill_tuple 2825256 2.6959 LWLockRelease 2283086 2.1785 LockBuffer 2135048 2.0373 ExecTargetList 1636017 1.5611 ExecEvalVar 1632377 1.5576 UnpinBuffer 1566087 1.4944 RelationGetBufferForTuple 1561378 1.4899 ExecMakeFunctionResultNoSets 1511366 1.4421 ReadBuffer 1381614 1.3183 heap_compute_data_size 3. vacuuming this table - it turned out that VACUUM FULL is completly unusable on a table(which i actually expected before) of this size not only to the locking involved but rather due to a gigantic memory requirement and unbelievable slowness. It seems that the heap-scan part of vacuum full completed after about 2 hours ending up with a postmaster having a resident size of about 8,5GB(!!!) with maintainance_work_mem set to 1GB. profile for this stage looks like: samples % symbol name 941058 26.0131 scan_heap 444435 12.2852 HeapTupleSatisfiesVacuum 242117 6.6927 TransactionIdIsInProgress 220044 6.0825 _mdfd_getseg 212571 5.8760 hash_search 186963 5.1681 TransactionIdPrecedes 176016 4.8655 SetBufferCommitInfoNeedsSave 137668 3.8055 TransactionIdDidCommit 137068 3.7889 PageRepairFragmentation 111474 3.0814 TransactionLogFetch 103814 2.8697 LWLockAcquire 102925 2.8451 LWLockRelease 102456 2.8321 hash_any 67199 1.8575 BufferAlloc after that the postmaster started slowly consuming more and more memory, doing virtually no IO and eating CPU like mad with a profile similiar to: samples % symbol name 2708391248 94.1869 repair_frag 155395833 5.4040 enough_space 5707137 0.1985 XLogInsert 1410703 0.0491 PageAddItem 691616 0.0241 BgBufferSync I actually ended up canceling the VACUUM FULL after about 50 hours of runtime with a resident size of ~11,5GB. Stefan ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
