Re: Running out of memory while making a join

Hi,

That is what I got from gdb:

TopMemoryContext: 88992 total in 10 blocks; 10336 free (7 chunks); 78656
used
Type information cache: 24576 total in 2 blocks; 11888 free (5 chunks);
12688 used
Operator lookup cache: 24576 total in 2 blocks; 11888 free (5 chunks);
12688 used
Operator class cache: 8192 total in 1 blocks; 1680 free (0 chunks); 6512
used
TopTransactionContext: 8192 total in 1 blocks; 7648 free (1 chunks); 544
used
MessageContext: 73728 total in 4 blocks; 14752 free (3 chunks); 58976 used
smgr relation table: 24576 total in 2 blocks; 13904 free (4 chunks);
10672 used
TransactionAbortContext: 32768 total in 1 blocks; 32736 free (0 chunks);
32 used
Portal hash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used
PortalMemory: 8192 total in 1 blocks; 7888 free (0 chunks); 304 used
PortalHeapMemory: 1024 total in 1 blocks; 744 free (0 chunks); 280 used
ExecutorState: 11586756656 total in 1391 blocks; 4938408 free (6
chunks); 11581818248 used
TIDBitmap: 57344 total in 3 blocks; 23936 free (8 chunks); 33408
used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 8192 total in 1 blocks; 8016 free (0 chunks); 176 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
Relcache by OID: 24576 total in 2 blocks; 14912 free (3 chunks); 9664 used
CacheMemoryContext: 817392 total in 20 blocks; 145728 free (0 chunks);
671664 used
uni_codauten: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
pk_nfe: 2048 total in 1 blocks; 632 free (0 chunks); 1416 used
idx_wm_nfsp_dtemis: 2048 total in 1 blocks; 752 free (0 chunks); 1296
used
idx_wm_nfsp_codpre: 2048 total in 1 blocks; 704 free (0 chunks); 1344
used
wm_nfsp_pkey: 2048 total in 1 blocks; 440 free (0 chunks); 1608 used
pg_index_indrelid_index: 2048 total in 1 blocks; 704 free (0 chunks);
1344 used
pg_attrdef_adrelid_adnum_index: 2048 total in 1 blocks; 608 free (0
chunks); 1440 used
pg_ts_dict_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks);
1328 used
pg_aggregate_fnoid_index: 3072 total in 2 blocks; 1744 free (3 chunks);
1328 used
pg_language_name_index: 3072 total in 2 blocks; 1744 free (3 chunks);
1328 used
pg_statistic_relid_att_index: 3072 total in 2 blocks; 1600 free (2
chunks); 1472 used
pg_ts_dict_dictname_index: 3072 total in 2 blocks; 1648 free (2
chunks); 1424 used
pg_namespace_nspname_index: 3072 total in 2 blocks; 1696 free (2
chunks); 1376 used
pg_opfamily_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks);
1328 used
pg_opclass_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks);
1376 used
pg_ts_parser_prsname_index: 3072 total in 2 blocks; 1648 free (2
chunks); 1424 used
pg_amop_fam_strat_index: 3072 total in 2 blocks; 1384 free (2 chunks);
1688 used
pg_opclass_am_name_nsp_index: 3072 total in 2 blocks; 1624 free (3
chunks); 1448 used
pg_trigger_tgrelid_tgname_index: 3072 total in 2 blocks; 1600 free (2
chunks); 1472 used
pg_cast_source_target_index: 3072 total in 2 blocks; 1600 free (2
chunks); 1472 used
pg_auth_members_role_member_index: 3072 total in 2 blocks; 1648 free (2
chunks); 1424 used
pg_attribute_relid_attnum_index: 3072 total in 2 blocks; 1600 free (2
chunks); 1472 used
pg_ts_config_cfgname_index: 3072 total in 2 blocks; 1648 free (2
chunks); 1424 used
pg_authid_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376
used
pg_ts_config_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks);
1328 used
pg_conversion_default_index: 3072 total in 2 blocks; 1432 free (3
chunks); 1640 used
pg_language_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks);
1328 used
pg_enum_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328
used
pg_proc_proname_args_nsp_index: 3072 total in 2 blocks; 1624 free (3
chunks); 1448 used
pg_ts_parser_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks);
1328 used
pg_database_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks);
1376 used
pg_conversion_name_nsp_index: 3072 total in 2 blocks; 1648 free (2
chunks); 1424 used
pg_class_relname_nsp_index: 3072 total in 2 blocks; 1600 free (2
chunks); 1472 used
pg_attribute_relid_attnam_index: 3072 total in 2 blocks; 1648 free (2
chunks); 1424 used
pg_class_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376
used
pg_amproc_fam_proc_index: 3072 total in 2 blocks; 1384 free (2 chunks);
1688 used
pg_operator_oprname_l_r_n_index: 3072 total in 2 blocks; 1384 free (2
chunks); 1688 used
pg_index_indexrelid_index: 3072 total in 2 blocks; 1696 free (2
chunks); 1376 used
pg_type_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376
used
pg_rewrite_rel_rulename_index: 3072 total in 2 blocks; 1648 free (2
chunks); 1424 used
pg_authid_rolname_index: 3072 total in 2 blocks; 1696 free (2 chunks);
1376 used
pg_auth_members_member_role_index: 3072 total in 2 blocks; 1648 free (2
chunks); 1424 used
pg_enum_typid_label_index: 3072 total in 2 blocks; 1648 free (2
chunks); 1424 used
pg_constraint_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks);
1328 used
pg_conversion_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks);
1328 used
pg_ts_template_tmplname_index: 3072 total in 2 blocks; 1648 free (2
chunks); 1424 used
pg_ts_config_map_index: 3072 total in 2 blocks; 1624 free (3 chunks);
1448 used
pg_namespace_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks);
1328 used
pg_type_typname_nsp_index: 3072 total in 2 blocks; 1648 free (2
chunks); 1424 used
pg_operator_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks);
1376 used
pg_amop_opr_fam_index: 3072 total in 2 blocks; 1600 free (2 chunks);
1472 used
pg_proc_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376
used
pg_opfamily_am_name_nsp_index: 3072 total in 2 blocks; 1624 free (3
chunks); 1448 used
pg_ts_template_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks);
1328 used
MdSmgr: 8192 total in 1 blocks; 7520 free (0 chunks); 672 used
LOCALLOCK hash: 24576 total in 2 blocks; 15984 free (5 chunks); 8592 used
Timezones: 53584 total in 2 blocks; 3744 free (0 chunks); 49840 used
ErrorContext: 8192 total in 1 blocks; 8160 free (3 chunks); 32 used

There is NO WARRANTY, to the extent permitted by law. Type "show copying"
and "show warranty" for details.
This GDB was configured as "x86_64-redhat-linux-gnu".
For bug reporting instructions, please see:
<http://www.gnu.org/software/gdb/bugs/>.
Attaching to process 31446
Reading symbols from /usr/local/pgsql/bin/postgres...(no debugging symbols
found)...done.
Reading symbols from /lib64/libcrypt.so.1...(no debugging symbols
found)...done.
Loaded symbols for /lib64/libcrypt.so.1
Reading symbols from /lib64/libdl.so.2...(no debugging symbols
found)...done.
Loaded symbols for /lib64/libdl.so.2
Reading symbols from /lib64/libm.so.6...(no debugging symbols found)...done.
Loaded symbols for /lib64/libm.so.6
Reading symbols from /lib64/libc.so.6...(no debugging symbols found)...done.
Loaded symbols for /lib64/libc.so.6
Reading symbols from /lib64/libfreebl3.so...(no debugging symbols
found)...done.
Loaded symbols for /lib64/libfreebl3.so
Reading symbols from /lib64/ld-linux-x86-64.so.2...(no debugging symbols
found)...done.
Loaded symbols for /lib64/ld-linux-x86-64.so.2
Reading symbols from /lib64/libnss_files.so.2...(no debugging symbols
found)...done.
Loaded symbols for /lib64/libnss_files.so.2
0x000000395d6e83e2 in recv () from /lib64/libc.so.6
Missing separate debuginfos, use: debuginfo-install
glibc-2.12-1.80.el6.x86_64 nss-softokn-freebl-3.12.9-11.el6.x86_64
(gdb) cont
Continuing.
bt
^C
Program received signal SIGINT, Interrupt.
0x00000000005ff177 in record_out ()
(gdb) bt
#0 0x00000000005ff177 in record_out ()
#1 0x000000000065b73f in FunctionCall1 ()
#2 0x000000000065c61b in OutputFunctionCall ()
#3 0x00000000004495cc in printtup ()
#4 0x000000000051e6a3 in ExecutorRun ()
#5 0x00000000005b658a in PortalRunSelect ()
#6 0x00000000005b7af9 in PortalRun ()
#7 0x00000000005b2ddf in exec_simple_query ()
#8 0x00000000005b4423 in PostgresMain ()
#9 0x00000000005881e4 in ServerLoop ()
#10 0x000000000058a1e5 in PostmasterMain ()
#11 0x0000000000540cf8 in main ()
(gdb) ^C(gdb) Quit
(gdb) bt
#0 0x00000000005ff177 in record_out ()
#1 0x000000000065b73f in FunctionCall1 ()
#2 0x000000000065c61b in OutputFunctionCall ()
#3 0x00000000004495cc in printtup ()
#4 0x000000000051e6a3 in ExecutorRun ()
#5 0x00000000005b658a in PortalRunSelect ()
#6 0x00000000005b7af9 in PortalRun ()
#7 0x00000000005b2ddf in exec_simple_query ()
#8 0x00000000005b4423 in PostgresMain ()
#9 0x00000000005881e4 in ServerLoop ()
#10 0x000000000058a1e5 in PostmasterMain ()
#11 0x0000000000540cf8 in main ()
(gdb) ^C(gdb) Quit
(gdb) bt
#0 0x00000000005ff177 in record_out ()
#1 0x000000000065b73f in FunctionCall1 ()
#2 0x000000000065c61b in OutputFunctionCall ()
#3 0x00000000004495cc in printtup ()
#4 0x000000000051e6a3 in ExecutorRun ()
#5 0x00000000005b658a in PortalRunSelect ()
#6 0x00000000005b7af9 in PortalRun ()
#7 0x00000000005b2ddf in exec_simple_query ()
#8 0x00000000005b4423 in PostgresMain ()
#9 0x00000000005881e4 in ServerLoop ()
#10 0x000000000058a1e5 in PostmasterMain ()
#11 0x0000000000540cf8 in main ()
(gdb) cont
Continuing.
^C
Program received signal SIGINT, Interrupt.
0x000000395d688ae9 in memcpy () from /lib64/libc.so.6
(gdb) bt
#0 0x000000395d688ae9 in memcpy () from /lib64/libc.so.6
#1 0x000000000046314f in heap_tuple_untoast_attr ()
#2 0x00000000005fb2ef in numeric_out ()
#3 0x000000000065b73f in FunctionCall1 ()
#4 0x000000000065c61b in OutputFunctionCall ()
#5 0x00000000005ff121 in record_out ()
#6 0x000000000065b73f in FunctionCall1 ()
#7 0x000000000065c61b in OutputFunctionCall ()
#8 0x00000000004495cc in printtup ()
#9 0x000000000051e6a3 in ExecutorRun ()
#10 0x00000000005b658a in PortalRunSelect ()
#11 0x00000000005b7af9 in PortalRun ()
#12 0x00000000005b2ddf in exec_simple_query ()
#13 0x00000000005b4423 in PostgresMain ()
#14 0x00000000005881e4 in ServerLoop ()
#15 0x000000000058a1e5 in PostmasterMain ()
#16 0x0000000000540cf8 in main ()
(gdb) cont
Continuing.
^C
Program received signal SIGINT, Interrupt.
0x00000000005395d0 in appendStringInfoChar ()
(gdb) bt
#0 0x00000000005395d0 in appendStringInfoChar ()
#1 0x00000000005ff1e6 in record_out ()
#2 0x000000000065b73f in FunctionCall1 ()
#3 0x000000000065c61b in OutputFunctionCall ()
#4 0x00000000004495cc in printtup ()
#5 0x000000000051e6a3 in ExecutorRun ()
#6 0x00000000005b658a in PortalRunSelect ()
#7 0x00000000005b7af9 in PortalRun ()
#8 0x00000000005b2ddf in exec_simple_query ()
#9 0x00000000005b4423 in PostgresMain ()
#10 0x00000000005881e4 in ServerLoop ()
#11 0x000000000058a1e5 in PostmasterMain ()
#12 0x0000000000540cf8 in main ()
(gdb) cont
Continuing.
^C
Program received signal SIGINT, Interrupt.
0x00000000005ed175 in int2out ()
(gdb) bt
#0 0x00000000005ed175 in int2out ()
#1 0x000000000065b73f in FunctionCall1 ()
#2 0x000000000065c61b in OutputFunctionCall ()
#3 0x00000000005ff121 in record_out ()
#4 0x000000000065b73f in FunctionCall1 ()
#5 0x000000000065c61b in OutputFunctionCall ()
#6 0x00000000004495cc in printtup ()
#7 0x000000000051e6a3 in ExecutorRun ()
#8 0x00000000005b658a in PortalRunSelect ()
#9 0x00000000005b7af9 in PortalRun ()
#10 0x00000000005b2ddf in exec_simple_query ()
#11 0x00000000005b4423 in PostgresMain ()
#12 0x00000000005881e4 in ServerLoop ()
#13 0x000000000058a1e5 in PostmasterMain ()
#14 0x0000000000540cf8 in main ()
(gdb) cont
Continuing.
^C
Program received signal SIGINT, Interrupt.
0x00000000005f5ab0 in get_str_from_var ()
(gdb) bt
#0 0x00000000005f5ab0 in get_str_from_var ()
#1 0x00000000005fb33b in numeric_out ()
#2 0x000000000065b73f in FunctionCall1 ()
#3 0x000000000065c61b in OutputFunctionCall ()
#4 0x00000000005ff121 in record_out ()
#5 0x000000000065b73f in FunctionCall1 ()
#6 0x000000000065c61b in OutputFunctionCall ()
#7 0x00000000004495cc in printtup ()
#8 0x000000000051e6a3 in ExecutorRun ()
#9 0x00000000005b658a in PortalRunSelect ()
#10 0x00000000005b7af9 in PortalRun ()
#11 0x00000000005b2ddf in exec_simple_query ()
#12 0x00000000005b4423 in PostgresMain ()
#13 0x00000000005881e4 in ServerLoop ()
#14 0x000000000058a1e5 in PostmasterMain ()
#15 0x0000000000540cf8 in main ()
(gdb) cont
Continuing.
^C
Program received signal SIGINT, Interrupt.
0x0000000000671990 in AllocSetAlloc ()
(gdb) bt
#0 0x0000000000671990 in AllocSetAlloc ()
#1 0x00000000005f5136 in alloc_var ()
#2 0x00000000005f5192 in set_var_from_num ()
#3 0x00000000005fb32f in numeric_out ()
#4 0x000000000065b73f in FunctionCall1 ()
#5 0x000000000065c61b in OutputFunctionCall ()
#6 0x00000000005ff121 in record_out ()
#7 0x000000000065b73f in FunctionCall1 ()
#8 0x000000000065c61b in OutputFunctionCall ()
#9 0x00000000004495cc in printtup ()
#10 0x000000000051e6a3 in ExecutorRun ()
#11 0x00000000005b658a in PortalRunSelect ()
#12 0x00000000005b7af9 in PortalRun ()
#13 0x00000000005b2ddf in exec_simple_query ()
#14 0x00000000005b4423 in PostgresMain ()
#15 0x00000000005881e4 in ServerLoop ()
#16 0x000000000058a1e5 in PostmasterMain ()
#17 0x0000000000540cf8 in main ()
(gdb) cont
Continuing.
^C
Program received signal SIGINT, Interrupt.
0x00000000006719a2 in AllocSetAlloc ()
(gdb) bt
#0 0x00000000006719a2 in AllocSetAlloc ()
#1 0x0000000000463136 in heap_tuple_untoast_attr ()
#2 0x00000000005fb2ef in numeric_out ()
#3 0x000000000065b73f in FunctionCall1 ()
#4 0x000000000065c61b in OutputFunctionCall ()
#5 0x00000000005ff121 in record_out ()
#6 0x000000000065b73f in FunctionCall1 ()
#7 0x000000000065c61b in OutputFunctionCall ()
#8 0x00000000004495cc in printtup ()
#9 0x000000000051e6a3 in ExecutorRun ()
#10 0x00000000005b658a in PortalRunSelect ()
#11 0x00000000005b7af9 in PortalRun ()
#12 0x00000000005b2ddf in exec_simple_query ()
#13 0x00000000005b4423 in PostgresMain ()
#14 0x00000000005881e4 in ServerLoop ()
#15 0x000000000058a1e5 in PostmasterMain ()
#16 0x0000000000540cf8 in main ()
(gdb) cont
Continuing.
^C
Program received signal SIGINT, Interrupt.
0x000000395d688ae9 in memcpy () from /lib64/libc.so.6
(gdb) bt
#0 0x000000395d688ae9 in memcpy () from /lib64/libc.so.6
#1 0x00000000006724b1 in MemoryContextStrdup ()
#2 0x00000000005d7069 in date_out ()
#3 0x000000000065b73f in FunctionCall1 ()
#4 0x000000000065c61b in OutputFunctionCall ()
#5 0x00000000005ff121 in record_out ()
#6 0x000000000065b73f in FunctionCall1 ()
#7 0x000000000065c61b in OutputFunctionCall ()
#8 0x00000000004495cc in printtup ()
#9 0x000000000051e6a3 in ExecutorRun ()
#10 0x00000000005b658a in PortalRunSelect ()
#11 0x00000000005b7af9 in PortalRun ()
#12 0x00000000005b2ddf in exec_simple_query ()
#13 0x00000000005b4423 in PostgresMain ()
#14 0x00000000005881e4 in ServerLoop ()
#15 0x000000000058a1e5 in PostmasterMain ()
#16 0x0000000000540cf8 in main ()
(gdb) cont
Continuing.
^C
Program received signal SIGINT, Interrupt.
0x00000000005395e6 in appendStringInfoChar ()
(gdb) call MemoryContextStats(TopMemoryContext)
$1 = 68
(gdb) cont
Continuing.

Program received signal SIGINT, Interrupt.
0x00000000005ff168 in record_out ()
(gdb) cont
Continuing.

Program received signal SIGINT, Interrupt.
0x00000000005ff168 in record_out ()
(gdb) ^C(gdb) Quit
(gdb) ^C(gdb) Quit
(gdb) quit
A debugging session is active.

Inferior 1 [process 31446] will be detached.

Quit anyway? (y or n) y
Detaching from program: /usr/local/pgsql/bin/postgres, process 31446
[root(at)00002-xxx-SGDB ~]#

On Sat, Nov 10, 2012 at 11:01 PM, Craig Ringer <craig(at)2ndquadrant(dot)com>wrote:

> On 11/11/2012 08:54 AM, Craig Ringer wrote:
>
>
> Now follow Tom's advice:
>
> In gdb,
> call MemoryContextStats(TopMemoryContext)
> should produce some useful information on the process's stderr file.
>
>
> Oh, I forgot to explain how to actually get the output.
>
> stderr goes to the PostgreSQL log files, so you need to look in the Pg
> logs. These are in /var/lib/pgsql/data/pg_log or
> /var/lib/pgsql/[version]/data/pg_log on Red Hat / Fedora systems, and in
> /var/log/postgresql/ on Debian/Ubuntu systems.
>
> You're looking for something like:
>
> MessageContext: 8192 total in 1 blocks; 7120 free (1 chunks); 1072 used
> Operator class cache: 8192 total in 1 blocks; 1680 free (0 chunks); 6512
> used
> smgr relation table: 24576 total in 2 blocks; 13920 free (4 chunks);
> 10656 used
> TransactionAbortContext: 32768 total in 1 blocks; 32736 free (0 chunks);
> 32 used
> Portal hash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used
> PortalMemory: 8192 total in 1 blocks; 8160 free (1 chunks); 32 used
> Relcache by OID: 24576 total in 2 blocks; 13872 free (3 chunks); 10704
> used
> CacheMemoryContext: 817840 total in 20 blocks; 140840 free (3 chunks);
> 677000 used
> pg_db_role_setting_databaseid_rol_index: 2048 total in 1 blocks; 528
> free (0 chunks); 1520 used
> pg_opclass_am_name_nsp_index: 3072 total in 2 blocks; 1272 free (2
> chunks); 1800 used
> ... lots more ...
> pg_database_oid_index: 3072 total in 2 blocks; 1656 free (3
> chunks); 1416 used
> pg_authid_rolname_index: 3072 total in 2 blocks; 1656 free (3 chunks);
> 1416 used
> MdSmgr: 8192 total in 1 blocks; 8128 free (0 chunks); 64 used
> tokenize file cxt: 0 total in 0 blocks; 0 free (0 chunks); 0 used
> hba parser context: 7168 total in 3 blocks; 3760 free (4 chunks); 3408
> used
> LOCALLOCK hash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used
> Timezones: 83472 total in 2 blocks; 3744 free (0 chunks); 79728 used
> ErrorContext: 8192 total in 1 blocks; 8160 free (3 chunks); 32 used
>
> Copy and paste the entire block (including the bits I omitted from mine
> because it's just an example to show you what it looks like).
>
> --
> Craig Ringer
>

--
Reimer
47-3347-1724 47-9183-0547 msn: carlos(dot)reimer(at)opendb(dot)com(dot)br