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 58A3F53B67 for ; Mon, 18 Apr 2005 11:36:23 -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 32029-01 for ; Mon, 18 Apr 2005 14:36:18 +0000 (GMT) Received: from itapoa.terra.com.br (itapoa.terra.com.br [200.154.55.227]) by svr1.postgresql.org (Postfix) with ESMTP id 3A52653D7D for ; Mon, 18 Apr 2005 11:36:00 -0300 (ADT) Received: from cuenca.terra.com.br (cuenca.terra.com.br [200.154.55.130]) by itapoa.terra.com.br (Postfix) with ESMTP id 4077430CFEA for ; Mon, 18 Apr 2005 11:36:04 -0300 (BRT) X-Terra-Karma: -2% X-Terra-Hash: 1d27f70f4951edd6ac3f9af5a794db05 Received-SPF: pass (cuenca.terra.com.br: domain of terra.com.br designates 200.154.55.130 as permitted sender) client-ip=200.154.55.130; envelope-from=rodrigo.miguel@terra.com.br; helo=rmsmnote; Received: from rmsmnote (unknown [200.168.177.140]) (authenticated user rodrmigu) by cuenca.terra.com.br (Postfix) with ESMTP id EE9F613001B for ; Mon, 18 Apr 2005 11:36:03 -0300 (BRT) From: "Rodrigo Moreno" To: Subject: How to improve postgres performace Date: Mon, 18 Apr 2005 11:36:01 -0300 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook, Build 11.0.5510 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 Thread-Index: AcVEI/FSkM+18tPnRmqfEik6OBtYjg== Message-Id: <20050418143603.EE9F613001B@cuenca.terra.com.br> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.177 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST X-Spam-Level: * X-Archive-Number: 200504/398 X-Sequence-Number: 11795 Hi all, Some months ago i post a similar problem here i it was solved by running vaccumdb time by time. So, when i started using the postgres, i never been used the vacuumdb, and after 2 months i started using once a week, after few weeks, i tried once a day and now twice a day. At this weekend i have started to use pg_autovacuum with default settings. I really worried about that, because it's no enough anymore, and users claim about performace. But running the vacuumdb full, everthing starts to run better again, so i think the problem is not related to a specific query. What I can do to check what I have change to get more performance ? Could I use vacuum verbose to check what is going on ? So, how ? Most all the time, even user querying the server the machine is 96%-100% idle. The discs are SCSI, FreeBSD 5.3, the size of database is 1.1Gb, max 30 connections and 10 concurrent conections. My server have 512Mb Ram and 256Mb has changed to SHMAX. There is max 1000 inserted/excluded/Updated row by day. These are my kernel params: -------------------------- options SHMMAXPGS=65536 options SEMMNI=40 options SEMMNS=240 options SEMUME=40 options SEMMNU=120 Postgresql.conf non-default settings ------------------------------------ tcpip_socket = true max_connections = 30 shared_buffers = 1024 sort_mem = 2048 vacuum_mem = 16384 wal_buffers = 16 checkpoint_segments = 5 effective_cache_size = 16384 random_page_cost = 2 stats_start_collector = true stats_row_level = true I follow the most of all discussions in this group and tried myself change the parameters, but now, I don't know more what to do to get better performance. Thanks a Lot Rodrigo Moreno