Search Postgresql Archives

Re: Running out of memory while making a join

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@00002-xxx-SGDB ~]#




On Sat, Nov 10, 2012 at 11:01 PM, Craig Ringer <craig@xxxxxxxxxxxxxxx> 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.reimer@xxxxxxxxxxxxx


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux