X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 27E2CD1B1C9 for ; Thu, 5 Aug 2004 10:08:26 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 44294-10 for ; Thu, 5 Aug 2004 13:08:20 +0000 (GMT) Received: from pns.mm.eutelsat.org (pns.mm.eutelsat.org [194.214.173.227]) by svr1.postgresql.org (Postfix) with ESMTP id E4080D1B171 for ; Thu, 5 Aug 2004 10:08:15 -0300 (ADT) Received: from nts-03.mm.eutelsat.org (localhost [127.0.0.1]) by pns.mm.eutelsat.org (8.11.6/linuxconf) with ESMTP id i75D7s310555; Thu, 5 Aug 2004 15:07:54 +0200 Received: from [127.0.0.1] (accesspoint.mm.eutelsat.org [194.214.173.4]) by nts-03.mm.eutelsat.org (8.11.6/linuxconf) with ESMTP id i75D8GZ01181; Thu, 5 Aug 2004 15:08:17 +0200 Message-ID: <4112311A.2050808@bigfoot.com> Date: Thu, 05 Aug 2004 15:07:38 +0200 From: Gaetano Mendola User-Agent: Mozilla Thunderbird 0.7.1 (Windows/20040626) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Valerie Schneider DSI/DEV Cc: "pgsql-performance@postgresql.org" Subject: Re: [GENERAL] Tuning queries on large database References: <200408051229.i75CTZO22267@mu.meteo.fr> In-Reply-To: <200408051229.i75CTZO22267@mu.meteo.fr> X-Enigmail-Version: 0.84.2.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/63 X-Sequence-Number: 7699 -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Valerie Schneider DSI/DEV wrote: | #--------------------------------------------------------------------------- | # RESOURCE USAGE (except WAL) | #--------------------------------------------------------------------------- | | # - Memory - | | shared_buffers = 30000 # min 16, at least max_connections*2, 8KB each | #sort_mem = 1024 # min 64, size in KB | sort_mem = 5000 # min 64, size in KB | #vacuum_mem = 8192 # min 1024, size in KB | | # - Free Space Map - | | #max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes each | #max_fsm_relations = 1000 # min 100, ~50 bytes each | | # - Kernel Resource Usage - | | #max_files_per_process = 1000 # min 25 | #preload_libraries = '' | | | #--------------------------------------------------------------------------- | # WRITE AHEAD LOG | #--------------------------------------------------------------------------- | | # - Settings - | | #fsync = true # turns forced synchronization on or off | #wal_sync_method = fsync # the default varies across platforms: | # fsync, fdatasync, open_sync, or open_datasync | #wal_buffers = 8 # min 4, 8KB each | | # - Checkpoints - | | #checkpoint_segments = 3 # in logfile segments, min 1, 16MB each | checkpoint_segments = 30 # in logfile segments, min 1, 16MB each | #checkpoint_timeout = 300 # range 30-3600, in seconds | #checkpoint_warning = 30 # 0 is off, in seconds | #commit_delay = 0 # range 0-100000, in microseconds | #commit_siblings = 5 # range 1-1000 | | | #--------------------------------------------------------------------------- | # QUERY TUNING | #--------------------------------------------------------------------------- | | # - Planner Method Enabling - | | #enable_hashagg = true | #enable_hashjoin = true | #enable_indexscan = true | #enable_mergejoin = true | #enable_nestloop = true | enable_seqscan = false | #enable_sort = true | #enable_tidscan = true | | # - Planner Cost Constants - | | #effective_cache_size = 1000 # typically 8KB each | effective_cache_size = 200000 # typically 8KB each | #random_page_cost = 4 # units are one sequential page fetch cost | random_page_cost = 2 # units are one sequential page fetch cost | #cpu_tuple_cost = 0.01 # (same) | #cpu_index_tuple_cost = 0.001 # (same) | #cpu_operator_cost = 0.0025 # (same) | | # - Genetic Query Optimizer - | | #geqo = true | #geqo_threshold = 11 | #geqo_effort = 1 | #geqo_generations = 0 | #geqo_pool_size = 0 # default based on tables in statement, | # range 128-1024 | #geqo_selection_bias = 2.0 # range 1.5-2.0 Your wal_buffers is too small try do bump up your wal_buffers to ~3000, and see the effects. why did you disable the sequential_scan (see later) ? Try also to lower the cpu_costs: cpu_tuple_cost = 0.005 cpu_index_tuple_cost = 0.0005 # (same) cpu_operator_cost = 0.0005 # (same) this will push the optimizer to choose the index scans. If not show us the explain with enable_seqscan = false and with enable_seqscan = true *Mount also your partition with the noatime parameter* Regards Gaeatano Mendola -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBEjEY7UpzwH2SGd4RAtxnAKDuTtYZvWMXL7zjHWU20VFtm2V1OACg/Y1l GZuQ5RviMB2nB4M8G6PW17U= =HxGz -----END PGP SIGNATURE-----