Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id DF5139FB37C for ; Wed, 20 Jun 2007 14:41:16 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 98408-03 for ; Wed, 20 Jun 2007 14:41:10 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.175]) by postgresql.org (Postfix) with ESMTP id CF7379FB25D for ; Wed, 20 Jun 2007 14:41:10 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id u2so448758uge for ; Wed, 20 Jun 2007 10:41:09 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:sender; b=TMCeWX5Hxu0iV8TGaLQmKBGh5Z9rX8IBX0yGcYJgGWiglmvwyr7pOkF5ex0KMLZq3EwxlB+8j9zLVv2AU9mXGu3YoTAyWJ59Rhc3AQRl2En6ouEvBPf/nWfxge8PB005k3HHR0z9MrwXa63q5TKyMMwDwcCLT6Kv37/PGxfrfzQ= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:sender; b=fcOnisU3DEJBCHhd1W2xeoBVryTK1aAklRTzIPDf6jrHmTUn9DVJwIWbVK9a1389B0YT3TUtvckAXzGF7hrNesi7noIDbbK7Nu3ewuXtGv3uEnwKvXieJ8sknODyMwhVsZru9JBoZgriics+u166XpylZ7ZIXhL70EFdASkZcO0= Received: by 10.67.98.4 with SMTP id a4mr1410715ugm.1182361269518; Wed, 20 Jun 2007 10:41:09 -0700 (PDT) Received: from ?172.24.32.117? ( [62.232.55.118]) by mx.google.com with ESMTP id a1sm4360531ugf.2007.06.20.10.41.08 (version=SSLv3 cipher=RC4-MD5); Wed, 20 Jun 2007 10:41:08 -0700 (PDT) Message-ID: <46796695.3080403@enterprisedb.com> Date: Wed, 20 Jun 2007 18:40:37 +0100 From: Heikki Linnakangas User-Agent: Icedove 1.5.0.10 (X11/20070329) MIME-Version: 1.0 To: Karl Wright CC: Francisco Reyes , Alvaro Herrera , Tom Lane , pgsql-performance@postgresql.org Subject: Re: Performance query about large tables, lots of concurrent access References: <4676DD57.7070304@metacarta.com> <14291.1182221811@sss.pgh.pa.us> <4677B831.8060704@metacarta.com> <4677D298.2010109@metacarta.com> <20070619132643.GC4265@alvh.no-ip.org> <4677DC06.3000300@metacarta.com> <20070619134637.GG4265@alvh.no-ip.org> <467963A8.2060703@metacarta.com> In-Reply-To: <467963A8.2060703@metacarta.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/431 X-Sequence-Number: 25464 Karl Wright wrote: > So, I guess this means that there's no way I can keep the database > adequately vacuumed with my anticipated load and hardware. One thing or > the other will have to change. Have you checked your maintenance_work_mem setting? If it's not large enough, vacuum will need to scan through all indexes multiple times instead of just once. With 16 GB of RAM you should set it to something like 2GB I think, or even more. > Is the VACUUM in 8.2 significantly faster than the one in 8.1? Yes, in particular if you have a lot of indexes. Scanning the indexes was done in index page order, which in worst case means random I/O, and we used to do an extra scan of all index pages to collect empty ones. Now it's all done as a single sequential pass. > Or, is > the database less sensitive performance-wise to delayed VACUUM commands? No. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com