Re: problems with large objects dump

On Thu, Oct 11, 2012 at 7:16 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

>
> It's pretty hard to say without knowing a lot more info about your system
> than you provided. One thing that would shed some light is if you spent
> some time finding out where the time is going --- is the system
> constantly I/O busy, or is it CPU-bound, and if so in which process,
> pg_dump or the connected backend?
>
>
the greatest amount of time is lost in I/O busy.

database_test=# select count(*) from pg_largeobject_metadata;
count
---------
5231973
(1 row)

I never use oprofile, but for a few hours into the process, I could take
this report:

opreport -l /var/lib/pgsql/bin/pg_dump
Using /var/lib/oprofile/samples/ for samples directory.
CPU: Core 2, speed 2333.42 MHz (estimated)
Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit
mask of 0x00 (Unhalted core cycles) count 100000
samples % symbol name
1202449 56.5535 sortDumpableObjects
174626 8.2130 DOTypeNameCompare
81181 3.8181 DeflateCompressorZlib
70640 3.3223 _WriteByte
68020 3.1991 DOCatalogIdCompare
53789 2.5298 WriteInt
39797 1.8717 WriteToc
38252 1.7991 WriteDataToArchive
32947 1.5496 WriteStr
32488 1.5280 pg_qsort
30122 1.4167 dumpTableData_copy
27706 1.3031 dumpDumpableObject
26078 1.2265 dumpBlobs
25591 1.2036 _tocEntryRequired
23030 1.0831 WriteData
21171 0.9957 buildTocEntryArrays
20825 0.9794 _WriteData
18936 0.8906 _WriteBuf
18113 0.8519 BuildArchiveDependencies
12607 0.5929 findComments
11642 0.5475 EndCompressor
10833 0.5095 _CustomWriteFunc
10562 0.4968 WriteDataChunks
10247 0.4819 dumpBlob
5947 0.2797 EndBlob
5824 0.2739 _EndBlob
5047 0.2374 main
5030 0.2366 dumpComment
4959 0.2332 AllocateCompressor
4762 0.2240 dumpSecLabel
4705 0.2213 StartBlob
4052 0.1906 WriteOffset
3285 0.1545 ArchiveEntry
2640 0.1242 _StartBlob
2391 0.1125 pg_calloc
2233 0.1050 findObjectByDumpId
2197 0.1033 SetArchiveRestoreOptions
2149 0.1011 pg_strdup
1760 0.0828 getDumpableObjects
1311 0.0617 ParseCompressionOption
1288 0.0606 med3
1248 0.0587 _WriteExtraToc
944 0.0444 AssignDumpId
916 0.0431 findSecLabels
788 0.0371 pg_malloc
340 0.0160 addObjectDependency
317 0.0149 _ArchiveEntry
144 0.0068 swapfunc
72 0.0034 ScanKeywordLookup
60 0.0028 findObjectByCatalogId
41 0.0019 fmtId
27 0.0013 ExecuteSqlQuery
20 9.4e-04 dumpTable
10 4.7e-04 getTableAttrs
8 3.8e-04 fmtCopyColumnList
6 2.8e-04 shouldPrintColumn
5 2.4e-04 findObjectByOid
3 1.4e-04 dumpFunc
3 1.4e-04 format_function_signature
3 1.4e-04 getTypes
2 9.4e-05 _StartData
2 9.4e-05 buildACLCommands
2 9.4e-05 findLoop
2 9.4e-05 getTables
2 9.4e-05 parseOidArray
2 9.4e-05 selectSourceSchema
1 4.7e-05 TocIDRequired
1 4.7e-05 _EndData
1 4.7e-05 archprintf
1 4.7e-05 dumpACL
1 4.7e-05 dumpCollation
1 4.7e-05 dumpConstraint
1 4.7e-05 dumpOpr
1 4.7e-05 expand_schema_name_patterns
1 4.7e-05 findDumpableDependencies
1 4.7e-05 fmtQualifiedId
1 4.7e-05 getCollations
1 4.7e-05 getExtensions
1 4.7e-05 getFormattedTypeName
1 4.7e-05 getIndexes
1 4.7e-05 makeTableDataInfo
1 4.7e-05 vwrite_msg

thank you very much for your help

regards.

Sergio