random observations while testing with a 1,8B row table

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 cores(at)2,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