Re: 8.4devel out of memory - Mailing list pgsql-hackers

>>> Tom Lane <tgl@sss.pgh.pa.us> wrote: 
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
>> The tables and views aren't that hard; finding a way to generate
>> enough fake data may be a challenge.  (I assume that since it took
>> over a half hour to run out of memory, the volume of data needs to
be
>> sufficient to get there.)
> 
> We don't really need 2GB of leakage to find the problem ... a query
> that generates a couple hundred meg of bloat would be plenty.
> 
> Since we don't know how much space the query would have needed to run
to
> completion, it's likely that something involving much less than a
tenth
> as much data would still be enough to make the leak obvious.
I've got the database down to the minimum database objects needed for
the query (three tables, five views on them, and a bunch of domains),
and gotten those tables down to 10% of their original size, with all
confidential data turned to meaningless literals.  The problem still
happens on today's snapshot.
kgrittn@OLR-DEV-PG:~> cat /proc/version
Linux version 2.6.5-7.286-bigsmp (geeko@buildhost) (gcc version 3.3.3
(SuSE Linux)) #1 SMP Thu May 31 10:12:58 UTC 2007
kgrittn@OLR-DEV-PG:~> cat /etc/SuSE-release
SUSE LINUX Enterprise Server 9 (i586)
VERSION = 9
PATCHLEVEL = 3
kgrittn@OLR-DEV-PG:~> /usr/local/pgsql-8.4dev-20081008/bin/pg_config
BINDIR = /usr/local/pgsql-8.4dev-20081008/bin
DOCDIR = /usr/local/pgsql-8.4dev-20081008/share/doc
HTMLDIR = /usr/local/pgsql-8.4dev-20081008/share/doc
INCLUDEDIR = /usr/local/pgsql-8.4dev-20081008/include
PKGINCLUDEDIR = /usr/local/pgsql-8.4dev-20081008/include
INCLUDEDIR-SERVER = /usr/local/pgsql-8.4dev-20081008/include/server
LIBDIR = /usr/local/pgsql-8.4dev-20081008/lib
PKGLIBDIR = /usr/local/pgsql-8.4dev-20081008/lib
LOCALEDIR = /usr/local/pgsql-8.4dev-20081008/share/locale
MANDIR = /usr/local/pgsql-8.4dev-20081008/share/man
SHAREDIR = /usr/local/pgsql-8.4dev-20081008/share
SYSCONFDIR = /usr/local/pgsql-8.4dev-20081008/etc
PGXS = /usr/local/pgsql-8.4dev-20081008/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--prefix=/usr/local/pgsql-8.4dev-20081008'
'--enable-integer-datetimes' '--enable-debug' '--disable-nls'
CC = gcc
CPPFLAGS = -D_GNU_SOURCE
CFLAGS = -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wendif-labels
-fno-strict-aliasing -g
CFLAGS_SL = -fpic
LDFLAGS = -Wl,-rpath,'/usr/local/pgsql-8.4dev-20081008/lib'
LDFLAGS_SL =
LIBS = -lpgport -lz -lreadline -lcrypt -ldl -lm
VERSION = PostgreSQL 8.4devel
listen_addresses = '*'
port = 5666
max_connections = 200
shared_buffers = 256MB
temp_buffers = 10MB
max_prepared_transactions = 0
work_mem = 16MB
maintenance_work_mem = 400MB
bgwriter_lru_maxpages = 1000
bgwriter_lru_multiplier = 4.0
wal_buffers = 256kB
checkpoint_segments = 50
seq_page_cost = 0.1
random_page_cost = 0.1
effective_cache_size = 3GB
geqo = off
default_statistics_target = 100
from_collapse_limit = 20
join_collapse_limit = 20
logging_collector = on
log_connections = on
log_disconnections = on
log_line_prefix = '[%m] %p %q<%u %d %r> '
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 10
autovacuum_analyze_threshold = 10
datestyle = 'iso, mdy'
lc_messages = 'C'
lc_monetary = 'C'
lc_numeric = 'C'
lc_time = 'C'
default_text_search_config = 'pg_catalog.english'
escape_string_warning = off
sql_inheritance = off
standard_conforming_strings = on
vmstat 1 output at start and end of process
(process interrupted by ^C)
procs -----------memory---------- ---swap-- -----io---- --system--
----cpu----r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us
sy id wa0  0  31620 426432  11876 2363148    0    0     2     1    1     7  4 
0 96  00  0  31620 426432  11876 2363148    0    0     0     0 1016   680  0 
0 100  00  0  31620 426432  11876 2363148    0    0     0     0 1034   787  3 
0 98  00  0  31620 426432  11884 2363140    0    0     0    12 1017   702  0 
0 99  10  0  31620 426308  11884 2363140    0    0     0    27 1019   704  0 
0 100  00  0  31620 426308  11884 2363140    0    0     0     0 1023   777  0 
0 99  00  0  31620 426324  11884 2363140    0    0     0     0 1017   677  0 
0 100  00  0  31620 426324  11884 2363140    0    0     0     0 1007   716  3 
0 97  00  0  31620 426324  11884 2363140    0    0     0     0 1011   705  0 
0 100  00  0  31620 426316  11884 2363140    0    0     0    30 1017   715  0 
0 100  00  0  31620 426308  11884 2363140    0    0     0     0 1020   812  0 
1 99  00  0  31620 426308  11884 2363140    0    0     0     0 1016   686  0 
0 100  00  0  31620 426308  11884 2363140    0    0     0     0 1013   717  0 
0 100  00  0  31620 426308  11884 2363140    0    0     0     0 1013   712  0 
0 100  00  0  31620 426340  11884 2363140    0    0     0     6 1013   693  0 
0 100  00  0  31620 426340  11884 2363140    0    0     0     8 1028   773  0 
0 100  00  0  31620 426340  11884 2363140    0    0     0     0 1014   728  0 
0 100  00  0  31620 426200  11884 2363140    0    0     0     8 1047   787  2 
0 99  00  0  31620 426200  11884 2363140    0    0     0     0 1021   729  0 
0 100  00  0  31620 426200  11884 2363140    0    0     0    26 1017   718  0 
0 100  00  0  31620 426200  11884 2363140    0    0     0     4 1018   792  0 
0 100  00  0  31620 426200  11884 2363140    0    0     0     8 1026   717  0 
0 100  00  0  31620 426200  11884 2363140    0    0     0     9 1026   747  9 
0 92  00  0  31620 426200  11884 2363140    0    0     0     8 1013   681  0 
0 100  00  0  31620 426200  11884 2363140    0    0     0    18 1018   679  0 
0 100  00  0  31620 426192  11884 2363140    0    0     0     0 1021   759  0 
0 100  00  0  31620 426192  11884 2363140    0    0     0     0 1009   670  0 
0 100  00  0  31620 426192  11884 2363140    0    0     0     0 1018   679  0 
0 100  00  0  31620 426192  11884 2363140    0    0     0     0 1020   711  0 
0 100  00  0  31620 426192  11884 2363140    0    0     0    86 1025   710  0 
0 100  01  0  31620 426168  11884 2363140    0    0     0     0 1020   754  0 
0 100  01  0  31620 419968  11884 2366224    0    0     0     0 1042   714 38 
1 61  01  0  31620 418480  11884 2366224    0    0     0    13 1036   870 66
25 10  01  0  31620 416992  11884 2366224    0    0     0     0 1016   745 49 
1 50  01  0  31620 415520  11884 2366224    0    0     0    24 1020   755 49 
1 50  02  0  31620 414032  11884 2366224    0    0     0    13 1029   831 50 
1 50  01  0  31620 412544  11884 2366224    0    0     0     0 1012   716 50 
1 50  01  0  31620 411180  11884 2366224    0    0     0     0 1012   742 49 
1 50  01  0  31620 409692  11884 2366224    0    0     0     0 1009   755 49 
1 50  01  0  31620 408204  11884 2366224    0    0     0    84 1020   730 49 
1 50  01  0  31620 406592  11884 2366224    0    0     0     0 1019   801 49 
1 50  01  0  31620 405104  11884 2366224    0    0     0     0 1010   735 49 
3 49  01  0  31620 403756  11884 2366224    0    0     0     0 1011   748 49 
1 50  01  0  31620 402268  11884 2366224    0    0     0     0 1019   712 49 
1 50  01  0  31620 400780  11884 2366224    0    0     0    22 1021   731 49 
1 50  01  0  31620 399268  11884 2366224    0    0     0     0 1017   789 49 
1 50  01  0  31620 397780  11884 2366224    0    0     0     0 1015   705 49 
1 50  01  0  31620 396292  11884 2366224    0    0     0     0 1038   749 50 
1 49  02  0  31620 394680  11884 2366224    0    0     0     0 1019   767 49 
1 50  01  0  31620 393192  11884 2366224    0    0     0   109 1028   716 49 
1 50  01  0  31620 391704  11884 2366224    0    0     0     4 1021   775 49 
1 50  01  0  31620 390216  11884 2366224    0    0     0     4 1017   699 50 
1 50  0
procs -----------memory---------- ---swap-- -----io---- --system--
----cpu----r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us
sy id wa3  0  31860 136604   4348 2183840    0    0     0     4 1016   665 49 
1 50  02  0  31860 137844   4348 2181784    0    0     0     7 1031   739 49 
1 50  02  0  31860 137596   4348 2180756    0    0     0    19 1016   669 49 
1 50  02  0  31860 137348   4348 2178700    0    0     0     4 1013   670 49 
1 50  02  0  31860 137240   4356 2177664    0    0     0    27 1023   709 49 
1 49  12  0  31860 136992   4356 2176636    0    0     0   105 1034   680 49 
1 50  02  0  31860 136744   4356 2175608    0    0     0     4 1017   724 49 
2 50  03  0  31860 136620   4356 2173552    0    0     0    19 1018   665 49 
1 50  02  0  31860 137380   4356 2171496    0    0     0     7 1014   662 50 
1 50  02  0  31860 137256   4356 2170468    0    0     0     4 1022   653 49 
1 50  02  0  31860 137008   4356 2169440    0    0     0    28 1017   669 49 
1 50  02  0  31860 136860   4356 2168412    0    0     0    23 1026   742 50 
1 50  02  0  31860 318396   4356 2167384    0    0     0    25 1016   732 51 
3 47  01  0  31860 639556   4356 2167384    0    0     0    11 1016   686  0 
2 98  01  0  31860 639556   4356 2167384    0    0     0    19 1039   757  2 
1 98  01  0  31860 639556   4356 2167384    0    0     0    36 1019   639  0 
0 100  01  0  31860 639060   4356 2167384    0    0     0  8240 1125   779  0 
0 98  11  0  31860 639680   4356 2167384    0    0     0   104 1091   767  0 
0 78 222  0  31860 639680   4356 2167384    0    0     0     0 1011   637  0 
0 100  01  0  31860 639680   4356 2167384    0    0     0     0 1017   652  0 
0 100  01  0  31860 639696   4356 2167384    0    0     0    29 1021   666  0 
0 100  01  0  31860 639696   4356 2167384    0    0     0     0 1034   739  0 
0 100  01  0  31860 643416   4356 2167384    0    0     0     0 1018   677  0 
0 99  01  0  31860 643416   4356 2167384    0    0     0     5 1012   649  0 
0 100  01  0  31860 643416   4356 2167384    0    0     0     0 1013   655  0 
0 100  01  0  31860 643416   4356 2167384    0    0     0    41 1017   637  0 
0 100  01  0  31860 643416   4356 2167384    0    0     0     0 1023   724  0 
0 100  02  0  31860 643284   4364 2167376    0    0     0    44 1023   698  2 
1 96  11  0  31860 643408   4364 2167376    0    0     0     0 1014   643  7 
0 93  01  0  31860 643408   4364 2167376    0    0     0    26 1046   741  1 
0 99  0
The query:
SELECT     "MS"."sMatterNo",     CAST(COUNT(*) AS int) AS "count" FROM  "MatterSearch" "MS"  JOIN "MatterDateStat" "S"
ON   (     "S"."matterNo" = "MS"."sMatterNo" AND      "S"."isOnHold" = FALSE  ) WHERE (  "MS"."matterStatusCode" IN
('OP','RO') ) GROUP BY "MS"."sMatterNo"
 
;
The pg_dump tarball is 20MB.  Should I email it directly to you?
-Kevin