Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from
	<pgsql-admin-owner+M40459=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
	id 1Tiel6-0000qP-OD
	for pgsql-admin@arkaria.postgresql.org; Wed, 12 Dec 2012 05:21:29 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
	by malur.postgresql.org with smtp (Exim 4.72) (envelope-from
	<pgsql-admin-owner+M40459=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
	id 1Tiel5-0007h5-PL
	for pgsql-admin@arkaria.postgresql.org; Wed, 12 Dec 2012 05:21:27 +0000
Received: from makus.postgresql.org ([98.129.198.125])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <ruralhunter@gmail.com>) id 1Tiel4-0007gz-FZ
	for pgsql-admin@postgresql.org; Wed, 12 Dec 2012 05:21:26 +0000
Received: from mail-pa0-f44.google.com ([209.85.220.44])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <ruralhunter@gmail.com>) id 1Tiel2-00068p-22
	for pgsql-admin@postgresql.org; Wed, 12 Dec 2012 05:21:25 +0000
Received: by mail-pa0-f44.google.com with SMTP id hz11so233465pad.31
	for <pgsql-admin@postgresql.org>; Tue, 11 Dec 2012 21:21:23 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject
	:references:in-reply-to:content-type:content-transfer-encoding;
	bh=6VQM6OBaq/575r3YsmAEx90tiXJMCVffEKwHq7R34/o=;
	b=0MKKaxyeIha+DcrpAXR4UoLFtYemONcYdTN120u7LS3+6ounwyKvfF68E5glqMwBFr
	Fjhh2pTuKPPAxbboNR6f7XxOHD3O/3HeTP3ulpLakTP7A4TYluauQs01M33DFQg5eL7v
	fY+xlyR+kENm+SRM20EvBBwR1nYtsP782gd1KmqkC+Htymo/U0pBckm5oWFOLSCB+j/F
	di9d2fkeimlAKPZmsf0vEq51hEmjgzmR2uYEUVndHcC33DM6VVVp+UYLBYrCr3Hj+enL
	FzyVeqfZ6H1MLH/m/p3eaXldCKigSLht7oH7pUWwKWOMnI3Tj5cmNIDN9vmBc5T1eGmp
	yxYQ==
Received: by 10.66.83.196 with SMTP id s4mr2133653pay.74.1355289681938;
	Tue, 11 Dec 2012 21:21:21 -0800 (PST)
Received: from [192.168.1.9] ([116.226.40.101])
	by mx.google.com with ESMTPS id vk5sm15001070pbc.34.2012.12.11.21.21.18
	(version=TLSv1/SSLv3 cipher=OTHER);
	Tue, 11 Dec 2012 21:21:20 -0800 (PST)
Message-ID: <50C8144D.7050000@gmail.com>
Date: Wed, 12 Dec 2012 13:21:17 +0800
From: Rural Hunter <ruralhunter@gmail.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; rv:17.0) Gecko/17.0 Thunderbird/17.0
MIME-Version: 1.0
To: Sergey Konoplev <gray.ru@gmail.com>
CC: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: Massive table bloat
References: <1355242268184-5736111.post@n5.nabble.com>
	<CAL_0b1tKNmHe4Qq7fDAkpJNt2Jq8sWzKmwWtscF659x8BYuXeQ@mail.gmail.com>
	<1355260487663-5736180.post@n5.nabble.com>
	<CAL_0b1vDs8dpxFwyMwgROoWtM=QQaPvpm6rA8cQLXGP_AcU8rQ@mail.gmail.com>
	<50C7FCAF.1080502@gmail.com>
	<CAL_0b1vQvDf9tKwavJgTnToTHKBtZj7mFa0PzOo_o85F8M5Zuw@mail.gmail.com>
	<50C800B6.6060306@gmail.com>
	<CAL_0b1upditor-T0KxpvxL+FEfJ6PJTmj+uHaX5XobHdM6RbDg@mail.gmail.com>
	<50C80882.2010502@gmail.com>
	<CAL_0b1tz+J_qxk9wAeFiMStJyFhs-9OpA3X_gNqznPQ6bVKw6w@mail.gmail.com>
In-Reply-To: 
 <CAL_0b1tz+J_qxk9wAeFiMStJyFhs-9OpA3X_gNqznPQ6bVKw6w@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
X-Pg-Spam-Score: -1.6 (-)
List-Archive: <http://archives.postgresql.org/pgsql-admin>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-admin.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@postgresql.org>
List-Post: <mailto:pgsql-admin@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-admin>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
X-Mailing-List: pgsql-admin
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

于 2012/12/12 12:47, Sergey Konoplev 写道:
> On Tue, Dec 11, 2012 at 8:30 PM, Rural Hunter <ruralhunter@gmail.com> wrote:
>> No. I was running it with another db super user. should it only be run by
>> postgres?
>>
>> $ echo 'SELECT 1;' | psql -q -A -t -X -U postgres -P null="<NULL>"
>> Password for user postgres:
>> 1
> Oh, looks like I know why it happens.
>
> The tool does not expect any password prompts.
>
> $ echo 'SELECT 1;' | psql -q -A -t -X -U postgres -P null="<NULL>"
> 1
>
> It expects either trusted access (without password) or that password
> will be specified as a parameter -W somesecret.
>
> I will definitely need to fix it. My false, sorry, it is not easy to
> wrap a command line tool to a fully flegged database adapter in Perl.
>
> So either make a trusted access for the super user from the localhost
> (you are working on localhost, right?) or specify  -W
> theuserspassword.
>
> Anyway I suggest to install DBD::Pg Perl module, it will work much
> faster and will load the system significantly less. The psql wrapper I
> was asked to implement because one of the users had a hopeless boss
> who did not allowed him to install DBD::Pg.
Ok, thanks. I installed dbd::pg. Now I can run it with specify 
additional parameters(-h, -p). Seems pgcompactor doesn't read them from 
env variables. However, I met another error when pgcompactor processes 
tables. Seems it doesn't expect some tables with autovacuum off:
ERROR A database error occurred, exiting:
DatabaseError DBD::Pg::st execute failed: ERROR:  invalid input syntax 
for type real: "{autovacuum_enabled=false}" [for Statement "SELECT
     ceil(pure_page_count * 100 / fillfactor) AS effective_page_count,
     round(
         100 * (
             1 - (pure_page_count * 100 / fillfactor) / (size::real / bs)
         )::numeric, 2
     ) AS free_percent,
     ceil(size::real - bs * pure_page_count * 100 / fillfactor) AS 
free_space
FROM (
     SELECT
         bs, size, fillfactor,
         ceil(
             reltuples * (
                 max(stanullfrac) * ma * ceil(
                     (
                         ma * ceil(
                             (
                                 header_width +
                                 ma * ceil(count(1)::real / ma)
                             )::real / ma
                         ) + sum((1 - stanullfrac) * stawidth)
                     )::real / ma
                 ) +
                 (1 - max(stanullfrac)) * ma * ceil(
                     (
                         ma * ceil(header_width::real / ma) +
                         sum((1 - stanullfrac) * stawidth)
                     )::real / ma
                 )
             )::real / (bs - 24)
         ) AS pure_page_count
     FROM (
         SELECT
             pg_catalog.pg_class.oid AS class_oid,
             reltuples,
             23 AS header_width, 8 AS ma,
             current_setting('block_size')::integer AS bs,
             pg_catalog.pg_relation_size(pg_catalog.pg_class.oid) AS size,
             coalesce(
                 regexp_replace(
                     reloptions::text, E'.*fillfactor=(\\d+).*', E'\\1'),
                 '100')::real AS fillfactor
         FROM pg_catalog.pg_class
         WHERE pg_catalog.pg_class.oid = 'public.article_text_197'::regclass
     ) AS const
     LEFT JOIN pg_catalog.pg_statistic ON starelid = class_oid
     GROUP BY bs, class_oid, fillfactor, ma, size, reltuples, header_width
) AS sq
"] at /loader/0x1ec3ff8/PgToolkit/Database/Dbi.pm line 143.

>
>>
>>>> 于 2012/12/12 11:46, Sergey Konoplev 写道:
>>>>
>>>>> On Tue, Dec 11, 2012 at 7:40 PM, Rural Hunter <ruralhunter@gmail.com>
>>>>> wrote:
>>>>>> I downloaded pgtoolkit-v1.0beta3-fatscripts.tar.gz and tested it. I got
>>>>>> error when trying this:
>>>>>> ./pgcompactor -a -u
>>>>>> DatabaseChooserError Can not find an adapter. at
>>>>>> /loader/0x1c26f18/PgToolkit/DatabaseChooser.pm line 63.
>>>>>> ./pgcompactor -d testdb -u
>>>>>> DatabaseChooserError Can not find an adapter. at
>>>>>> /loader/0x1156f50/PgToolkit/DatabaseChooser.pm line 63.
>>>>> You need to have either psql or DBD::PgPP or DBD::Pg on your machine.
>>>>> The last one is recommended.
>>>>>
>>>>>> 于 2012/12/12 5:27, Sergey Konoplev 写道:
>>>>>>
>>>>>>> On Tue, Dec 11, 2012 at 1:14 PM, Michael Sawyers <msawyers@iii.com>
>>>>>>> wrote:
>>>>>>>> Thanks for the tool suggestion.  I already know that I will be
>>>>>>>> refused
>>>>>>>> permission to use it on a live db for the first run here, but I will
>>>>>>>> be
>>>>>>>> using this on several test machines that I am sure are bloated to
>>>>>>>> prove
>>>>>>>> the
>>>>>>>> point and get this added into the standard toolkit here.
>>>>>>> If you will have any feedback considering pgcompactor feel free to
>>>>>>> write me directly. I am going to publish a new release in the nearest
>>>>>>> days so I may include your issues in it.
>>>>>>>
>>>>>>> ps. I have been using this tool constantly on more than 40 DB servers
>>>>>>> for more than a year so it is tested quite good.
>>>>>>>
>>>>>>> --
>>>>>>> Sergey Konoplev
>>>>>>> Database and Software Architect
>>>>>>> http://www.linkedin.com/in/grayhemp
>>>>>>>
>>>>>>> Phones:
>>>>>>> USA +1 415 867 9984
>>>>>>> Russia, Moscow +7 901 903 0499
>>>>>>> Russia, Krasnodar +7 988 888 1979
>>>>>>>
>>>>>>> Skype: gray-hemp
>>>>>>> Jabber: gray.ru@gmail.com
>>>>>>>
>>>>>>>
>>>>> --
>>>>> Sergey Konoplev
>>>>> Database and Software Architect
>>>>> http://www.linkedin.com/in/grayhemp
>>>>>
>>>>> Phones:
>>>>> USA +1 415 867 9984
>>>>> Russia, Moscow +7 901 903 0499
>>>>> Russia, Krasnodar +7 988 888 1979
>>>>>
>>>>> Skype: gray-hemp
>>>>> Jabber: gray.ru@gmail.com
>>>>>
>>>
>>> --
>>> Sergey Konoplev
>>> Database and Software Architect
>>> http://www.linkedin.com/in/grayhemp
>>>
>>> Phones:
>>> USA +1 415 867 9984
>>> Russia, Moscow +7 901 903 0499
>>> Russia, Krasnodar +7 988 888 1979
>>>
>>> Skype: gray-hemp
>>> Jabber: gray.ru@gmail.com
>>>
>
>
> --
> Sergey Konoplev
> Database and Software Architect
> http://www.linkedin.com/in/grayhemp
>
> Phones:
> USA +1 415 867 9984
> Russia, Moscow +7 901 903 0499
> Russia, Krasnodar +7 988 888 1979
>
> Skype: gray-hemp
> Jabber: gray.ru@gmail.com
>



-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin