Is there a way to use another drive for processing tables/indexes?

[proton]

For example: I have one drive with database (full at 99%) and another drive (almost free).

When I run pgcompacttable, I get messages like:

Skipping processing: 10.38% space to compact from 20% minimum required.

Is there a way to use another (free) drive for processing tables/indexes?

[MaximBoguk]

Hi,

Message "Skipping processing: 10.38% space to compact from 20% minimum required." isn't about available disk space but about available space inside an index. If index have less than 20% free space - there are usually no sense to try compact it. pgcompacttable doesn't check available disk space at all.

Kind Regards, Maksym

On Sat, Jun 13, 2015 at 4:15 PM, Peter Savichev notifications@github.com wrote:

For example: I have one drive with database (full at 99%) and another drive (almost free).

When I run pgcompacttable, I get messages like:

Skipping processing: 10.38% space to compact from 20% minimum required.

Is there a way to use another (free) drive for processing tables/indexes?

— Reply to this email directly or view it on GitHub https://github.com/PostgreSQL-Consulting/pgcompacttable/issues/1.

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: maxim.boguk@gmail.com МойКруг: http://mboguk.moikrug.ru/

"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."

[proton]

Thank you!

[ghost]

Hello.

I understand that if such a message is issued, which means that the table should be cleaned by archive values to reduce the size ?!

[MaximBoguk]

On Tue, Jun 16, 2015 at 5:55 PM, Pavel Bobrovnikov <notifications@github.com

wrote:

I understand that if such a message is issued, which means that the table should be cleaned simply by archive values to reduce the size ?!

I not sure what you mean in this statement.​ This message mean that there not enough free space inside table to justify compaction. If you would like try compact it anyway there are --force flag for it.

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: maxim.boguk@gmail.com МойКруг: http://mboguk.moikrug.ru/

"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."

[ghost]

My example: zabbix-server + pgsql (9.3.5) --force flag does't help.

root@psql-1:~# df -h Filesystem Size Used Avail Use% Mounted on /dev/mapper/vg_root-lv_root 37G 1.8G 34G 5% / tmpfs 4.9G 0 4.9G 0% /dev/shm /dev/sda1 485M 32M 428M 7% /boot /dev/mapper/vg_data01-postgre 197G 161G 27G 86% /var/lib/pgsql

root@psql-1:~# perl /tmp/pgcompacttable-master/bin/pgcompacttable -h * -U postgres -W * -d zabbix-server -t history_uint -v -f Tue Jun 16 10:59:01 2015 Connecting to database Tue Jun 16 10:59:01 2015 Postgress backend pid: 16711 Tue Jun 16 10:59:01 2015 Handling tables. Attempt 1 Tue Jun 16 10:59:01 2015 Start handling table public.history_uint Tue Jun 16 13:14:09 2015 Vacuum initial: 4527752 pages left, duration 8107.094 seconds. Tue Jun 16 13:32:18 2015 Bloat statistics with pgstattuple: duration 1089.068 seconds. Tue Jun 16 13:32:18 2015 Statistics: 4527752 pages (10257536 pages including toasts and indexes) , approximately 7.030% (318678 pages) can be compacted reducing the size by 2.431GB. Tue Jun 16 13:32:18 2015 Skipping processing: 7.03% space to compact from 20% minimum required. Tue Jun 16 13:32:19 2015 Cannot get index size statistics. Tue Jun 16 13:32:19 2015 Finish handling table public.history_uint

[MaximBoguk]

On Tue, Jun 16, 2015 at 9:30 PM, Pavel Bobrovnikov <notifications@github.com

wrote:

My example: zabbix-server + pgsql (9.3.5) --force flag does't help.

root@psql-1:~# df -h Filesystem Size Used Avail Use% Mounted on /dev/mapper/vg_root-lv_root 37G 1.8G 34G 5% / tmpfs 4.9G 0 4.9G 0% /dev/shm /dev/sda1 485M 32M 428M 7% /boot /dev/mapper/vg_data01-postgre 197G 161G 27G 86% /var/lib/pgsql

root@psql-1:~# perl /tmp/pgcompacttable-master/bin/pgcompacttable -h * -U postgres -W * -d zabbix-server -t history_uint -v -f Tue Jun 16 10:59:01 2015 http://zabbix-server Connecting to database Tue Jun 16 10:59:01 2015 http://zabbix-server Postgress backend pid: 16711 Tue Jun 16 10:59:01 2015 http://zabbix-server Handling tables. Attempt 1 Tue Jun 16 10:59:01 2015 Start handling table ​​ public. ​​ ​​ history_uint Tue Jun 16 13:14:09 2015 Vacuum initial: 4527752 pages left, duration 8107.094 seconds. Tue Jun 16 13:32:18 2015 Bloat statistics with pgstattuple: duration 1089.068 seconds. Tue Jun 16 13:32:18 2015 Statistics: 4527752 pages (10257536 pages including toasts and indexes) , approximately 7.030% (318678 pages) can be compacted reducing the size by 2.431GB. Tue Jun 16 13:32:18 2015 Skipping processing: 7.03% space to compact from 20% minimum required. Tue Jun 16 13:32:19 2015 Cannot get index size statistics. Tue Jun 16 13:32:19 2015 Finish handling table public.history_uint

— Reply to this email directly or view it on GitHub https://github.com/PostgreSQL-Consulting/pgcompacttable/issues/1#issuecomment-112391510 .

​It's weird. ​Could you send me a results of ​\d+ ​public.history_uint from the zabbix database?

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: maxim.boguk@gmail.com МойКруг: http://mboguk.moikrug.ru/

"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."

[ghost]

zabbix-server=# \d+ history_uint; Table "public.history_uint" Column | Type | Modifiers | Storage | Stats target | Description --------+---------------+-------------------------------+---------+--------------+------------- itemid | bigint | not null | plain | | clock | integer | not null default 0 | plain | | value | numeric(20,0) | not null default (0)::numeric | main | | ns | integer | not null default 0 | plain | | Indexes: "history_uint_1" btree (itemid, clock) "pgcompact_index_14476" btree (itemid, clock) INVALID Has OIDs: no

[MaximBoguk]

On Tue, Jun 16, 2015 at 10:41 PM, Pavel Bobrovnikov < notifications@github.com> wrote:

zabbix-server=# \d+ history_uint; Table "public.history_uint" Column | Type | Modifiers | Storage | Stats target | Description

--------+---------------+-------------------------------+---------+--------------+------------- itemid | bigint | not null | plain | | clock | integer | not null default 0 | plain | | value | numeric(20,0) | not null default (0)::numeric | main | | ns | integer | not null default 0 | plain | | Indexes: "history_uint_1" btree (itemid, clock) ​​ "pgcompact_index_14476" btree (itemid, clock) INVALID Has OIDs: no

​It's looks like that you have had interruped pgcompact run on this table once. You should drop index ​ ​"pgcompact_index_14476" ​; and than try rerun compactor.​

— Reply to this email directly or view it on GitHub https://github.com/PostgreSQL-Consulting/pgcompacttable/issues/1#issuecomment-112410664 .

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: maxim.boguk@gmail.com МойКруг: http://mboguk.moikrug.ru/

"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."

[ghost]

Ок, I'll try to do it at night, because now locks is increase.

[ghost]

Hi,

I drop index pgcompact_index_14476 and repet action - not results. Log: Wed Jun 17 18:05:28 2015 Progress: 97%, 238175 pages completed. Wed Jun 17 18:06:28 2015 Progress: 97%, 239085 pages completed. Wed Jun 17 19:25:47 2015 Vacuum final: cannot clean 244966 pages, 4533427 pages left, duration 4732.170 seconds. Wed Jun 17 19:32:15 2015 Analyze final: duration 387.182 second. Wed Jun 17 19:49:14 2015 Bloat statistics with pgstattuple: duration 1019.014 seconds. Wed Jun 17 21:16:15 2015 SQL Error: 7 Use of uninitialized value in sprintf at /tmp/pgcompacttable-master/bin/pgcompac ttable line 180. Wed Jun 17 21:16:15 2015 Skipping index history_uint_1: Wed Jun 17 21:16:15 2015 Processing complete. Wed Jun 17 21:16:15 2015 Processing results: 4533427 pages left (10809492 pages including toasts and indexes), size reduce d by -44.336MB (-4.211GB including toasts and indexes) in total. Wed Jun 17 21:16:15 2015 Finish handling able public.history_uint Wed Jun 17 21:16:15 2015 Processing complete. Wed Jun 17 21:16:15 2015 Processing results: size reduced by -44.336MB (-4.211GB including toasts and indexes) in total. Wed Jun 17 21:16:15 2015 Disconnecting from database [Wed Jun 17 21:16:15 2015] Processing complete: 1 retries to process has been done [Wed Jun 17 21:16:15 2015] Processing results: size reduced by -44.336MB (-4.211GB including toasts and indexes) in total, -44.336MB (-4.211GB) zabbix-server. root@psql-1:~# df -h Filesystem Size Used Avail Use% Mounted on /dev/mapper/vg_root-lv_root 37G 1.8G 34G 5% / tmpfs 4.9G 0 4.9G 0% /dev/shm /dev/sda1 485M 32M 428M 7% /boot /dev/mapper/vg_data01-postgre 197G 172G 16G 92% /var/lib/pgsql root@psql-1:~# su postgres bash-4.1$ psql -d zabbix-server could not change directory to "/root": Permission denied psql (9.3.5) Type "help" for help.

zabbix-server=# \d+ history_uint; Table "public.history_uint" Column | Type | Modifiers | Storage | Stats target | Description --------+---------------+-------------------------------+---------+--------------+------------- itemid | bigint | not null | plain | | clock | integer | not null default 0 | plain | | value | numeric(20,0) | not null default (0)::numeric | main | | ns | integer | not null default 0 | plain | | Indexes: "history_uint_1" btree (itemid, clock) "pgcompact_index_10389" btree (itemid, clock) INVALID Has OIDs: no

[rvoronin]

Hallo, Pavel,

Please try new version of pgcompacttable . Possibly the bug is fixed. If no

• please send me the log and I will try to find out the reason.

Warmest, Roman

2015-06-18 9:33 GMT+03:00 Pavel Bobrovnikov notifications@github.com:

Hi,

I drop index pgcompact_index_14476 and repet action - not results. Log: Wed Jun 17 18:05:28 2015 Progress: 97%, 238175 pages completed. Wed Jun 17 18:06:28 2015 Progress: 97%, 239085 pages completed. Wed Jun 17 19:25:47 2015 Vacuum final: cannot clean 244966 pages, 4533427 pages left, duration 4732.170 seconds. Wed Jun 17 19:32:15 2015 Analyze final: duration 387.182 second. Wed Jun 17 19:49:14 2015 Bloat statistics with pgstattuple: duration 1019.014 seconds. Wed Jun 17 21:16:15 2015 SQL Error: 7 Use of uninitialized value in sprintf at /tmp/pgcompacttable-master/bin/pgcompac ttable line 180. Wed Jun 17 21:16:15 2015 Skipping index history_uint_1: Wed Jun 17 21:16:15 2015 Processing complete. Wed Jun 17 21:16:15 2015 Processing results: 4533427 pages left (10809492 pages including toasts and indexes), size reduce d by -44.336MB (-4.211GB including toasts and indexes) in total. Wed Jun 17 21:16:15 2015 Finish handling able public.history_uint Wed Jun 17 21:16:15 2015 http://zabbix-server Processing complete. Wed Jun 17 21:16:15 2015 http://zabbix-server Processing results: size reduced by -44.336MB (-4.211GB including toasts and indexes) in total. Wed Jun 17 21:16:15 2015 http://zabbix-server Disconnecting from database [Wed Jun 17 21:16:15 2015] Processing complete: 1 retries to process has been done [Wed Jun 17 21:16:15 2015] Processing results: size reduced by -44.336MB (-4.211GB including toasts and indexes) in total, -44.336MB (-4.211GB) zabbix-server. root@psql-1:~# df -h Filesystem Size Used Avail Use% Mounted on /dev/mapper/vg_root-lv_root 37G 1.8G 34G 5% / tmpfs 4.9G 0 4.9G 0% /dev/shm /dev/sda1 485M 32M 428M 7% /boot /dev/mapper/vg_data01-postgre 197G 172G 16G 92% /var/lib/pgsql root@psql-1:~# su postgres bash-4.1$ psql -d zabbix-server could not change directory to "/root": Permission denied psql (9.3.5) Type "help" for help.

zabbix-server=# \d+ history_uint; Table "public.history_uint" Column | Type | Modifiers | Storage | Stats target | Description

--------+---------------+-------------------------------+---------+--------------+------------- itemid | bigint | not null | plain | | clock | integer | not null default 0 | plain | | value | numeric(20,0) | not null default (0)::numeric | main | | ns | integer | not null default 0 | plain | | Indexes: "history_uint_1" btree (itemid, clock) "pgcompact_index_10389" btree (itemid, clock) INVALID Has OIDs: no

— Reply to this email directly or view it on GitHub https://github.com/PostgreSQL-Consulting/pgcompacttable/issues/1#issuecomment-113052513 .