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

Question

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

proton opened this issue 9 years ago · 11 comments

proton commented 9 years ago

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?

Answer 1 · 2015-06-13T06:22:19.000Z

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

Answer 2 · 2015-06-13T06:28:32.000Z

Thank you!

Answer 3 · 2015-06-16T07:55:36.000Z

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 ?!

Answer 4 · 2015-06-16T11:24:38.000Z

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

Answer 5 · 2015-06-16T11:30:45.000Z

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

Answer 6 · 2015-06-16T12:18:32.000Z

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

Answer 7 · 2015-06-16T12:41:36.000Z

Answer 8 · 2015-06-16T13:14:09.000Z

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

Answer 9 · 2015-06-16T13:57:23.000Z

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

Answer 10 · 2015-06-18T06:33:25.000Z

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.

Answer 11 · 2015-06-22T08:24:36.000Z

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