X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 3F005D1CCBE for ; Wed, 11 Feb 2004 16:16:31 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 78785-07 for ; Wed, 11 Feb 2004 12:16:15 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 6F0E7D1C9E1 for ; Wed, 11 Feb 2004 12:16:16 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id i1BGGF1Y033566 for ; Wed, 11 Feb 2004 16:16:15 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i1BGAW5H032290 for pgsql-performance@postgresql.org; Wed, 11 Feb 2004 16:10:32 GMT From: Chris Browne X-Newsgroups: comp.databases.postgresql.performance Subject: Re: slow database Date: Wed, 11 Feb 2004 10:39:19 -0500 Organization: cbbrowne Computing Inc Lines: 63 Message-ID: <603c9hiq1k.fsf@dev6.int.libertyrms.info> References: <1076509411.402a3ae38d9b8@tiago.hazor.com.br> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@news.hub.org User-Agent: Gnus/5.110002 (No Gnus v0.2) XEmacs/21.4 (Reasonable Discussion, linux) Cancel-Lock: sha1:02lj7CptZQZ8T3Gc9H1jq69rJqo= To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/131 X-Sequence-Number: 5655 alemon@tiago.hazor.com.br writes: > my data base is very slow. The machine is a processor Xeon 2GB with > 256 MB of RAM DDR. My archive of configuration is this: > sort_mem = 131072 # min 64, size in KB > #vacuum_mem = 8192 # min 1024, size in KB Change it back to 8192, or perhaps even less. This large value is probably causing swapping, because it leads to every sort trying to use 1073741824 bytes of memory, which is considerably more than you have. > fsync = false > wal_sync_method = fdatasync # the default varies across platforms: I presume that you are aware that you have chosen the value that leaves your data vulnerable to corruption? I wouldn't set this to false... > enable_seqscan = false > enable_indexscan = false > enable_tidscan = false > enable_sort = false > enable_nestloop = false > enable_mergejoin = false > enable_hashjoin = false Was there some reason why you wanted to disable every query optimization strategy that can be disabled? If you're looking to get slow queries, this would accomplish that nicely. > effective_cache_size = 170000 # typically 8KB each > random_page_cost = 1000000000 # units are one sequential page fetch cost > cpu_tuple_cost = 0.3 # (same) > cpu_index_tuple_cost = 0.6 # (same) > cpu_operator_cost = 0.7 # (same) Where did you get those numbers? The random_page_cost alone will probably force every query to do seq scans, ignoring indexes, and is _really_ nonsensical. The other values seem way off. > default_statistics_target = 1 # range 1-1000 ... Apparently it didn't suffice to try to disable query optimization, and modify the cost parameters into nonsense; it was also "needful" to tell the statistics analyzer to virtually eliminate statistics collection. If you want a value other than 10, then pick a value slightly LARGER than 10. > somebody please knows to give tips to me to increase the performance Delete the postgresql.conf file, create a new database using initdb, and take the file produced by _that_, and replace with that one. The default values, while not necessarily perfect, are likely to be 100x better than what you have got. Was this the result of someone trying to tune the database for some sort of anti-benchmark? -- let name="cbbrowne" and tld="cbbrowne.com" in name ^ "@" ^ tld;; http://www.ntlug.org/~cbbrowne/rdbms.html Rules of the Evil Overlord #179. "I will not outsource core functions."