Обсуждение: TopMemoryContext - Configuration Mistake?
I got the following error during a select query.<br /><br /> May it be a config error, or is it a bug?<br /><br /> TopMemoryContext:186872 total in 15 blocks; 16544 free (41 chunks); 170328 used<br /> TopTransactionContext: 8192 totalin 1 blocks; 7568 free (0 chunks); 624 used<br /> Type information cache: 24576 total in 2 blocks; 11888 free (5 chunks);12688 used<br /> Operator class cache: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used<br /> Operatorlookup cache: 24576 total in 2 blocks; 11888 free (5 chunks); 12688 used<br /> MessageContext: 2042626048 totalin 256 blocks; 16072 free (7 chunks); 2042609976 used<br /> JoinRelHashTable: 1040384 total in 7 blocks; 512384free (12 chunks); 528000 used<br /> smgr relation table: 24576 total in 2 blocks; 9776 free (4 chunks); 14800 used<br/> TransactionAbortContext: 32768 total in 1 blocks; 32736 free (0 chunks); 32 used<br /> Portal hash: 8192 totalin 1 blocks; 1680 free (0 chunks); 6512 used<br /> PortalMemory: 8192 total in 1 blocks; 8160 free (1 chunks); 32used<br /> Relcache by OID: 24576 total in 2 blocks; 12832 free (3 chunks); 11744 used<br /> CacheMemoryContext: 2549344total in 23 blocks; 986984 free (1 chunks); 1562360 used<br /> gen_porti_sched_pkey: 2048 total in 1 blocks; 656free (0 chunks); 1392 used<br /> m_zondes_pkey: 2048 total in 1 blocks; 704 free (0 chunks); 1344 used<br /> oevi_1:2048 total in 1 blocks; 752 free (0 chunks); 1296 used<br /> oe_vessel_imbarco_pkey: 2048 total in 1 blocks; 632free (0 chunks); 1416 used<br /> m_zonmar_pkey: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used<br /> m_tipmer_pkey:2048 total in 1 blocks; 752 free (0 chunks); 1296 used<br /> m_cianav_pkey: 2048 total in 1 blocks; 656free (0 chunks); 1392 used<br /> oev_1: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used<br /> oe_vessel_t_pkey:2048 total in 1 blocks; 656 free (0 chunks); 1392 used<br /> mmerca_cod_emb: 2048 total in 1 blocks;752 free (0 chunks); 1296 used<br /> m_merca_pkey: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used<br /> m_aeropu_pkey: 2048 total in 1 blocks; 704 free (0 chunks); 1344 used<br /> mcli_nome: 2048 total in 1 blocks;752 free (0 chunks); 1296 used<br /> mcli_acro: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used<br /> <m_cli_x1>: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used<br /> m_cli_pkey: 2048 total in 1 blocks;752 free (0 chunks); 1296 used<br /> oec_2: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used<br /> oec_1:2048 total in 1 blocks; 752 free (0 chunks); 1296 used<br /> oe_container_booking_nr_progr_ctnr_azienda_key: 2048total in 1 blocks; 632 free (0 chunks); 1416 used<br /> oe_container_pkey: 2048 total in 1 blocks; 632 free (0 chunks);1416 used<br /> oem_x1: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used<br /> oe_sped_m_pkey: 2048total in 1 blocks; 632 free (0 chunks); 1416 used<br /> ref_sales_pkey: 2048 total in 1 blocks; 440 free (0 chunks);1608 used<br /> oes_x7: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used<br /> oes_x6: 2048 total in1 blocks; 752 free (0 chunks); 1296 used<br /> oes_x5: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used<br /> oes_x4: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used<br /> oes_x3: 2048 total in 1 blocks; 752 free (0chunks); 1296 used<br /> oes_x2: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used<br /> oes_x1: 2048 totalin 1 blocks; 752 free (0 chunks); 1296 used<br /> oe_sped_t_pkey: 2048 total in 1 blocks; 656 free (0 chunks); 1392used<br /> navig_save_pkey: 2048 total in 1 blocks; 656 free (0 chunks); 1392 used<br /> navig_fields_pkey: 2048total in 1 blocks; 632 free (0 chunks); 1416 used<br /> navig_subarea_pkey: 2048 total in 1 blocks; 656 free (0 chunks);1392 used<br /> navig_area_pkey: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used<br /> navig_left_table_pkey:2048 total in 1 blocks; 752 free (0 chunks); 1296 used<br /> gnp_cod_tipo_par: 2048 total in 1blocks; 608 free (0 chunks); 1440 used<br /> gen_param_pkey: 2048 total in 1 blocks; 632 free (0 chunks); 1416 used<br/> glchart_groups_pk_gr: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used<br /> pg_attrdef_oid_index:2048 total in 1 blocks; 752 free (0 chunks); 1296 used<br /> empresa_pkey: 2048 total in 1 blocks;704 free (0 chunks); 1344 used<br /> pg_attrdef_adrelid_adnum_index: 2048 total in 1 blocks; 608 free (0 chunks);1440 used<br /> pg_index_indrelid_index: 2048 total in 1 blocks; 704 free (0 chunks); 1344 used<br /> pg_ts_dict_oid_index:3072 total in 2 blocks; 1744 free (3 chunks); 1328 used<br /> pg_aggregate_fnoid_index: 3072 totalin 2 blocks; 1696 free (2 chunks); 1376 used<br /> pg_language_name_index: 3072 total in 2 blocks; 1744 free (3chunks); 1328 used<br /> pg_statistic_relid_att_index: 3072 total in 2 blocks; 1600 free (2 chunks); 1472 used<br /> pg_ts_dict_dictname_index: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 used<br /> pg_namespace_nspname_index:3072 total in 2 blocks; 1696 free (2 chunks); 1376 used<br /> pg_opfamily_oid_index: 3072total in 2 blocks; 1744 free (3 chunks); 1328 used<br /> pg_opclass_oid_index: 3072 total in 2 blocks; 1696 free(2 chunks); 1376 used<br /> pg_ts_parser_prsname_index: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 used<br/> pg_amop_fam_strat_index: 3072 total in 2 blocks; 1384 free (2 chunks); 1688 used<br /> pg_opclass_am_name_nsp_index:3072 total in 2 blocks; 1576 free (3 chunks); 1496 used<br /> pg_trigger_tgrelid_tgname_index:3072 total in 2 blocks; 1600 free (2 chunks); 1472 used<br /> pg_cast_source_target_index:3072 total in 2 blocks; 1600 free (2 chunks); 1472 used<br /> pg_auth_members_role_member_index:3072 total in 2 blocks; 1648 free (2 chunks); 1424 used<br /> pg_attribute_relid_attnum_index:3072 total in 2 blocks; 1600 free (2 chunks); 1472 used<br /> pg_ts_config_cfgname_index:3072 total in 2 blocks; 1648 free (2 chunks); 1424 used<br /> pg_authid_oid_index: 3072 totalin 2 blocks; 1696 free (2 chunks); 1376 used<br /> pg_ts_config_oid_index: 3072 total in 2 blocks; 1744 free (3chunks); 1328 used<br /> pg_conversion_default_index: 3072 total in 2 blocks; 1432 free (3 chunks); 1640 used<br /> pg_language_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used<br /> pg_enum_oid_index: 3072 totalin 2 blocks; 1744 free (3 chunks); 1328 used<br /> pg_proc_proname_args_nsp_index: 3072 total in 2 blocks; 1576free (3 chunks); 1496 used<br /> pg_ts_parser_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used<br/> pg_database_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376 used<br /> pg_conversion_name_nsp_index:3072 total in 2 blocks; 1648 free (2 chunks); 1424 used<br /> pg_class_relname_nsp_index:3072 total in 2 blocks; 1600 free (2 chunks); 1472 used<br /> pg_attribute_relid_attnam_index:3072 total in 2 blocks; 1648 free (2 chunks); 1424 used<br /> pg_class_oid_index: 3072total in 2 blocks; 1696 free (2 chunks); 1376 used<br /> pg_amproc_fam_proc_index: 3072 total in 2 blocks; 1384 free(2 chunks); 1688 used<br /> pg_operator_oprname_l_r_n_index: 3072 total in 2 blocks; 1384 free (2 chunks); 1688 used<br/> pg_index_indexrelid_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376 used<br /> pg_type_oid_index:3072 total in 2 blocks; 1696 free (2 chunks); 1376 used<br /> pg_rewrite_rel_rulename_index: 3072 totalin 2 blocks; 1648 free (2 chunks); 1424 used<br /> pg_authid_rolname_index: 3072 total in 2 blocks; 1696 free (2chunks); 1376 used<br /> pg_auth_members_member_role_index: 3072 total in 2 blocks; 1600 free (2 chunks); 1472 used<br/> pg_enum_typid_label_index: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 used<br /> pg_constraint_oid_index:3072 total in 2 blocks; 1744 free (3 chunks); 1328 used<br /> pg_conversion_oid_index: 3072 totalin 2 blocks; 1744 free (3 chunks); 1328 used<br /> pg_ts_template_tmplname_index: 3072 total in 2 blocks; 1648 free(2 chunks); 1424 used<br /> pg_ts_config_map_index: 3072 total in 2 blocks; 1624 free (3 chunks); 1448 used<br /> pg_namespace_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376 used<br /> pg_type_typname_nsp_index:3072 total in 2 blocks; 1600 free (2 chunks); 1472 used<br /> pg_operator_oid_index: 3072 totalin 2 blocks; 1696 free (2 chunks); 1376 used<br /> pg_amop_opr_fam_index: 3072 total in 2 blocks; 1600 free (2 chunks);1472 used<br /> pg_proc_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376 used<br /> pg_opfamily_am_name_nsp_index:3072 total in 2 blocks; 1624 free (3 chunks); 1448 used<br /> pg_ts_template_oid_index:3072 total in 2 blocks; 1744 free (3 chunks); 1328 used<br /> MdSmgr: 8192 total in 1 blocks;5664 free (0 chunks); 2528 used<br /> LOCALLOCK hash: 24576 total in 2 blocks; 15984 free (5 chunks); 8592 used<br/> Timezones: 53584 total in 2 blocks; 3744 free (0 chunks); 49840 used<br /> ErrorContext: 24576 total in 3 blocks;24480 free (18 chunks); 96 used<br /> 2010-08-03 10:48:03 CEST [31005]: [658-1] ERROR: out of memory<br /><br /><divclass="moz-signature">-- <br /></div><pre class="moz-signature" cols="72"><font face="Verdana"><b><font color="#000000">EdoardoInnocenti </font></b><small><font color="#000000">Infrastructure Coordinator</font></small> <font color="#000000"> <b>SDB Information Technology</b> <small>Phone: +39.055.3811222 Fax: +39.055.5201411</small></font></font></pre><br /><div style="text-align: center; width: 620px; font-family: Verdana;"><small>Utilizziamo i dati personali che la riguardano esclusivamente per nostre finalità amministrative e contabili,anche quando li comunichiamo a terzi. Informazioni dettagliate, anche in ordine al Suo diritto di accesso e aglialtri Suoi diritti, sono riportate alla pagina <a href="http://www.savinodelbene.com/news/privacy.html">http://www.savinodelbene.com/news/privacy.html</a><br/> Se avete ricevutoquesto messaggio per errore Vi preghiamo di ritornarlo al mittente eliminandolo assieme agli eventuali allegati,ai sensi art. 616 codice penale <a href="http://www.savinodelbene.com/codice_penale_616.html">http://www.savinodelbene.com/codice_penale_616.html</a><br/> L'Aziendanon si assume alcuna responsabilità giuridica qualora pervengano da questo indirizzo messaggi estranei all'attivitàlavorativa o contrari a norme. </small></div><br />
Edoardo Innocenti <edoardo.innocenti@savinodelbene.com> writes:
> I got the following error during a select query.<br>
What was the query, what does EXPLAIN show as the plan for it,
and which PG version is your server exactly?
> � MessageContext: 2042626048 total in 256 blocks; 16072 free (7
> chunks); 2042609976 used<br>
This looks like it could be a memory leak, but we'd need to be able
to reproduce the problem in order to investigate or fix it.
regards, tom lane
Il 16/08/2010 17:59, Tom Lane ha scritto: <blockquote cite="mid:5923.1281974354@sss.pgh.pa.us" type="cite"><pre wrap="">EdoardoInnocenti <a class="moz-txt-link-rfc2396E" href="mailto:edoardo.innocenti@savinodelbene.com"><edoardo.innocenti@savinodelbene.com></a>writes: </pre><blockquotetype="cite"><pre wrap="">I got the following error during a select query.<br> </pre></blockquote><prewrap=""> What was the query, what does EXPLAIN show as the plan for it, and which PG version is your server exactly? </pre></blockquote> The database running on a CenOS 5.1 (Linux xxxxxxx 2.6.18-53.el5 #1 SMP Mon Nov 12 02:14:55 EST 2007x86_64 x86_64 x86_64 GNU/Linux ), PostgreSQL version is 8.3.8 (postgresql-8.3.8-1PGDG.rhel5, postgresql-contrib-8.3.8-1PGDG.rhel5,postgresql-libs-8.3.8-1PGDG.rhel5, compat-postgresql-libs-4-1PGDG.rhel5, postgresql-server-8.3.8-1PGDG.rhel5)<br/><br /> Query Plan:<br /> "HashAggregate (cost=4910.06..4910.19 rows=1 width=659)"<br /> " -> Nested Loop (cost=84.49..4909.99 rows=1 width=659)" <br /> " -> Nested Loop (cost=84.49..4901.70rows=1 width=655)" <br /> " Join Filter: (aer_d.zon_mar = zmar.codigo)" <br /> " -> Nested Loop (cost=84.49..4900.03rows=1 width=601)" <br /> " Join Filter: (merca.grupo = tipmer.codigo)" <br /> " -> Nested Loop (cost=84.49..4898.56rows=1 width=574)" <br /> " -> Nested Loop (cost=84.49..4890.27 rows=1 width=530)" <br /> " Join Filter:(zmar4.codigo = aer_d.zon_mar)" <br /> " -> Nested Loop (cost=84.49..4888.60 rows=1 width=522)" <br /> " ->Nested Loop (cost=84.49..4880.32 rows=1 width=514)" <br /> " Join Filter: (oec.progr_ctnr = oem.progr_ctnr)" <br />" -> Nested Loop (cost=84.49..4807.00 rows=3 width=514)" <br /> " -> Nested Loop (cost=84.49..4782.15 rows=3 width=494)"<br /> " -> Nested Loop (cost=84.49..4757.31 rows=3 width=495)" <br /> " -> Nested Loop (cost=84.49..4732.46rows=3 width=451)" <br /> " Join Filter: (aer_l.zon_mar = zmar3.codigo)" <br /> " -> Nested Loop (cost=84.49..4727.44rows=3 width=459)" <br /> " -> Nested Loop (cost=84.49..4726.60 rows=3 width=389)" <br /> " ->Nested Loop (cost=84.49..4705.74 rows=3 width=337)" <br /> " -> Nested Loop (cost=84.49..4680.89 rows=3 width=293)"<br /> " -> Nested Loop (cost=84.49..4680.16 rows=1 width=249)" <br /> " -> Hash Join (cost=84.49..4676.45rows=1 width=249)" <br /> " Hash Cond: ((oes.aero_ori = oevi.port_loading) AND (oes.vessel_code = oevi.vessel_code))"<br /> " -> Hash Left Join (cost=76.20..4502.78 rows=22050 width=233)" <br /> " Hash Cond: ((oes.azienda= refs.azienda) AND (oes.hbl = refs.house) AND (oes.expediente = refs.reference))" <br /> " -> Hash LeftJoin (cost=38.10..3637.70 rows=22050 width=180)" <br /> " Hash Cond: ((oes.azienda = refs2.azienda) AND (oes.hbl = refs2.house)AND (oes.expediente = refs2.reference))" <br /> " -> Seq Scan on oe_sped_t oes (cost=0.00..2772.62 rows=22050width=119)" <br /> " Filter: (azienda = '60'::bpchar)" <br /> " -> Hash (cost=28.12..28.12 rows=570 width=92)"<br /> " -> Seq Scan on ref_sales refs2 (cost=0.00..28.12 rows=570 width=92)" <br /> " Filter: (azienda = '60'::bpchar)"<br /> " -> Hash (cost=28.12..28.12 rows=570 width=92)" <br /> " -> Seq Scan on ref_sales refs (cost=0.00..28.12rows=570 width=92)" <br /> " Filter: (azienda = '60'::bpchar)" <br /> " -> Hash (cost=8.27..8.27 rows=1width=20)" <br /> " -> Index Scan using oevi_1 on oe_vessel_imbarco oevi (cost=0.00..8.27 rows=1 width=20)" <br/> " Index Cond: ((departure >= '2010-07-01'::date) AND (departure <= '2010-07-31'::date))" <br /> " Filter: (azienda= '60'::bpchar)" <br /> " -> Index Scan using m_aeropu_pkey on m_aeropu aer_l (cost=0.00..3.70 rows=1 width=16)"<br /> " Index Cond: (aer_l.codigo = oes.aero_ori)" <br /> " -> Index Scan using oe_container_booking_nr_progr_ctnr_azienda_keyon oe_container oec (cost=0.00..0.67 rows=5 width=48)" <br /> " Index Cond:((oec.booking_nr = oes.booking_nr) AND (oec.azienda = '60'::bpchar))" <br /> " -> Index Scan using m_cli_pkey onm_cli fab (cost=0.00..8.27 rows=1 width=60)" <br /> " Index Cond: (fab.codigo = oes.cod_fab)" <br /> " -> Index Scanusing oe_vessel_t_pkey on oe_vessel_t oev (cost=0.00..6.94 rows=1 width=64)" <br /> " Index Cond: ((oev.vessel_code =oes.vessel_code) AND (oev.azienda = '60'::bpchar))" <br /> " -> Index Scan using m_zonmar_pkey on m_zonmar zmar2 (cost=0.00..0.27rows=1 width=70)" <br /> " Index Cond: (zmar2.codigo = aer_l.zon_mar)" <br /> " -> Seq Scan on m_zonmarzmar3 (cost=0.00..1.30 rows=30 width=8)" <br /> " -> Index Scan using m_cli_pkey on m_cli agent (cost=0.00..8.27rows=1 width=60)" <br /> " Index Cond: (agent.codigo = oes.agen_des)" <br /> " -> Index Scan using m_merca_pkeyon m_merca merca (cost=0.00..8.27 rows=1 width=11)" <br /> " Index Cond: (merca.codigo = oes.tip_mer)" <br />" -> Index Scan using m_cianav_pkey on m_cianav cia2 (cost=0.00..8.27 rows=1 width=24)" <br /> " Index Cond: ((cia2.codigo= oev.carrier) AND (cia2.azienda = '60'::bpchar))" <br /> " -> Index Scan using oe_sped_m_pkey on oe_sped_moem (cost=0.00..24.36 rows=5 width=20)" <br /> " Index Cond: ((oem.entry_nr = oes.entry_nr) AND (oem.azienda = '60'::bpchar))"<br /> " -> Index Scan using m_aeropu_pkey on m_aeropu aer_d (cost=0.00..8.27 rows=1 width=16)" <br />" Index Cond: (aer_d.codigo = oes.aero_des)" <br /> " -> Seq Scan on m_zonmar zmar4 (cost=0.00..1.30 rows=30 width=8)"<br /> " -> Index Scan using m_cli_pkey on m_cli cons (cost=0.00..8.27 rows=1 width=60)" <br /> " Index Cond:(cons.codigo = oes.cod_des)" <br /> " -> Seq Scan on m_tipmer tipmer (cost=0.00..1.21 rows=21 width=37)" <br /> "-> Seq Scan on m_zonmar zmar (cost=0.00..1.30 rows=30 width=70)" <br /> " -> Index Scan using m_cianav_pkey on m_cianavcia (cost=0.00..8.27 rows=1 width=48)" <br /> " Index Cond: ((cia.codigo = oev.carrier) AND (cia.azienda = '60'::bpchar))"<br/><br /><br /> This is db parameters:<br /><br /> name | setting <br /> ---------------------------------+---------------------------------------<br /> add_missing_from | off <br /> allow_system_table_mods | off <br /> archive_command | (disabled) <br/> archive_mode | off <br /> archive_timeout |0 <br /> N seconds.<br /> array_nulls | on <br /> authentication_timeout | 1min <br/> autovacuum | on <br /> autovacuum_analyze_scale_factor |0.1 <br /> eltuples.<br /> autovacuum_analyze_threshold | 50 <br /> autovacuum_freeze_max_age | 200000000 <br/> autovacuum_max_workers | 3 <br /> autovacuum_naptime |1min <br /> autovacuum_vacuum_cost_delay | 20ms <br/> autovacuum_vacuum_cost_limit | -1 <br /> autovacuum_vacuum_scale_factor |0.2 <br /> autovacuum_vacuum_threshold | 50 <br/> backslash_quote | safe_encoding <br /> bgwriter_delay |200ms <br /> bgwriter_lru_maxpages | 100 <br/> bgwriter_lru_multiplier | 2 <br /> block_size |8192 <br /> bonjour_name | <br/> check_function_bodies | on <br /> checkpoint_completion_target |0.5 <br /> interval.<br /> checkpoint_segments | 3 <br /> checkpoint_timeout | 5min <br/> checkpoint_warning | 30s <br /> client_encoding |UTF8 <br /> client_min_messages | notice <br/> commit_delay | 0 <br /> isk.<br /> commit_siblings | 5 <br /> config_file | /var/lib/pgsql/data/postgresql.conf <br /> constraint_exclusion | off <br/> cpu_index_tuple_cost | 0.005 <br /> n index scan.<br /> cpu_operator_cost | 0.0025 <br /> call.<br /> cpu_tuple_cost | 0.01 <br /> custom_variable_classes | <br /> data_directory | /var/lib/pgsql/data <br/> DateStyle | ISO, MDY <br /> db_user_namespace |off <br /> deadlock_timeout | 1s <br/> debug_assertions | off <br /> debug_pretty_print |off <br /> debug_print_parse | off <br/> debug_print_plan | off <br /> debug_print_rewritten |off <br /> default_statistics_target | 10 <br/> default_tablespace | <br /> default_text_search_config |pg_catalog.english <br /> default_transaction_isolation | read committed <br/> default_transaction_read_only | off <br /> default_with_oids |off <br /> dynamic_library_path | $libdir <br/> effective_cache_size | 4048MB <br /> enable_bitmapscan |on <br /> enable_hashagg | on <br/> enable_hashjoin | on <br /> enable_indexscan |on <br /> enable_mergejoin | on <br/> enable_nestloop | on <br /> enable_seqscan |on <br /> enable_sort | on <br/> enable_tidscan | on <br /> escape_string_warning |on <br /> explain_pretty_print | on <br/> external_pid_file | <br /> extra_float_digits |0 <br /> from_collapse_limit | 8 <br/> fsync | on <br /> full_page_writes |on <br /> geqo | on <br/> geqo_effort | 5 <br /> geqo_generations |0 <br /> geqo_pool_size | 0 <br/> geqo_selection_bias | 2 <br /> geqo_threshold |12 <br /> gin_fuzzy_search_limit | 0 <br/> hba_file | /var/lib/pgsql/data/pg_hba.conf <br /> ident_file |/var/lib/pgsql/data/pg_ident.conf <br /> ignore_system_indexes | off <br/> integer_datetimes | off <br /> join_collapse_limit |8 <br /> krb_caseins_users | off <br/> ve.<br /> krb_realm | <br /> krb_server_hostname | <br /> krb_server_keyfile | <a class="moz-txt-link-freetext"href="FILE:/etc/sysconfig/pgsql/krb5.keytab">FILE:/etc/sysconfig/pgsql/krb5.keytab</a><br /> krb_srvname | postgres <br /> lc_collate | en_US.UTF-8 <br /> lc_ctype | en_US.UTF-8 <br/> lc_messages | en_US.UTF-8 <br /> lc_monetary |en_US.UTF-8 <br /> lc_numeric | en_US.UTF-8 <br/> lc_time | en_US.UTF-8 <br /> listen_addresses |* <br /> local_preload_libraries | <br/> log_autovacuum_min_duration | -1 <br /> log_checkpoints |on <br /> log_connections | on <br/> log_destination | stderr <br /> log_directory |pg_log <br /> log_disconnections | on <br/> log_duration | on <br /> log_error_verbosity |default <br /> log_executor_stats | off <br/> log_filename | postgresql-%Y-%m-%d_%H%M%S.log <br /> log_hostname |on <br /> log_line_prefix | %t [%p]: [%l-1] <br/> log_lock_waits | on <br /> log_min_duration_statement |-1 <br /> log_min_error_statement | error <br/> log_min_messages | notice <br /> log_parser_stats |off <br /> log_planner_stats | off <br/> log_rotation_age | 1d <br /> log_rotation_size |100MB <br /> log_statement | all <br/> log_statement_stats | off <br /> log_temp_files |-1 <br /> log_timezone | Europe/Rome <br/> log_truncate_on_rotation | on <br /> logging_collector |on <br /> maintenance_work_mem | 16MB <br/> max_connections | 640 <br /> max_files_per_process |1000 <br /> max_fsm_pages | 204800 <br/> max_fsm_relations | 1000 <br /> max_function_args |100 <br /> max_identifier_length | 63 <br/> max_index_keys | 32 <br /> max_locks_per_transaction |64 <br /> max_prepared_transactions | 5 <br/> max_stack_depth | 2MB <br /> password_encryption |on <br /> port | 5432 <br/> post_auth_delay | 0 <br /> pre_auth_delay |0 <br /> random_page_cost | 4 <br/> regex_flavor | advanced <br /> search_path |"$user",public <br /> seq_page_cost | 1 <br/> server_encoding | UTF8 <br /> server_version |8.3.8 <br /> server_version_num | 80308 <br/> session_replication_role | origin <br /> shared_buffers |2GB <br /> shared_preload_libraries | <br/> silent_mode | off <br /> sql_inheritance |on <br /> ssl | off <br/> ssl_ciphers | ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH <br /> standard_conforming_strings |off <br /> statement_timeout | 0 <br/> superuser_reserved_connections | 3 <br /> synchronize_seqscans |on <br /> synchronous_commit | on <br/> syslog_facility | LOCAL0 <br /> syslog_ident |postgres <br /> tcp_keepalives_count | 0 <br/> tcp_keepalives_idle | 0 <br /> tcp_keepalives_interval |0 <br /> temp_buffers | 2048 <br/> temp_tablespaces | <br /> TimeZone |Europe/Rome <br /> timezone_abbreviations | Default <br/> trace_notify | off <br /> trace_sort |off <br /> track_activities | on <br/> track_counts | on <br /> transaction_isolation |read committed <br /> transaction_read_only | off <br/> transform_null_equals | off <br /> unix_socket_directory | <br /> unix_socket_group | <br/> unix_socket_permissions | 511 <br /> update_process_title |on <br /> vacuum_cost_delay | 0 <br/> vacuum_cost_limit | 200 <br /> vacuum_cost_page_dirty |20 <br /> vacuum_cost_page_hit | 1 <br/> vacuum_cost_page_miss | 10 <br /> vacuum_freeze_min_age |100000000 <br /> wal_buffers | 64kB <br/> wal_sync_method | fdatasync <br /> wal_writer_delay |200ms <br /> work_mem | 16MB <br/> xmlbinary | base64 <br /> xmloption |content <br /> zero_damaged_pages | off <br/><br /><br /><br /><blockquote cite="mid:5923.1281974354@sss.pgh.pa.us" type="cite"><pre wrap=""></pre><blockquote type="cite"><prewrap=""> MessageContext: 2042626048 total in 256 blocks; 16072 free (7 chunks); 2042609976 used<br> </pre></blockquote><pre wrap=""> This looks like it could be a memory leak, but we'd need to be able to reproduce the problem in order to investigate or fix it. </pre></blockquote> This issue is not deterministic, sometime it append (with TopMemoryContext) sometime not. Can I fixit by increasing work_mem?<br /><br /> Thanks <br /><br /> PS: I cannot give you the data for privace issue<br /><br /><blockquotecite="mid:5923.1281974354@sss.pgh.pa.us" type="cite"><pre wrap=""> regards, tom lane </pre></blockquote><br /><br /><div class="moz-signature">-- <br /></div><pre class="moz-signature" cols="72"><font face="Verdana"><b><fontcolor="#000000">Edoardo Innocenti </font></b><small><font color="#000000">Infrastructure Coordinator</font></small> <font color="#000000"> <b>SDB Information Technology</b> <small>Phone: +39.055.3811222 Fax: +39.055.5201411</small></font></font></pre><br /><div style="text-align: center; width: 620px; font-family: Verdana;"><small>Utilizziamo i dati personali che la riguardano esclusivamente per nostre finalità amministrative e contabili,anche quando li comunichiamo a terzi. Informazioni dettagliate, anche in ordine al Suo diritto di accesso e aglialtri Suoi diritti, sono riportate alla pagina <a href="http://www.savinodelbene.com/news/privacy.html">http://www.savinodelbene.com/news/privacy.html</a><br/> Se avete ricevutoquesto messaggio per errore Vi preghiamo di ritornarlo al mittente eliminandolo assieme agli eventuali allegati,ai sensi art. 616 codice penale <a href="http://www.savinodelbene.com/codice_penale_616.html">http://www.savinodelbene.com/codice_penale_616.html</a><br/> L'Aziendanon si assume alcuna responsabilità giuridica qualora pervengano da questo indirizzo messaggi estranei all'attivitàlavorativa o contrari a norme. </small></div><br />
Edoardo Innocenti <edoardo.innocenti@savinodelbene.com> writes: > Query Plan:<br> > "HashAggregate (cost=4910.06..4910.19 rows=1 width=659)" Hm ... the planner seems to think that this is a small query that isn't going to take long, which is a bit at odds with the fact that you're running out of memory. I suspect that these rowcount estimates are far too low, which would suggest that you need to make sure your ANALYZE statistics are up-to-date, and perhaps raise the statistics targets. Try to get the plan's estimated rowcounts to approximate reality. BTW, you might want to think about turning off your mail program's HTML option. What other people are seeing looks like this: http://archives.postgresql.org/pgsql-admin/2010-08/msg00126.php and it's not pretty. regards, tom lane
Il 17/08/2010 15:44, Tom Lane ha scritto: <blockquote cite="mid:9280.1282052697@sss.pgh.pa.us" type="cite"><pre wrap="">EdoardoInnocenti <a class="moz-txt-link-rfc2396E" href="mailto:edoardo.innocenti@savinodelbene.com"><edoardo.innocenti@savinodelbene.com></a>writes: </pre><blockquotetype="cite"><pre wrap="">Query Plan:<br> "HashAggregate (cost=4910.06..4910.19 rows=1 width=659)" </pre></blockquote><pre wrap=""> Hm ... the planner seems to think that this is a small query that isn't going to take long, which is a bit at odds with the fact that you're running out of memory. I suspect that these rowcount estimates are far too low, which would suggest that you need to make sure your ANALYZE statistics are up-to-date, and perhaps raise the statistics targets. Try to get the plan's estimated rowcounts to approximate reality. </pre></blockquote> We have pg_autovacuum enable and we launch vacuumdb -z every weeks. <br /> These tables do not growfast, 300 record max. Is it possible that <br /> the statistics are the cause?<br /><blockquote cite="mid:9280.1282052697@sss.pgh.pa.us"type="cite"><pre wrap="">BTW, you might want to think about turning off your mailprogram's HTML option. What other people are seeing looks like this: <a class="moz-txt-link-freetext" href="http://archives.postgresql.org/pgsql-admin/2010-08/msg00126.php">http://archives.postgresql.org/pgsql-admin/2010-08/msg00126.php</a> and it's not pretty. regards, tom lane </pre></blockquote><br /><br /><div class="moz-signature">-- <br /></div><pre class="moz-signature" cols="72"><font face="Verdana"><b><fontcolor="#000000">Edoardo Innocenti </font></b><small><font color="#000000">Infrastructure Coordinator</font></small> <font color="#000000"> <b>SDB Information Technology</b> <small>Phone: +39.055.3811222 Fax: +39.055.5201411</small></font></font></pre><br /><div style="text-align: center; width: 620px; font-family: Verdana;"><small>Utilizziamo i dati personali che la riguardano esclusivamente per nostre finalità amministrative e contabili,anche quando li comunichiamo a terzi. Informazioni dettagliate, anche in ordine al Suo diritto di accesso e aglialtri Suoi diritti, sono riportate alla pagina <a href="http://www.savinodelbene.com/news/privacy.html">http://www.savinodelbene.com/news/privacy.html</a><br/> Se avete ricevutoquesto messaggio per errore Vi preghiamo di ritornarlo al mittente eliminandolo assieme agli eventuali allegati,ai sensi art. 616 codice penale <a href="http://www.savinodelbene.com/codice_penale_616.html">http://www.savinodelbene.com/codice_penale_616.html</a><br/> L'Aziendanon si assume alcuna responsabilità giuridica qualora pervengano da questo indirizzo messaggi estranei all'attivitàlavorativa o contrari a norme. </small></div><br />