Обсуждение: Massive table bloat
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.
On Tue, Dec 11, 2012 at 8:11 AM, Michael Sawyers <msawyers@iii.com> wrote:
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.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.
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
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
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.
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.
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
On Tue, Dec 11, 2012 at 12:48 PM, Steve Crawford <scrawford@pinpointresearch.com> wrote:
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
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.
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
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. From the earlier comments it sounds like using clustering on the table is the best way to go since it will compact the space used for data, and is even the way that current postgres versions go about doing a vacuum full. The downsides are that it would require a service window because of locking the table, and that it would need extra space to build the temporary copy of the table while things are being indexed. All that is something that I can work with and be able to do the work without restoring from dump, but of course I plan to make a fresh dump file before I start, just in case. I'm very grateful to all of you that have taken the time to give your opinions and advice and I'm feeling positive about this work. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Massive-table-bloat-tp5736111p5736176.html Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
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
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
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.
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
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+[hidden email]]
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]> 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]
--
Sent via pgsql-admin mailing list ([hidden email])
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.
NAML
View this message in context: RE: Massive table bloat
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
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 > >
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
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 >
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
于 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 >
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
于 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
>
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
Вложения
于 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
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
于 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 >
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