X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 1B3FA52A9E for ; Thu, 18 Aug 2005 13:43:28 -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 12596-02 for ; Thu, 18 Aug 2005 16:43:25 +0000 (GMT) Received: from hub.org (hub.org [200.46.204.220]) by svr1.postgresql.org (Postfix) with ESMTP id 28B3052A1E for ; Thu, 18 Aug 2005 13:43:25 -0300 (ADT) Received: from localhost (av.hub.org [200.46.204.144]) by hub.org (Postfix) with ESMTP id CC6F6A25B2F for ; Thu, 18 Aug 2005 13:43:24 -0300 (ADT) Received: from hub.org ([200.46.204.220]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 97426-06 for ; Thu, 18 Aug 2005 16:43:19 +0000 (GMT) Received: from ganymede.hub.org (blk-222-82-85.eastlink.ca [24.222.82.85]) by hub.org (Postfix) with ESMTP id BF96FA25B6A for ; Thu, 18 Aug 2005 13:43:18 -0300 (ADT) Received: by ganymede.hub.org (Postfix, from userid 1000) id 7290434077; Thu, 18 Aug 2005 13:43:19 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by ganymede.hub.org (Postfix) with ESMTP id 6F71434058 for ; Thu, 18 Aug 2005 13:43:19 -0300 (ADT) X-Return-Path: X-Received: from ganymede.hub.org ([unix socket]) by ganymede.hub.org (Cyrus v2.2.12) with LMTPA; Thu, 18 Aug 2005 10:10:36 -0300 X-Sieve: CMU Sieve 2.2 X-Received: from localhost (localhost [127.0.0.1]) by ganymede.hub.org (Postfix) with ESMTP id 35A1437142 for ; Thu, 18 Aug 2005 10:10:36 -0300 (ADT) X-Received: from mail.postgresql.org [200.46.204.71] by localhost with IMAP (fetchmail-6.2.5.2) for scrappy@localhost (single-drop); Thu, 18 Aug 2005 10:10:36 -0300 (ADT) X-Received: from svr1.postgresql.org ([unix socket]) by svr1.postgresql.org (Cyrus v2.2.12) with LMTPA; Thu, 18 Aug 2005 10:08:20 -0300 X-Sieve: CMU Sieve 2.2 X-Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 3389A529CB for ; Thu, 18 Aug 2005 10:08:20 -0300 (ADT) X-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 84008-06 for ; Thu, 18 Aug 2005 13:08:19 +0000 (GMT) X-Received: from postgresql.org (svr1.postgresql.org [200.46.204.71]) by svr1.postgresql.org (Postfix) with ESMTP id 07ED552809 for ; Thu, 18 Aug 2005 10:08:19 -0300 (ADT) X-Original-To: pgsql-admin-postgresql.org-owner@localhost.postgresql.org X-Delivered-To: pgsql-admin-postgresql.org-owner@localhost.postgresql.org X-Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 674A2529D6 for ; Thu, 18 Aug 2005 10:08:17 -0300 (ADT) X-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 84008-05 for ; Thu, 18 Aug 2005 13:08:13 +0000 (GMT) X-Received: from 217-20-115-93.internetserviceteam.com (217-20-115-93.internetserviceteam.com [217.20.115.93]) by svr1.postgresql.org (Postfix) with ESMTP id 3FEF4529BB for ; Thu, 18 Aug 2005 10:08:11 -0300 (ADT) X-Received: from [192.168.1.71] (190.Red-81-44-0.pooles.rima-tde.net [81.44.0.190]) by 217-20-115-93.internetserviceteam.com (Postfix) with ESMTP id 52B153877D for ; Thu, 18 Aug 2005 14:17:40 +0200 (CEST) Message-ID: <4304883E.5010704@mediaroot.de> Date: Thu, 18 Aug 2005 14:08:14 +0100 From: Aldor User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-admin-owner@postgresql.org Subject: Normal VACUUM locks indexes? Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Virus-Scanned: by amavisd-new at hub.org ReSent-Date: Thu, 18 Aug 2005 13:43:15 -0300 (ADT) Resent-From: "Marc G. Fournier" Resent-To: pgsql-admin@postgresql.org ReSent-Subject: Normal VACUUM locks indexes? ReSent-Message-ID: <20050818134315.O1002@ganymede.hub.org> X-Virus-Scanned: by amavisd-new at hub.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.946 required=5 tests=[AWL=0.896, FORGED_RCVD_HELO=0.05] X-Spam-Level: X-Archive-Number: 200508/245 X-Sequence-Number: 18564 Hi, when vacuuming a big table with some indexes I experienced a locking of VACUUM. As I understood the theory the normal VACUUM should never lock something or influence queries made to the table. When running VACUUM it was vacuuming first the indexes, after that it was vacuuming the table data. While it was vacuuming the indexes I tried to make some queries into the table - I didn't get any lock error but saw that that the process which was responsible for my query was set to the state WAIT. After that I made a second query, again... WAIT. After VACUUM finished the indexes and started processing the table data the WAIT processes were processed and the query result appeared. Any new query was also not set to WAIT, it was processed immediately. The idea of normal VACUUM is to run it while the system needs to process user queries in the same time. I saw this behevior when setting the vacuum-options in postgresql.conf to no delay so that I can run VACUUM with nearly full power. I'm not sure if I would experience the same problem when I would set up a delay and setup the cost rules in postgresql.conf but I think when normal VACUUM starts vacuuming the indexes of the table it locks queries using them. On bigger tables this can mean that your queries are sleeping for 10-20 Minutes until the indexes have been vacuumed and this is the reason why I think that this behavior is not compatible with the "theory" of normal VACUUM;-) 1) Question: Is this a normal behavior of Postgres to lock the indexes while vacuuming them? 2a) Question: If yes: Is there anything which can be done to prevent Postgres doing this or maybe tell VACUUM to skip the indexes and only vacuum the table data? 2b) Question: If no: Which parameters are important to find out why this happens only in my case or what kind of other information is needed to find out why this happen? Aldor