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 [email protected]
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
#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: [email protected]
МойКруг: 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 <[email protected]

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: [email protected]
МойКруг: 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 <[email protected]

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
#1 (comment)
.

​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: [email protected]
МойКруг: 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 <
[email protected]> 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
#1 (comment)
.

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]
МойКруг: 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 [email protected]:

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
#1 (comment)
.