Re: Running out of memory while making a join

[Craig Ringer <craig@xxxxxxxxxxxxxxx>]

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