Search Postgresql Archives

Out of memory on SELECT (from sort?) in 8.3

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

 



I have a SELECT query that causes an out-of-memory error on my production
Postgres 8.3 server. I believe the culprit is from the sort in the query,
but its running out on queries that return 300,000 results which I didn't
think was very large.

work_mem is set to 128MB, but I thought the server would change to an
on-disk sort method if it couldn't perform the sort in memory. Is this not
the case?

In the log file, I see the following information:

2008-08-16 02:02:43 CDT lms_nna ERROR:  out of memory
2008-08-16 02:02:43 CDT lms_nna DETAIL:  Failed on request of size 16384.
TopMemoryContext: 105376 total in 12 blocks; 9288 free (10 chunks); 96088
used
  LibxmlContext: 8380416 total in 10 blocks; 3828016 free (0 chunks);
4552400 used
  TopTransactionContext: 8192 total in 1 blocks; 7648 free (0 chunks); 544
used
  PL/PgSQL function context: 8192 total in 1 blocks; 5512 free (4 chunks);
2680 used
  Type information cache: 24576 total in 2 blocks; 11888 free (5 chunks);
12688 used
  PL/PgSQL function context: 8192 total in 1 blocks; 4648 free (7 chunks);
3544 used
  CFuncHash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used
  Rendezvous variable hash: 8192 total in 1 blocks; 1680 free (0 chunks);
6512 used
  PLpgSQL function cache: 24224 total in 2 blocks; 3744 free (0 chunks);
20480 used
  Record information cache: 24576 total in 2 blocks; 15984 free (5
chunks); 8592 used
  Prepared Queries: 24576 total in 2 blocks; 15984 free (5 chunks); 8592 used
  Operator class cache: 8192 total in 1 blocks; 1680 free (0 chunks); 6512
used
  Operator lookup cache: 24576 total in 2 blocks; 11888 free (5 chunks);
12688 used
  MessageContext: 8192 total in 1 blocks; 6976 free (1 chunks); 1216 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; 7616 free (0 chunks); 576 used
    PortalHeapMemory: 2048 total in 1 blocks; 424 free (0 chunks); 1624 used
      ExecutorState: 841031232 total in 51159 blocks; 1712232 free (56
chunks); 839319000 used
        TIDBitmap: 2088960 total in 8 blocks; 243200 free (25 chunks);
1845760 used
        TupleSort: 28303408 total in 13 blocks; 5346944 free (14 chunks);
22956464 used
        ExprContext: 8192 total in 1 blocks; 8096 free (0 chunks); 96 used
        ExprContext: 8192 total in 1 blocks; 8096 free (0 chunks); 96 used
        ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
    PortalHeapMemory: 1024 total in 1 blocks; 944 free (0 chunks); 80 used
  Relcache by OID: 24576 total in 2 blocks; 13872 free (3 chunks); 10704 used
  CacheMemoryContext: 817392 total in 20 blocks; 23488 free (1 chunks);
793904 used
    CachedPlan: 15360 total in 4 blocks; 1528 free (0 chunks); 13832 used
    CachedPlanSource: 15360 total in 4 blocks; 1440 free (0 chunks); 13920
used
    unnamed prepared statement: 8192 total in 1 blocks; 3856 free (2
chunks); 4336 used
    CachedPlan: 1024 total in 1 blocks; 312 free (0 chunks); 712 used
    CachedPlanSource: 1024 total in 1 blocks; 56 free (0 chunks); 968 used
    SPI Plan: 1024 total in 1 blocks; 832 free (0 chunks); 192 used
    CachedPlan: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
    CachedPlanSource: 1024 total in 1 blocks; 80 free (0 chunks); 944 used
    SPI Plan: 1024 total in 1 blocks; 808 free (0 chunks); 216 used
    CachedPlan: 3072 total in 2 blocks; 488 free (0 chunks); 2584 used
    CachedPlanSource: 1024 total in 1 blocks; 24 free (0 chunks); 1000 used
    SPI Plan: 1024 total in 1 blocks; 808 free (0 chunks); 216 used
    CachedPlan: 1024 total in 1 blocks; 816 free (0 chunks); 208 used
    CachedPlanSource: 1024 total in 1 blocks; 656 free (0 chunks); 368 used
    pg_toast_18005_index: 2048 total in 1 blocks; 608 free (0 chunks);
1440 used
    reporting_modified_idx: 2048 total in 1 blocks; 704 free (0 chunks);
1344 used
    reporting_last_processed_date_idx: 2048 total in 1 blocks; 752 free (0
chunks); 1296 used
    reporting_date_idx: 2048 total in 1 blocks; 704 free (0 chunks); 1344
used
    lead_created_idx: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
    lead_processing_step_idx: 2048 total in 1 blocks; 752 free (0 chunks);
1296 used
    lead_destination_idx: 2048 total in 1 blocks; 752 free (0 chunks);
1296 used
    lead_modified_idx: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
    lead_source_idx: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
    processing_state_idx: 2048 total in 1 blocks; 752 free (0 chunks);
1296 used
    lead_pkey: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
    CachedPlan: 1024 total in 1 blocks; 816 free (0 chunks); 208 used
    CachedPlanSource: 1024 total in 1 blocks; 664 free (1 chunks); 360 used
    CachedPlan: 1024 total in 1 blocks; 312 free (0 chunks); 712 used
    CachedPlanSource: 1024 total in 1 blocks; 56 free (0 chunks); 968 used
    SPI Plan: 1024 total in 1 blocks; 832 free (0 chunks); 192 used
    locks_pkey: 2048 total in 1 blocks; 704 free (0 chunks); 1344 used
    CachedPlan: 3072 total in 2 blocks; 648 free (0 chunks); 2424 used
    CachedPlanSource: 3072 total in 2 blocks; 1248 free (1 chunks); 1824 used
    SPI Plan: 1024 total in 1 blocks; 808 free (0 chunks); 216 used
    CachedPlan: 1024 total in 1 blocks; 816 free (0 chunks); 208 used
    CachedPlanSource: 1024 total in 1 blocks; 664 free (1 chunks); 360 used
    xslt_style_sheet_pkey: 2048 total in 1 blocks; 704 free (0 chunks);
1344 used
    external_system_user_name_key: 2048 total in 1 blocks; 752 free (0
chunks); 1296 used
    external_system_name_key: 2048 total in 1 blocks; 752 free (0 chunks);
1296 used
    external_system_pkey: 2048 total in 1 blocks; 752 free (0 chunks);
1296 used
    dealer_external_system_id_idx: 2048 total in 1 blocks; 752 free (0
chunks); 1296 used
    dealer_dealer_code_key: 2048 total in 1 blocks; 704 free (0 chunks);
1344 used
    dealer_pkey: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
    pg_index_indrelid_index: 2048 total in 1 blocks; 704 free (0 chunks);
1344 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; 1576 free (3
chunks); 1496 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; 1696 free (2 chunks);
1376 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; 1576 free (3
chunks); 1496 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; 1696 free (2 chunks);
1376 used
    pg_type_typname_nsp_index: 3072 total in 2 blocks; 1600 free (2
chunks); 1472 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; 7104 free (0 chunks); 1088 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 (0 chunks); 32 used




[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