From: Rural Hunter <ruralhunter@gmail.com>
To: Sergey Konoplev <gray.ru@gmail.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: Massive table bloat
Date: Wed, 12 Dec 2012 13:21:17 +0800
Message-ID: <50C8144D.7050000@gmail.com> (raw)
In-Reply-To: <CAL_0b1tz+J_qxk9wAeFiMStJyFhs-9OpA3X_gNqznPQ6bVKw6w@mail.gmail.com>
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
于 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ruralhunter@gmail.com, gray.ru@gmail.com
Subject: Re: Massive table bloat
In-Reply-To: <50C8144D.7050000@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox