Compression table does not occur

[ghost]

Hi,

I run compression procedure is started with the command:

[root@hostname]# perl /tmp/pgcompacttable-master/bin/pgcompacttable -U postgres -d database -t table
Thu Oct 29 10:37:55 2015 Connecting to database
Thu Oct 29 10:37:55 2015 Postgress backend pid: 25779
Thu Oct 29 10:37:56 2015 Handling tables. Attempt 1
Thu Oct 29 14:14:51 2015 Statistics: 48059264 pages (65754320 pages including toasts and indexes) , approximately 19.240% (9249084 pages) can be compacted reducing the size by 70.565GB.
Thu Oct 29 14:21:22 2015 Reindex queries: public.Таблицаtable_pkey, initial size 2882369 pages (21.991GB), will be reduced by 51% (11.328GB)
Thu Oct 29 14:21:22 2015 CREATE UNIQUE INDEX CONCURRENTLY pgcompact_index_25771 ON table USING btree (id); --database
Thu Oct 29 14:21:22 2015 BEGIN; SET LOCAL statement_timeout TO 1000;
ALTER TABLE "public"."table" DROP CONSTRAINT "Таблицаtable_pkey";
ALTER TABLE "public"."table" ADD CONSTRAINT "Таблицаtable_pkey" PRIMARY KEY USING INDEX pgcompact_index_25771;
END;; --database
Thu Oct 29 14:24:08 2015 Processing complete.
Thu Oct 29 14:24:08 2015 Processing results: size reduced by 256.000KB (-130.172MB including toasts and indexes) in total.
Thu Oct 29 14:24:08 2015 Disconnecting from database
[Thu Oct 29 14:24:08 2015] Processing complete: 1 retries to process has been done
[Thu Oct 29 14:24:08 2015] Processing results: size reduced by 256.000KB (-130.172MB including toasts and indexes) in total, 256.000KB (-130.172MB) database.

Why not start the procedures for data compression and not run for re-indexing procedure ?

Thank you.

[rvoronin]

Hi,

If you do not need to re-index, you should use '-r' or '--no-reindex' parameter then.

Best wishes.

[ghost]

You understood me wrong.
I want to reindexing table and table size has decreased.
"Statistics: 48059264 pages (65754320 pages including toasts and indexes) , approximately 19.240% (9249084 pages) can be compacted reducing the size by 70.565GB."
Now the process hangs and crashes 4:00 ...
I sent the entire log . Why is this happening?

[MaximBoguk]

On Thu, Oct 29, 2015 at 2:29 PM, Pavel Bobrovnikov <[email protected]

wrote:

Hi,

I run compression procedure is started with the command:

Thu Oct 29 14:14:51 2015 Statistics: 48059264 pages (65754320 pages
including toasts and indexes) ,
​​
approximately 19.240%(9249084 pages)
​​
can be compacted reducing the size by 70.565GB.

Why not start the procedures for data compression and not run for
re-indexing procedure ?

Thank you.​

​The key issue here: ​​approximately 19.240% ​can be compacted, it's less
that minimal bloat 20% required to start compaction procedure in normal
mode.

If you would like to compress table anyway you should use force mode:
-f
--force
Try to compact even those tables and indexes that do not meet
minimal bloat requirements.​

Maxim Boguk
Senior Postgresql DBA
http://www.postgresql-consulting.ru/ http://www.postgresql-consulting.com/

Phone RU: +7 910 405 4718
Phone AU: +61 45 218 5678

LinkedIn: http://www.linkedin.com/pub/maksym-boguk/80/b99/b1b
Skype: maxim.boguk
Jabber: [email protected]

"People problems are solved with people.
If people cannot solve the problem, try technology.
People will then wish they'd listened at the first stage."

[MaximBoguk]

On Thu, Oct 29, 2015 at 2:38 PM, Pavel Bobrovnikov <[email protected]

wrote:

You understood me wrong.
I want to reindexing table and table size has decreased.
Now the process hangs and crashes 4:00 ...
I sent the entire log . Why is this happening?

​Hi,
I don't see any signs of crash (or possible other issues) in provided log.
It's look like pgcompacttable had finished successfully.
​
​If you have skype you cound contact me on skype maxim.boguk
or jabber/gtalk [email protected] ​to discuss exact problem.

Maxim Boguk
Senior Postgresql DBA
http://www.postgresql-consulting.ru/ http://www.postgresql-consulting.com/

Phone RU: +7 910 405 4718
Phone AU: +61 45 218 5678

LinkedIn: http://www.linkedin.com/pub/maksym-boguk/80/b99/b1b
Skype: maxim.boguk
Jabber: [email protected]

"People problems are solved with people.
If people cannot solve the problem, try technology.
People will then wish they'd listened at the first stage."