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 9D5E1D1E934 for ; Wed, 24 Mar 2004 18:04:11 +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 34594-06 for ; Wed, 24 Mar 2004 14:04:08 -0400 (AST) Received: from mail.unisoftbg.com (mail.unisoftbg.com [194.12.229.207]) by svr1.postgresql.org (Postfix) with ESMTP id 14BFED1D2AC for ; Wed, 24 Mar 2004 14:04:04 -0400 (AST) Received: (qmail 4710 invoked by uid 507); 24 Mar 2004 19:08:46 -0000 Received: from unknown (HELO t1.unisoftbg.com) (pginfo%t1.unisoftbg.com@194.12.229.193) by 0 with SMTP; 24 Mar 2004 19:08:46 -0000 Message-ID: <4061BC33.9CC1E712@t1.unisoftbg.com> Date: Wed, 24 Mar 2004 17:49:55 +0100 From: pginfo X-Mailer: Mozilla 4.03 [en] (Win95; I) MIME-Version: 1.0 To: "scott.marlowe" Cc: "pgsql-performance@postgresql.org" Subject: Re: slow vacuum performance References: Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/355 X-Sequence-Number: 6211 Hi, scott.marlowe wrote: > On Wed, 24 Mar 2004, pginfo wrote: > > > Hi, > > > > I am running pg 7.4.1 on linux box. > > I have a midle size DB with many updates and after it I try to run > > vacuum full analyze. > > Is there a reason to not use just regular vacuum / analyze (i.e. NOT > full)? > Yes, in case I make massive updates (only in my case of cource) for example 2 M rows, I do not expect to have 2M new rows in next 180 days.That is the reaso for running vacuum full. My idea was to free unneedet space and so to have faster system. It is possible that I am wrong. > > It takes about 2 h. > > Full vacuums, by their nature, tend to be a bit slow. It's better to let > the database achieve a kind of "steady state" with regards to number of > dead tuples, and use regular vacuums to reclaim said space rather than a > full vacuum. > > > How can I improve the vacuum full analyze time? > > > > My configuration: > > > > shared_buffers = 15000 # min 16, at least max_connections*2, > > 8KB each > > sort_mem = 10000 # min 64, size in KB > > You might want to look at dropping sort_mem. It would appear you've been > going through the postgresql.conf file and bumping up numbers to see what > works and what doesn't. While most of the settings aren't too dangerous > to crank up a little high, sort_mem is quite dangerous to crank up high, > should you have a lot of people connected who are all sorting. Note that > sort_mem is a limit PER SORT, not per backend, or per database, or per > user, or even per table, but per sort. IF a query needs to run three or > four sorts, it can use 3 or 4x sort_mem. If a hundred users do this at > once, they can then use 300 or 400x sort_mem. You can see where I'm > heading. > > Note that for individual sorts in batch files, like import processes, you > can bump up sort_mem with the set command, so you don't have to have a > large setting in postgresql.conf to use a lot of sort mem when you need > to, you can just grab it during that one session. > I know. In my case we are using many ID's declared as varchar/name (I know it is bad idea, butwe are migrating this system from oracle) and pg have very bad performance with varchar/name indexes. The only solution I found was to increase the sort mem. But, I wll try to decrease this one and to see the result. > > vacuum_mem = 32000 # min 1024, size in KB > > If you've got lots of memory, crank up vacuum_mem to the 200 to 500 meg > range and see what happens. > I wil try it today. It is good idea and hope it will help. > For a good tuning guide, go here: > > http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html I know it. It is the best I found and also the site. Thanks for the help. ivan.