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@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