Re: Massive table bloat
От | Rural Hunter |
---|---|
Тема | Re: Massive table bloat |
Дата | |
Msg-id | 50C8144D.7050000@gmail.com обсуждение исходный текст |
Ответ на | Re: Massive table bloat (Sergey Konoplev <gray.ru@gmail.com>) |
Ответы |
Re: Massive table bloat
(Sergey Konoplev <gray.ru@gmail.com>)
|
Список | pgsql-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 >
В списке pgsql-admin по дате отправления: