pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedMassive table bloat
25+ messages / 5 participants
[nested] [flat]
* Massive table bloat
@ 2012-12-11 16:11 Michael Sawyers <msawyers@iii.com>
2012-12-11 18:25 ` Re: Massive table bloat Craig James <cjames@emolecules.com>
2012-12-11 19:35 ` Re: Massive table bloat Steve Crawford <scrawford@pinpointresearch.com>
2012-12-11 21:09 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
0 siblings, 3 replies; 25+ messages in thread
From: Michael Sawyers @ 2012-12-11 16:11 UTC (permalink / raw)
To: pgsql-admin
Our dba quit last week leaving me with an interesting problem.
We have a table currently using 33gb worth of space for only 152mb worth of
data because of bad processes or autovacuum not being aggressive enough. I
was able to confirm the size difference by doing a create table as select
along with a second test of restoring the table from the dump file to a dev
machine.
There is a very large list of foreign key relationships that I'm not
including for the sake of brevity.
The database version is 8.4.1
The old DBA had said that vacuum full would take days to complete, and we
don't have that much of a window. So I was considering using the to force
a full table rewrite. In testing on a dev machine it only took about five
minutes.
I do not have as much hands on experience with postgres so I wanted to get
thoughts on what is considered the proper way to deal with this kind of
situation.
Any comments would be welcome.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Massive-table-bloat-tp5736111.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Massive table bloat
2012-12-11 16:11 Massive table bloat Michael Sawyers <msawyers@iii.com>
@ 2012-12-11 18:25 ` Craig James <cjames@emolecules.com>
2012-12-11 19:41 ` Re: Massive table bloat Michael Sawyers <msawyers@iii.com>
2 siblings, 1 reply; 25+ messages in thread
From: Craig James @ 2012-12-11 18:25 UTC (permalink / raw)
To: Michael Sawyers <msawyers@iii.com>; +Cc: pgsql-admin
On Tue, Dec 11, 2012 at 8:11 AM, Michael Sawyers <msawyers@iii.com> wrote:
> Our dba quit last week leaving me with an interesting problem.
>
> We have a table currently using 33gb worth of space for only 152mb worth of
> data because of bad processes or autovacuum not being aggressive enough. I
> was able to confirm the size difference by doing a create table as select
> along with a second test of restoring the table from the dump file to a dev
> machine.
>
> There is a very large list of foreign key relationships that I'm not
> including for the sake of brevity.
>
> The database version is 8.4.1
>
> The old DBA had said that vacuum full would take days to complete, and we
> don't have that much of a window. So I was considering using the to force
> a full table rewrite. In testing on a dev machine it only took about five
> minutes.
>
> I do not have as much hands on experience with postgres so I wanted to get
> thoughts on what is considered the proper way to deal with this kind of
> situation.
>
> Any comments would be welcome.
>
>
With only 33 GB of data, you might consider a full
dump/drop-db/create-db/restore. You didn't say what sort of hardware you're
using, or what sort of down time you could afford, but generally pg_dump
and pg_restore are remarkably fast. If you have spare/backup machines, you
could even do trial runs to see how long it takes.
That would also give you the opportunity to upgrade to a new version of
Postgres, although that might require changes to your app that you're not
willing to make. Even if you don't upgrade the version (you should at
least upgrade to the latest 8.4.x release), dump/restore will fix your
problem.
Craig James
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Massive table bloat
2012-12-11 16:11 Massive table bloat Michael Sawyers <msawyers@iii.com>
2012-12-11 18:25 ` Re: Massive table bloat Craig James <cjames@emolecules.com>
@ 2012-12-11 19:41 ` Michael Sawyers <msawyers@iii.com>
2012-12-11 20:48 ` Re: Massive table bloat Steve Crawford <scrawford@pinpointresearch.com>
0 siblings, 1 reply; 25+ messages in thread
From: Michael Sawyers @ 2012-12-11 19:41 UTC (permalink / raw)
To: pgsql-admin
Political reasons have ruled out the dump and reload options, but restoring
the entire database took several hours. I'm also restricted on version
because newer versions of postgres are not supported with that specific
product, including maintenance updates.
So I'm trying to fix things in place which is where some of the difficulty
is coming in. I did test the the restored database on a dev machine, with
vacuum full, alter table, and cluster table all taking a relatively short
window of time.
I'm just trying to get the best method based on the limitations I have been
handed. Right now it seems like clustering the table on an index, then
dropping that cluster flag might be the cleanest way, but I'm just not 100%
sure.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Massive-table-bloat-tp5736111p5736150.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Massive table bloat
2012-12-11 16:11 Massive table bloat Michael Sawyers <msawyers@iii.com>
2012-12-11 18:25 ` Re: Massive table bloat Craig James <cjames@emolecules.com>
2012-12-11 19:41 ` Re: Massive table bloat Michael Sawyers <msawyers@iii.com>
@ 2012-12-11 20:48 ` Steve Crawford <scrawford@pinpointresearch.com>
2012-12-11 20:54 ` Re: Massive table bloat Craig James <cjames@emolecules.com>
0 siblings, 1 reply; 25+ messages in thread
From: Steve Crawford @ 2012-12-11 20:48 UTC (permalink / raw)
To: Michael Sawyers <msawyers@iii.com>; +Cc: pgsql-admin
On 12/11/2012 11:41 AM, Michael Sawyers wrote:
> Political reasons have ruled out the dump and reload options, but restoring
> the entire database took several hours. I'm also restricted on version
> because newer versions of postgres are not supported with that specific
> product, including maintenance updates.
You may want to gently suggest that the powers-that-be start at
http://www.postgresql.org/docs/8.4/static/release-8-4-15.html and work
their way back to
http://www.postgresql.org/docs/8.4/static/release-8-4-2.html compiling a
complete list of data-corrupting, query-wrecking or security-damaging
issues that have been corrected in the over three years since 8.4.1 was
released.
They may also want to look at
http://www.postgresql.org/support/versioning/ - particularly the
statement, "The community considers not upgrading to be riskier than
upgrading."
Ultimately it is their responsibility to make the decision whether or
not to upgrade. It is your responsibility to provide the data necessary
to help them make an informed choice.
Cheers,
Steve
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Massive table bloat
2012-12-11 16:11 Massive table bloat Michael Sawyers <msawyers@iii.com>
2012-12-11 18:25 ` Re: Massive table bloat Craig James <cjames@emolecules.com>
2012-12-11 19:41 ` Re: Massive table bloat Michael Sawyers <msawyers@iii.com>
2012-12-11 20:48 ` Re: Massive table bloat Steve Crawford <scrawford@pinpointresearch.com>
@ 2012-12-11 20:54 ` Craig James <cjames@emolecules.com>
2012-12-11 21:06 ` Re: Massive table bloat Michael Sawyers <msawyers@iii.com>
0 siblings, 1 reply; 25+ messages in thread
From: Craig James @ 2012-12-11 20:54 UTC (permalink / raw)
To: Steve Crawford <scrawford@pinpointresearch.com>; +Cc: Michael Sawyers <msawyers@iii.com>; pgsql-admin
On Tue, Dec 11, 2012 at 12:48 PM, Steve Crawford <
scrawford@pinpointresearch.com> wrote:
> On 12/11/2012 11:41 AM, Michael Sawyers wrote:
>
>> Political reasons have ruled out the dump and reload options, but
>> restoring
>> the entire database took several hours. I'm also restricted on version
>> because newer versions of postgres are not supported with that specific
>> product, including maintenance updates.
>>
>
> You may want to gently suggest that the powers-that-be start at
> http://www.postgresql.org/**docs/8.4/static/release-8-4-**15.html<http://www.postgresql.org/docs/... work their way back to
> http://www.postgresql.org/**docs/8.4/static/release-8-4-2.**html<http://www.postgresql.org/docs/8... a complete list of data-corrupting, query-wrecking or
> security-damaging issues that have been corrected in the over three years
> since 8.4.1 was released.
>
> They may also want to look at http://www.postgresql.org/**
> support/versioning/ <http://www.postgresql.org/support/versioning/; -
> particularly the statement, "The community considers not upgrading to be
> riskier than upgrading."
>
> Ultimately it is their responsibility to make the decision whether or not
> to upgrade. It is your responsibility to provide the data necessary to help
> them make an informed choice.
>
Often the reason for these technoligically stupid policies is third-party
software, where vendor X says, "we only support Postgres 8.3.2, using
anything else voids our service agreement." If that's the problem here,
then ask vendor X if they're willing to take responsibility for the
potential data corruption that their policy will cause. Vendors that work
with Postgres need to understand that "minor releases" won't break their
applications, and that they should encourage their customers to keep their
Postgres software up to date.
If, on the other hand, this is your company's internal policies, then pass
Steve's message along in with a strongly worded admonition that they pay
attention.
Craig James
>
> Cheers,
> Steve
>
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-admin<http://www.postgresql.org/mailpref/pgsql-admin;
>
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Massive table bloat
2012-12-11 16:11 Massive table bloat Michael Sawyers <msawyers@iii.com>
2012-12-11 18:25 ` Re: Massive table bloat Craig James <cjames@emolecules.com>
2012-12-11 19:41 ` Re: Massive table bloat Michael Sawyers <msawyers@iii.com>
2012-12-11 20:48 ` Re: Massive table bloat Steve Crawford <scrawford@pinpointresearch.com>
2012-12-11 20:54 ` Re: Massive table bloat Craig James <cjames@emolecules.com>
@ 2012-12-11 21:06 ` Michael Sawyers <msawyers@iii.com>
0 siblings, 0 replies; 25+ messages in thread
From: Michael Sawyers @ 2012-12-11 21:06 UTC (permalink / raw)
To: pgsql-admin
Thanks all for the feedback, especially on the topic of version. I plan on
pushing that whenever I have an opening.
But for now I've been placed in fire fighter mode with this one at the top
of the list (long story behind it but it involved massive customer service
issue on our end) so I need to be as sure as possible that a working plan is
in place and ready to go.
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Massive table bloat
2012-12-11 16:11 Massive table bloat Michael Sawyers <msawyers@iii.com>
@ 2012-12-11 19:35 ` Steve Crawford <scrawford@pinpointresearch.com>
2012-12-11 19:50 ` Re: Massive table bloat Michael Sawyers <msawyers@iii.com>
2 siblings, 1 reply; 25+ messages in thread
From: Steve Crawford @ 2012-12-11 19:35 UTC (permalink / raw)
To: pgsql-admin; +Cc: Michael Sawyers <msawyers@iii.com>
On 12/11/2012 08:11 AM, Michael Sawyers wrote:
> Our dba quit last week leaving me with an interesting problem.
>
> We have a table currently using 33gb worth of space for only 152mb worth of
> data because of bad processes or autovacuum not being aggressive enough. I
> was able to confirm the size difference by doing a create table as select
> along with a second test of restoring the table from the dump file to a dev
> machine.
>
>
>
> There is a very large list of foreign key relationships that I'm not
> including for the sake of brevity.
>
> The database version is 8.4.1
>
> The old DBA had said that vacuum full would take days to complete, and we
> don't have that much of a window. So I was considering using the to force
> a full table rewrite. In testing on a dev machine it only took about five
> minutes.
>
> I do not have as much hands on experience with postgres so I wanted to get
> thoughts on what is considered the proper way to deal with this kind of
> situation.
>
> Any comments would be welcome.
It's true that vacuum-full has historically been very slow as it
rewrites data in-place but you can use CLUSTER which is *WAY* faster
since it rewrites the data to a new file which it then drops back in
place. CLUSTER also gives you new clean indexes. In fact the current
versions of PostgreSQL basically use the CLUSTER process to handle
VACUUM FULL. (I just don't recall which version applied that change.)
How long did your test copy/dump/restore steps take? This will give you
a *rough* idea of the time that CLUSTER will take.
Really the only downside of CLUSTER is the requirement that you have
enough available space to write the new copy of the table/indexes.
Another option if you can't tolerate downtime is pg_reorg
(http://pgfoundry.org/projects/reorg/) or its fork pg_repack
(https://github.com/reorg/pg_repack) which essentially do the copy to a
new table in the background then briefly lock the tables to apply any
recent changes from the master before dropping the copy into place.
Cheers,
Steve
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Massive table bloat
2012-12-11 16:11 Massive table bloat Michael Sawyers <msawyers@iii.com>
2012-12-11 19:35 ` Re: Massive table bloat Steve Crawford <scrawford@pinpointresearch.com>
@ 2012-12-11 19:50 ` Michael Sawyers <msawyers@iii.com>
0 siblings, 0 replies; 25+ messages in thread
From: Michael Sawyers @ 2012-12-11 19:50 UTC (permalink / raw)
To: pgsql-admin
The dump takes about 30 minutes and restore on an older dev machine took
several hours to complete.
I did create a cluster on the restored (un-bloated) table and it finished in
~10 minutes and I should have space for the extra copies since the actual
data is very small.
I had looked at pg_reorg, but the bosses don't want me using something that
hasn't been cleared, otherwise it looks pretty handy to have.
Thanks for the point about cluster being the current vacuum full, and for
the opinion in general. Someday I hope all our customer can use a more up
to date version and I'll be making it more a point if I end up filling some
DBA role here officially.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Massive-table-bloat-tp5736111p5736151.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Massive table bloat
2012-12-11 16:11 Massive table bloat Michael Sawyers <msawyers@iii.com>
@ 2012-12-11 21:09 ` Sergey Konoplev <gray.ru@gmail.com>
2012-12-11 21:12 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-11 21:14 ` Re: Massive table bloat Michael Sawyers <msawyers@iii.com>
2 siblings, 2 replies; 25+ messages in thread
From: Sergey Konoplev @ 2012-12-11 21:09 UTC (permalink / raw)
To: Michael Sawyers <msawyers@iii.com>; +Cc: pgsql-admin
On Tue, Dec 11, 2012 at 8:11 AM, Michael Sawyers <msawyers@iii.com> wrote:
> We have a table currently using 33gb worth of space for only 152mb worth of
> data because of bad processes or autovacuum not being aggressive enough. I
> was able to confirm the size difference by doing a create table as select
> along with a second test of restoring the table from the dump file to a dev
> machine.
You can use pgcompactor (http://code.google.com/p/pgtoolkit/) to
gently reduce bloat. It does not use heavy locks, processes both
tables and indexes, determines if one is bloated and tested on 8.4.
>
>
>
> There is a very large list of foreign key relationships that I'm not
> including for the sake of brevity.
>
> The database version is 8.4.1
>
> The old DBA had said that vacuum full would take days to complete, and we
> don't have that much of a window. So I was considering using the to force
> a full table rewrite. In testing on a dev machine it only took about five
> minutes.
>
> I do not have as much hands on experience with postgres so I wanted to get
> thoughts on what is considered the proper way to deal with this kind of
> situation.
>
> Any comments would be welcome.
>
>
>
>
> --
> View this message in context: http://postgresql.1045698.n5.nabble.com/Massive-table-bloat-tp5736111.html
> Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
--
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Massive table bloat
2012-12-11 16:11 Massive table bloat Michael Sawyers <msawyers@iii.com>
2012-12-11 21:09 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
@ 2012-12-11 21:12 ` Sergey Konoplev <gray.ru@gmail.com>
1 sibling, 0 replies; 25+ messages in thread
From: Sergey Konoplev @ 2012-12-11 21:12 UTC (permalink / raw)
To: Michael Sawyers <msawyers@iii.com>; +Cc: pgsql-admin
On Tue, Dec 11, 2012 at 1:09 PM, Sergey Konoplev <gray.ru@gmail.com> wrote:
> On Tue, Dec 11, 2012 at 8:11 AM, Michael Sawyers <msawyers@iii.com> wrote:
>> We have a table currently using 33gb worth of space for only 152mb worth of
>> data because of bad processes or autovacuum not being aggressive enough. I
>> was able to confirm the size difference by doing a create table as select
>> along with a second test of restoring the table from the dump file to a dev
>> machine.
>
> You can use pgcompactor (http://code.google.com/p/pgtoolkit/) to
> gently reduce bloat. It does not use heavy locks, processes both
> tables and indexes, determines if one is bloated and tested on 8.4.
Sorry, forget to mention. It does not need an extra space and does not
required a service window.
--
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Massive table bloat
2012-12-11 16:11 Massive table bloat Michael Sawyers <msawyers@iii.com>
2012-12-11 21:09 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
@ 2012-12-11 21:14 ` Michael Sawyers <msawyers@iii.com>
2012-12-11 21:27 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
1 sibling, 1 reply; 25+ messages in thread
From: Michael Sawyers @ 2012-12-11 21:14 UTC (permalink / raw)
To: pgsql-admin
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.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Massive-table-bloat-tp5736111p5736180.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Massive table bloat
2012-12-11 16:11 Massive table bloat Michael Sawyers <msawyers@iii.com>
2012-12-11 21:09 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-11 21:14 ` Re: Massive table bloat Michael Sawyers <msawyers@iii.com>
@ 2012-12-11 21:27 ` Sergey Konoplev <gray.ru@gmail.com>
2012-12-11 21:38 ` Re: Massive table bloat Michael Sawyers <msawyers@iii.com>
2012-12-12 03:40 ` Re: Massive table bloat Rural Hunter <ruralhunter@gmail.com>
0 siblings, 2 replies; 25+ messages in thread
From: Sergey Konoplev @ 2012-12-11 21:27 UTC (permalink / raw)
To: Michael Sawyers <msawyers@iii.com>; +Cc: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Massive table bloat
2012-12-11 16:11 Massive table bloat Michael Sawyers <msawyers@iii.com>
2012-12-11 21:09 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-11 21:14 ` Re: Massive table bloat Michael Sawyers <msawyers@iii.com>
2012-12-11 21:27 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
@ 2012-12-11 21:38 ` Michael Sawyers <msawyers@iii.com>
1 sibling, 0 replies; 25+ messages in thread
From: Michael Sawyers @ 2012-12-11 21:38 UTC (permalink / raw)
To: pgsql-admin
I have no doubt about the tool, I have doubt about managements willingness to let me start using it. People here are very risk adverse, and sometimes that means difficulty in getting improved technology in the door. It happens of course, it is just slower than one might hope.
Once I have proven it on internal test systems I'm sure I can get the discussion started on including it in our normal processes, but it would be my head to use it on a live customer db for my first run.
Michael
From: Sergey Konoplev-2 [via PostgreSQL] [mailto:ml-node+s1045698n5736184h99@n5.nabble.com]
Sent: Tuesday, December 11, 2012 1:29 PM
To: Michael Sawyers
Subject: Re: Massive table bloat
On Tue, Dec 11, 2012 at 1:14 PM, Michael Sawyers <[hidden email]</user/SendEmail.jtp?type=node&node=5736184&i=0>> 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: [hidden email]</user/SendEmail.jtp?type=node&node=5736184&i=1>
--
Sent via pgsql-admin mailing list ([hidden email]</user/SendEmail.jtp?type=node&node=5736184&i=2>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
________________________________
If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/Massive-table-bloat-tp5736111p5736184.html
To unsubscribe from Massive table bloat, click here<http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=...;.
NAML<http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_h...;
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Massive-table-bloat-tp5736111p5736186.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Massive table bloat
2012-12-11 16:11 Massive table bloat Michael Sawyers <msawyers@iii.com>
2012-12-11 21:09 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-11 21:14 ` Re: Massive table bloat Michael Sawyers <msawyers@iii.com>
2012-12-11 21:27 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
@ 2012-12-12 03:40 ` Rural Hunter <ruralhunter@gmail.com>
2012-12-12 03:46 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
1 sibling, 1 reply; 25+ messages in thread
From: Rural Hunter @ 2012-12-12 03:40 UTC (permalink / raw)
To: Sergey Konoplev <gray.ru@gmail.com>; +Cc: pgsql-admin
Hi Sergey,
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.
于 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
>
>
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Massive table bloat
2012-12-11 16:11 Massive table bloat Michael Sawyers <msawyers@iii.com>
2012-12-11 21:09 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-11 21:14 ` Re: Massive table bloat Michael Sawyers <msawyers@iii.com>
2012-12-11 21:27 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 03:40 ` Re: Massive table bloat Rural Hunter <ruralhunter@gmail.com>
@ 2012-12-12 03:46 ` Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 03:57 ` Re: Massive table bloat Rural Hunter <ruralhunter@gmail.com>
0 siblings, 1 reply; 25+ messages in thread
From: Sergey Konoplev @ 2012-12-12 03:46 UTC (permalink / raw)
To: Rural Hunter <ruralhunter@gmail.com>; +Cc: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Massive table bloat
2012-12-11 16:11 Massive table bloat Michael Sawyers <msawyers@iii.com>
2012-12-11 21:09 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-11 21:14 ` Re: Massive table bloat Michael Sawyers <msawyers@iii.com>
2012-12-11 21:27 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 03:40 ` Re: Massive table bloat Rural Hunter <ruralhunter@gmail.com>
2012-12-12 03:46 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
@ 2012-12-12 03:57 ` Rural Hunter <ruralhunter@gmail.com>
2012-12-12 04:24 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
0 siblings, 1 reply; 25+ messages in thread
From: Rural Hunter @ 2012-12-12 03:57 UTC (permalink / raw)
To: Sergey Konoplev <gray.ru@gmail.com>; +Cc: pgsql-admin
I do have psql installed. I'm on the db server.
$ psql
psql.bin (9.1.0)
Type "help" for help.
postgres=# \q
$ ./pgcompactor -a -u
DatabaseChooserError Can not find an adapter. at
/loader/0x2539f18/PgToolkit/DatabaseChooser.pm line 63.
于 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
>
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Massive table bloat
2012-12-11 16:11 Massive table bloat Michael Sawyers <msawyers@iii.com>
2012-12-11 21:09 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-11 21:14 ` Re: Massive table bloat Michael Sawyers <msawyers@iii.com>
2012-12-11 21:27 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 03:40 ` Re: Massive table bloat Rural Hunter <ruralhunter@gmail.com>
2012-12-12 03:46 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 03:57 ` Re: Massive table bloat Rural Hunter <ruralhunter@gmail.com>
@ 2012-12-12 04:24 ` Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 04:30 ` Re: Massive table bloat Rural Hunter <ruralhunter@gmail.com>
0 siblings, 1 reply; 25+ messages in thread
From: Sergey Konoplev @ 2012-12-12 04:24 UTC (permalink / raw)
To: Rural Hunter <ruralhunter@gmail.com>; +Cc: pgsql-admin
On Tue, Dec 11, 2012 at 7:57 PM, Rural Hunter <ruralhunter@gmail.com> wrote:
> I do have psql installed. I'm on the db server.
> $ psql
> psql.bin (9.1.0)
> Type "help" for help.
>
> postgres=# \q
> $ ./pgcompactor -a -u
> DatabaseChooserError Can not find an adapter. at
> /loader/0x2539f18/PgToolkit/DatabaseChooser.pm line 63.
You are running it with user postgres, correct?
Can you show me the output of the following command, please?
echo 'SELECT 1;' | psql -q -A -t -X -U postgres -P null="<NULL>"
>
> 于 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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Massive table bloat
2012-12-11 16:11 Massive table bloat Michael Sawyers <msawyers@iii.com>
2012-12-11 21:09 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-11 21:14 ` Re: Massive table bloat Michael Sawyers <msawyers@iii.com>
2012-12-11 21:27 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 03:40 ` Re: Massive table bloat Rural Hunter <ruralhunter@gmail.com>
2012-12-12 03:46 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 03:57 ` Re: Massive table bloat Rural Hunter <ruralhunter@gmail.com>
2012-12-12 04:24 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
@ 2012-12-12 04:30 ` Rural Hunter <ruralhunter@gmail.com>
2012-12-12 04:47 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
0 siblings, 1 reply; 25+ messages in thread
From: Rural Hunter @ 2012-12-12 04:30 UTC (permalink / raw)
To: Sergey Konoplev <gray.ru@gmail.com>; +Cc: pgsql-admin
于 2012/12/12 12:24, Sergey Konoplev 写道:
> On Tue, Dec 11, 2012 at 7:57 PM, Rural Hunter <ruralhunter@gmail.com> wrote:
>> I do have psql installed. I'm on the db server.
>> $ psql
>> psql.bin (9.1.0)
>> Type "help" for help.
>>
>> postgres=# \q
>> $ ./pgcompactor -a -u
>> DatabaseChooserError Can not find an adapter. at
>> /loader/0x2539f18/PgToolkit/DatabaseChooser.pm line 63.
> You are running it with user postgres, correct?
>
> Can you show me the output of the following command, please?
>
> echo 'SELECT 1;' | psql -q -A -t -X -U postgres -P null="<NULL>"
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
>
>> 于 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
>
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Massive table bloat
2012-12-11 16:11 Massive table bloat Michael Sawyers <msawyers@iii.com>
2012-12-11 21:09 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-11 21:14 ` Re: Massive table bloat Michael Sawyers <msawyers@iii.com>
2012-12-11 21:27 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 03:40 ` Re: Massive table bloat Rural Hunter <ruralhunter@gmail.com>
2012-12-12 03:46 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 03:57 ` Re: Massive table bloat Rural Hunter <ruralhunter@gmail.com>
2012-12-12 04:24 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 04:30 ` Re: Massive table bloat Rural Hunter <ruralhunter@gmail.com>
@ 2012-12-12 04:47 ` Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 05:21 ` Re: Massive table bloat Rural Hunter <ruralhunter@gmail.com>
0 siblings, 1 reply; 25+ messages in thread
From: Sergey Konoplev @ 2012-12-12 04:47 UTC (permalink / raw)
To: Rural Hunter <ruralhunter@gmail.com>; +Cc: pgsql-admin
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.
>
>
>>
>>> 于 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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Massive table bloat
2012-12-11 16:11 Massive table bloat Michael Sawyers <msawyers@iii.com>
2012-12-11 21:09 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-11 21:14 ` Re: Massive table bloat Michael Sawyers <msawyers@iii.com>
2012-12-11 21:27 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 03:40 ` Re: Massive table bloat Rural Hunter <ruralhunter@gmail.com>
2012-12-12 03:46 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 03:57 ` Re: Massive table bloat Rural Hunter <ruralhunter@gmail.com>
2012-12-12 04:24 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 04:30 ` Re: Massive table bloat Rural Hunter <ruralhunter@gmail.com>
2012-12-12 04:47 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
@ 2012-12-12 05:21 ` Rural Hunter <ruralhunter@gmail.com>
2012-12-12 05:31 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
0 siblings, 1 reply; 25+ messages in thread
From: Rural Hunter @ 2012-12-12 05:21 UTC (permalink / raw)
To: Sergey Konoplev <gray.ru@gmail.com>; +Cc: 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
>
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Massive table bloat
2012-12-11 16:11 Massive table bloat Michael Sawyers <msawyers@iii.com>
2012-12-11 21:09 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-11 21:14 ` Re: Massive table bloat Michael Sawyers <msawyers@iii.com>
2012-12-11 21:27 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 03:40 ` Re: Massive table bloat Rural Hunter <ruralhunter@gmail.com>
2012-12-12 03:46 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 03:57 ` Re: Massive table bloat Rural Hunter <ruralhunter@gmail.com>
2012-12-12 04:24 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 04:30 ` Re: Massive table bloat Rural Hunter <ruralhunter@gmail.com>
2012-12-12 04:47 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 05:21 ` Re: Massive table bloat Rural Hunter <ruralhunter@gmail.com>
@ 2012-12-12 05:31 ` Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 05:39 ` Re: Massive table bloat Rural Hunter <ruralhunter@gmail.com>
0 siblings, 1 reply; 25+ messages in thread
From: Sergey Konoplev @ 2012-12-12 05:31 UTC (permalink / raw)
To: Rural Hunter <ruralhunter@gmail.com>; +Cc: pgsql-admin
On Tue, Dec 11, 2012 at 9:21 PM, Rural Hunter <ruralhunter@gmail.com> wrote:
> 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
Yes. It is known bug and it is fixed in the future (not yet released) version.
See the attachment.
--
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
Attachments:
pgtoolkit-v1.0rc1-fatpack.tar.gz (application/x-gzip, 20.2K) download
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Massive table bloat
2012-12-11 16:11 Massive table bloat Michael Sawyers <msawyers@iii.com>
2012-12-11 21:09 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-11 21:14 ` Re: Massive table bloat Michael Sawyers <msawyers@iii.com>
2012-12-11 21:27 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 03:40 ` Re: Massive table bloat Rural Hunter <ruralhunter@gmail.com>
2012-12-12 03:46 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 03:57 ` Re: Massive table bloat Rural Hunter <ruralhunter@gmail.com>
2012-12-12 04:24 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 04:30 ` Re: Massive table bloat Rural Hunter <ruralhunter@gmail.com>
2012-12-12 04:47 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 05:21 ` Re: Massive table bloat Rural Hunter <ruralhunter@gmail.com>
2012-12-12 05:31 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
@ 2012-12-12 05:39 ` Rural Hunter <ruralhunter@gmail.com>
2012-12-12 05:44 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
0 siblings, 1 reply; 25+ messages in thread
From: Rural Hunter @ 2012-12-12 05:39 UTC (permalink / raw)
To: Sergey Konoplev <gray.ru@gmail.com>; +Cc: pgsql-admin
于 2012/12/12 13:31, Sergey Konoplev 写道:
> Yes. It is known bug and it is fixed in the future (not yet released) version.
>
> See the attachment.
Great. It works now. Thanks a lot for your instant help!
>
> --
> 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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Massive table bloat
2012-12-11 16:11 Massive table bloat Michael Sawyers <msawyers@iii.com>
2012-12-11 21:09 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-11 21:14 ` Re: Massive table bloat Michael Sawyers <msawyers@iii.com>
2012-12-11 21:27 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 03:40 ` Re: Massive table bloat Rural Hunter <ruralhunter@gmail.com>
2012-12-12 03:46 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 03:57 ` Re: Massive table bloat Rural Hunter <ruralhunter@gmail.com>
2012-12-12 04:24 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 04:30 ` Re: Massive table bloat Rural Hunter <ruralhunter@gmail.com>
2012-12-12 04:47 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 05:21 ` Re: Massive table bloat Rural Hunter <ruralhunter@gmail.com>
2012-12-12 05:31 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 05:39 ` Re: Massive table bloat Rural Hunter <ruralhunter@gmail.com>
@ 2012-12-12 05:44 ` Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 13:44 ` Re: Massive table bloat Rural Hunter <ruralhunter@gmail.com>
0 siblings, 1 reply; 25+ messages in thread
From: Sergey Konoplev @ 2012-12-12 05:44 UTC (permalink / raw)
To: Rural Hunter <ruralhunter@gmail.com>; +Cc: pgsql-admin
On Tue, Dec 11, 2012 at 9:39 PM, Rural Hunter <ruralhunter@gmail.com> wrote:
> Great. It works now. Thanks a lot for your instant help!
You are welcome.
Thanks for your feedback and sorry for this bugs. I have noted down
this issue with password and planned to add .pg* and env support.
--
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Massive table bloat
2012-12-11 16:11 Massive table bloat Michael Sawyers <msawyers@iii.com>
2012-12-11 21:09 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-11 21:14 ` Re: Massive table bloat Michael Sawyers <msawyers@iii.com>
2012-12-11 21:27 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 03:40 ` Re: Massive table bloat Rural Hunter <ruralhunter@gmail.com>
2012-12-12 03:46 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 03:57 ` Re: Massive table bloat Rural Hunter <ruralhunter@gmail.com>
2012-12-12 04:24 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 04:30 ` Re: Massive table bloat Rural Hunter <ruralhunter@gmail.com>
2012-12-12 04:47 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 05:21 ` Re: Massive table bloat Rural Hunter <ruralhunter@gmail.com>
2012-12-12 05:31 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 05:39 ` Re: Massive table bloat Rural Hunter <ruralhunter@gmail.com>
2012-12-12 05:44 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
@ 2012-12-12 13:44 ` Rural Hunter <ruralhunter@gmail.com>
2013-02-02 07:56 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
0 siblings, 1 reply; 25+ messages in thread
From: Rural Hunter @ 2012-12-12 13:44 UTC (permalink / raw)
To: Sergey Konoplev <gray.ru@gmail.com>; +Cc: pgsql-admin
于 2012/12/12 13:44, Sergey Konoplev 写道:
> On Tue, Dec 11, 2012 at 9:39 PM, Rural Hunter <ruralhunter@gmail.com> wrote:
>> Great. It works now. Thanks a lot for your instant help!
> You are welcome.
>
> Thanks for your feedback and sorry for this bugs. I have noted down
> this issue with password and planned to add .pg* and env support.
No problem at all and thanks for the wonderful tool!
>
> --
> 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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Massive table bloat
2012-12-11 16:11 Massive table bloat Michael Sawyers <msawyers@iii.com>
2012-12-11 21:09 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-11 21:14 ` Re: Massive table bloat Michael Sawyers <msawyers@iii.com>
2012-12-11 21:27 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 03:40 ` Re: Massive table bloat Rural Hunter <ruralhunter@gmail.com>
2012-12-12 03:46 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 03:57 ` Re: Massive table bloat Rural Hunter <ruralhunter@gmail.com>
2012-12-12 04:24 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 04:30 ` Re: Massive table bloat Rural Hunter <ruralhunter@gmail.com>
2012-12-12 04:47 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 05:21 ` Re: Massive table bloat Rural Hunter <ruralhunter@gmail.com>
2012-12-12 05:31 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 05:39 ` Re: Massive table bloat Rural Hunter <ruralhunter@gmail.com>
2012-12-12 05:44 ` Re: Massive table bloat Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 13:44 ` Re: Massive table bloat Rural Hunter <ruralhunter@gmail.com>
@ 2013-02-02 07:56 ` Sergey Konoplev <gray.ru@gmail.com>
0 siblings, 0 replies; 25+ messages in thread
From: Sergey Konoplev @ 2013-02-02 07:56 UTC (permalink / raw)
To: Rural Hunter <ruralhunter@gmail.com>; Maxim Boguk <maxim.boguk@gmail.com>; Lonni Friedman <netllama@gmail.com>; +Cc: pgsql-admin
Hi all,
For those who are interested in pgcompactor - v1.0rc1 is out.
It contains a lot of improvements and has already been tested on a
plenty of databases.
The list of changes is below:
2013-02-01 v1.0rc1
- Refactored information files, PgToolkit is released under the
PostgreSQL License now
- Improved error messages, help hints and options' warnings
- Added -V (--version) functionality
- Fixed the bug with storage parameters on tables and indexes
- Removed useless information from compacting results
- Added bloat information to the messages about reindex impossibility
- Made sizes pretty printed (kB, MB, GB, TB)
- Moved skipping messages to the INFO level
- Fixed the infinity loop on the size change check bug
- Fixed the bug when reindex is skipped if table was not compacted but
will be skipped the next round
- Fixed the bug of reindexing when --dry-run is specified
- Optimized the pgstattuple based bloat calculation
- Refactored autonomous scripts building facilities, now the scripts
are available straight from the fatpack/ directory
- Fixed the error when 0 or 1 pages left
- Fix the silent --man and --help problem
- Separated completion statistics and warnings
- Added a basic processing of the cases with tables/indexes deletion
in the process of compacting
- Fixed the reindex syntax and added a comment with database name
- Fixed the partial indexes reindexing
- Increased verbosity on connection errors (thanks to ruralhunter)
- Made it use .pgpass and environment variables (thanks to
ruralhunter)
- Refactored the psql adapter to bidirectional communication what
increased processing speed dramatically
- Got rid of the final exception in the cleaning stored function
(thanks to Lonni Friedman)
On Wed, Dec 12, 2012 at 5:44 AM, Rural Hunter <ruralhunter@gmail.com> wrote:
> 于 2012/12/12 13:44, Sergey Konoplev 写道:
>
>> On Tue, Dec 11, 2012 at 9:39 PM, Rural Hunter <ruralhunter@gmail.com>
>> wrote:
>>>
>>> Great. It works now. Thanks a lot for your instant help!
>>
>> You are welcome.
>>
>> Thanks for your feedback and sorry for this bugs. I have noted down
>> this issue with password and planned to add .pg* and env support.
>
> No problem at all and thanks for the wonderful tool!
>
>>
>> --
>> 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
^ permalink raw reply [nested|flat] 25+ messages in thread
end of thread, other threads:[~2013-02-02 07:56 UTC | newest]
Thread overview: 25+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-12-11 16:11 Massive table bloat Michael Sawyers <msawyers@iii.com>
2012-12-11 18:25 ` Craig James <cjames@emolecules.com>
2012-12-11 19:41 ` Michael Sawyers <msawyers@iii.com>
2012-12-11 20:48 ` Steve Crawford <scrawford@pinpointresearch.com>
2012-12-11 20:54 ` Craig James <cjames@emolecules.com>
2012-12-11 21:06 ` Michael Sawyers <msawyers@iii.com>
2012-12-11 19:35 ` Steve Crawford <scrawford@pinpointresearch.com>
2012-12-11 19:50 ` Michael Sawyers <msawyers@iii.com>
2012-12-11 21:09 ` Sergey Konoplev <gray.ru@gmail.com>
2012-12-11 21:12 ` Sergey Konoplev <gray.ru@gmail.com>
2012-12-11 21:14 ` Michael Sawyers <msawyers@iii.com>
2012-12-11 21:27 ` Sergey Konoplev <gray.ru@gmail.com>
2012-12-11 21:38 ` Michael Sawyers <msawyers@iii.com>
2012-12-12 03:40 ` Rural Hunter <ruralhunter@gmail.com>
2012-12-12 03:46 ` Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 03:57 ` Rural Hunter <ruralhunter@gmail.com>
2012-12-12 04:24 ` Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 04:30 ` Rural Hunter <ruralhunter@gmail.com>
2012-12-12 04:47 ` Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 05:21 ` Rural Hunter <ruralhunter@gmail.com>
2012-12-12 05:31 ` Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 05:39 ` Rural Hunter <ruralhunter@gmail.com>
2012-12-12 05:44 ` Sergey Konoplev <gray.ru@gmail.com>
2012-12-12 13:44 ` Rural Hunter <ruralhunter@gmail.com>
2013-02-02 07:56 ` Sergey Konoplev <gray.ru@gmail.com>
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