pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedError in VACUUM FULL VERBOSE ANALYZE (not enough memory)
12+ messages / 5 participants
[nested] [flat]
* Error in VACUUM FULL VERBOSE ANALYZE (not enough memory)
@ 2004-12-17 17:58 Pailloncy Jean-Gerard <pailloncy@ifrance.com>
0 siblings, 2 replies; 12+ messages in thread
From: Pailloncy Jean-Gerard @ 2004-12-17 17:58 UTC (permalink / raw)
To: pgsql-performance
I have a table with an tsearch2 full text index on PG 7.4.2. And a
query against the index is really slow.
I try to do a "VACUUM FULL VERBOSE ANALYZE pkpoai.metadata" and I got
an error.
I monitor memory usage with top, and pg backend uses more and more
memory and hits the limit of 1GB of RAM use.
What can I do ?
Cordialement,
Jean-Gérard Pailloncy
# top (just before the error)
PID UID PRI NICE SIZE RES STATE WAIT TIME CPU COMMAND
20461 503 -5 0 765M 824M sleep biowai 4:26 33.20% postgres
# VACUUM FULL VERBOSE ANALYZE pkpoai.metadata;
INFO: vacuuming "pkpoai.metadata"
INFO: "metadata": found 167405 removable, 3133397 nonremovable row
versions in 344179 pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 168 to 2032 bytes long.
There were 13368 unused item pointers.
Total free space (including removable row versions) is 174825268 bytes.
9362 pages are or will become empty, including 0 at the end of the
table.
150433 pages containing 166581084 free bytes are potential move
destinations.
CPU 6.28s/1.42u sec elapsed 51.87 sec.
INFO: index "metadata_pkey" now contains 3133397 row versions in 10501
pages
DETAIL: 88443 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.39s/1.35u sec elapsed 26.12 sec.
INFO: index "metadata_archive_key" now contains 3133397 row versions
in 45268 pages
DETAIL: 88443 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 2.44s/1.65u sec elapsed 355.32 sec.
INFO: index "metadata_oai_identifier" now contains 3133397 row
versions in 36336 pages
DETAIL: 88443 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 1.67s/1.69u sec elapsed 258.86 sec.
INFO: index "test_metadata_all" now contains 3133397 row versions in
97707 pages
DETAIL: 88442 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 1.88s/3.98u sec elapsed 230.70 sec.
ERROR: out of memory
DETAIL: Failed on request of size 168.
EXPLAIN SELECT id, title, author, add_authors, identifier, date FROM
pkpoai.metadata WHERE to_tsvector('default_english',
coalesce(author,'') ||' '|| coalesce(affiliation,'') ||' '||
coalesce(add_authors,'') ||' '|| coalesce(add_affiliations,'') ||' '||
coalesce(title,'') ||' '|| coalesce(abstract,'') ||' '||
coalesce(discipline,'') ||' '|| coalesce(topic,'') ||' '||
coalesce(publisher,'') ||' '|| coalesce(contributors,'') ||' '||
coalesce(approach,'') ||' '|| coalesce(format,'') ||' '||
coalesce(source,'') ||' '|| coalesce(language,'') ||' '||
coalesce(relation,'') ||' '|| coalesce(coverage,'') ) @@
to_tsquery('default_english','pailloncy') LIMIT 100
Limit (cost=0.00..310.80 rows=100 width=176)
-> Index Scan using test_metadata_all on metadata
(cost=0.00..9706.34 rows=3123 width=176)
Index Cond: (to_tsvector('default_english'::text,
((((((((((((((((((((((((((((((COALESCE(author, ''::text) || ' '::text)
|| COALESCE(affiliation, ''::text)) || ' '::text) ||
COALESCE(add_authors, ''::text)) || ' '::text) ||
COALESCE(add_affiliations, ''::text)) || ' '::text) || COALESCE(title,
''::text)) || ' '::text) || COALESCE(abstract, ''::text)) || ' '::text)
|| COALESCE(discipline, ''::text)) || ' '::text) || COALESCE(topic,
''::text)) || ' '::text) || COALESCE(publisher, ''::text)) || '
'::text) || COALESCE(contributors, ''::text)) || ' '::text) ||
COALESCE(approach, ''::text)) || ' '::text) || COALESCE(format,
''::text)) || ' '::text) || COALESCE(source, ''::text)) || ' '::text)
|| (COALESCE("language", ''::character varying))::text) || ' '::text)
|| COALESCE(relation, ''::text)) || ' '::text) || COALESCE(coverage,
''::text))) @@ '\'paillonci\''::tsquery)
Filter: (to_tsvector('default_english'::text,
((((((((((((((((((((((((((((((COALESCE(author, ''::text) || ' '::text)
|| COALESCE(affiliation, ''::text)) || ' '::text) ||
COALESCE(add_authors, ''::text)) || ' '::text) ||
COALESCE(add_affiliations, ''::text)) || ' '::text) || COALESCE(title,
''::text)) || ' '::text) || COALESCE(abstract, ''::text)) || ' '::text)
|| COALESCE(discipline, ''::text)) || ' '::text) || COALESCE(topic,
''::text)) || ' '::text) || COALESCE(publisher, ''::text)) || '
'::text) || COALESCE(contributors, ''::text)) || ' '::text) ||
COALESCE(approach, ''::text)) || ' '::text) || COALESCE(format,
''::text)) || ' '::text) || COALESCE(source, ''::text)) || ' '::text)
|| (COALESCE("language", ''::character varying))::text) || ' '::text)
|| COALESCE(relation, ''::text)) || ' '::text) || COALESCE(coverage,
''::text))) @@ '\'paillonci\''::tsquery)
Total runtime: 148.567 ms
EXPLAIN ANALYZE SELECT id, title, author, add_authors, identifier, date
FROM pkpoai.metadata WHERE to_tsvector('default_english',
coalesce(author,'') ||' '|| coalesce(affiliation,'') ||' '||
coalesce(add_authors,'') ||' '|| coalesce(add_affiliations,'') ||' '||
coalesce(title,'') ||' '|| coalesce(abstract,'') ||' '||
coalesce(discipline,'') ||' '|| coalesce(topic,'') ||' '||
coalesce(publisher,'') ||' '|| coalesce(contributors,'') ||' '||
coalesce(approach,'') ||' '|| coalesce(format,'') ||' '||
coalesce(source,'') ||' '|| coalesce(language,'') ||' '||
coalesce(relation,'') ||' '|| coalesce(coverage,'') ) @@
to_tsquery('default_english','pailloncy') LIMIT 100
Limit (cost=0.00..310.80 rows=100 width=176) (actual
time=168751.929..168751.929 rows=0 loops=1)
-> Index Scan using test_metadata_all on metadata
(cost=0.00..9706.34 rows=3123 width=176) (actual
time=168751.921..168751.921 rows=0 loops=1)
Index Cond: (to_tsvector('default_english'::text,
((((((((((((((((((((((((((((((COALESCE(author, ''::text) || ' '::text)
|| COALESCE(affiliation, ''::text)) || ' '::text) ||
COALESCE(add_authors, ''::text)) || ' '::text) ||
COALESCE(add_affiliations, ''::text)) || ' '::text) || COALESCE(title,
''::text)) || ' '::text) || COALESCE(abstract, ''::text)) || ' '::text)
|| COALESCE(discipline, ''::text)) || ' '::text) || COALESCE(topic,
''::text)) || ' '::text) || COALESCE(publisher, ''::text)) || '
'::text) || COALESCE(contributors, ''::text)) || ' '::text) ||
COALESCE(approach, ''::text)) || ' '::text) || COALESCE(format,
''::text)) || ' '::text) || COALESCE(source, ''::text)) || ' '::text)
|| (COALESCE("language", ''::character varying))::text) || ' '::text)
|| COALESCE(relation, ''::text)) || ' '::text) || COALESCE(coverage,
''::text))) @@ '\'paillonci\''::tsquery)
Filter: (to_tsvector('default_english'::text,
((((((((((((((((((((((((((((((COALESCE(author, ''::text) || ' '::text)
|| COALESCE(affiliation, ''::text)) || ' '::text) ||
COALESCE(add_authors, ''::text)) || ' '::text) ||
COALESCE(add_affiliations, ''::text)) || ' '::text) || COALESCE(title,
''::text)) || ' '::text) || COALESCE(abstract, ''::text)) || ' '::text)
|| COALESCE(discipline, ''::text)) || ' '::text) || COALESCE(topic,
''::text)) || ' '::text) || COALESCE(publisher, ''::text)) || '
'::text) || COALESCE(contributors, ''::text)) || ' '::text) ||
COALESCE(approach, ''::text)) || ' '::text) || COALESCE(format,
''::text)) || ' '::text) || COALESCE(source, ''::text)) || ' '::text)
|| (COALESCE("language", ''::character varying))::text) || ' '::text)
|| COALESCE(relation, ''::text)) || ' '::text) || COALESCE(coverage,
''::text))) @@ '\'paillonci\''::tsquery)
Total runtime: 168752.362 ms
Information from phpPgAdmin 3.5.1
PostgreSQL seems to suffer from the TOAST.
Sequential Index Enregistrements
Scan Read Scan Fetch INSERT UPDATE DELETE
0 0 2 19080 0 0 0
I/O Performance
Heap Index TOAST TOAST Index
Disk Buffer % Disk Buffer % Disk Buffer % Disk Buffer
%
17157 1953 (10%) 46945 66047 (58%) 11781 7177 (38%) 2089 44853
(96%)
Performance Index
Index Scan Read Fetch
metadata_archive_key 0 0 0
metadata_oai_identifier 0 0 0
metadata_pkey 0 0 0
test_metadata_all 2 19080 19080
I/O Performance Index
Index Disk Buffer %
metadata_archive_key 0 0 (0%)
metadata_oai_identifie 0 0 (0%)
metadata_pkey 0 0 (0%)
test_metadata_all 46945 66047 (58%)
Structure of the Table pkpoai.metatda
I use only text field because I import data from the web and I do not
know an upper limit of the fields.
id integer NOT NULL
nextval('pkpoai.metadata_id_seq'::text)
archive integer NOT NULL 0
oai_identifier character varying(255) NOT NULL
identifier text NOT NULL
datestamp timestamp without time zone NOT NULL
author text NOT NULL
email text NOT NULL
affiliation text NOT NULL
add_authors text NOT NULL
add_emails text NOT NULL
add_affiliations text NOT NULL
title text NOT NULL
abstract text NOT NULL
discipline text NOT NULL
topic text NOT NULL
publisher text NOT NULL
contributors text NOT NULL
date character varying(255)
type text NOT NULL
approach text NOT NULL
format text NOT NULL
source text NOT NULL
language character varying(255) NOT NULL
relation text NOT NULL
coverage text NOT NULL
rights text NOT NULL
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Error in VACUUM FULL VERBOSE ANALYZE (not enough memory)
@ 2004-12-17 18:15 Josh Berkus <josh@agliodbs.com>
parent: Pailloncy Jean-Gerard <pailloncy@ifrance.com>
1 sibling, 1 reply; 12+ messages in thread
From: Josh Berkus @ 2004-12-17 18:15 UTC (permalink / raw)
To: Pailloncy Jean-Gerard <pailloncy@ifrance.com>; +Cc: pgsql-performance
Jean-Gerard,
> I have a table with an tsearch2 full text index on PG 7.4.2. And a
> query against the index is really slow.
> I try to do a "VACUUM FULL VERBOSE ANALYZE pkpoai.metadata" and I got
> an error.
> I monitor memory usage with top, and pg backend uses more and more
> memory and hits the limit of 1GB of RAM use.
What is your VACUUM_MEM set to in postgresql.conf?
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Error in VACUUM FULL VERBOSE ANALYZE (not enough memory)
@ 2004-12-17 18:25 Pailloncy Jean-Gerard <pailloncy@ifrance.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 12+ messages in thread
From: Pailloncy Jean-Gerard @ 2004-12-17 18:25 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-performance
>> I have a table with an tsearch2 full text index on PG 7.4.2. And a
>> query against the index is really slow.
>> I try to do a "VACUUM FULL VERBOSE ANALYZE pkpoai.metadata" and I got
>> an error.
>> I monitor memory usage with top, and pg backend uses more and more
>> memory and hits the limit of 1GB of RAM use.
>
> What is your VACUUM_MEM set to in postgresql.conf?
vacuum_mem = 131072
I have 1 GB of RAM.
There was only one running backend.
Cordialement,
Jean-Gérard Pailloncy
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Error in VACUUM FULL VERBOSE ANALYZE (not enough memory)
@ 2004-12-17 18:32 Pailloncy Jean-Gerard <jg@rilk.com>
parent: Pailloncy Jean-Gerard <pailloncy@ifrance.com>
0 siblings, 1 reply; 12+ messages in thread
From: Pailloncy Jean-Gerard @ 2004-12-17 18:32 UTC (permalink / raw)
To: pgsql-performance; +Cc: Josh Berkus <josh@agliodbs.com>
The classic output from top (during all other index vacuum):
PID UID PRI NICE SIZE RES STATE WAIT TIME CPU COMMAND
20461 503 14 0 13M 75M sleep semwai 5:27 2.05% postgres
When backend hits the tsearch2 index, SIZE/RES grows until it reachs
1GB, where I got the error.
PID UID PRI NICE SIZE RES STATE WAIT TIME CPU COMMAND
20461 503 -5 0 765M 824M sleep biowai 4:26 33.20% postgres
Cordialement,
Jean-Gérard Pailloncy
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Error in VACUUM FULL VERBOSE ANALYZE (not enough memory)
@ 2004-12-17 18:47 Josh Berkus <josh@agliodbs.com>
parent: Pailloncy Jean-Gerard <jg@rilk.com>
0 siblings, 1 reply; 12+ messages in thread
From: Josh Berkus @ 2004-12-17 18:47 UTC (permalink / raw)
To: Pailloncy Jean-Gerard <jg@rilk.com>; +Cc: pgsql-performance
Jean-Gerard,
> The classic output from top (during all other index vacuum):
> PID UID PRI NICE SIZE RES STATE WAIT TIME CPU COMMAND
> 20461 503 14 0 13M 75M sleep semwai 5:27 2.05% postgres
>
> When backend hits the tsearch2 index, SIZE/RES grows until it reachs
> 1GB, where I got the error.
> PID UID PRI NICE SIZE RES STATE WAIT TIME CPU COMMAND
> 20461 503 -5 0 765M 824M sleep biowai 4:26 33.20% postgres
OK, next thing to try is upgrading to 7.4.7. Since you have 7.4.2, this
should be a straightforward binary replacement.
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Error in VACUUM FULL VERBOSE ANALYZE (not enough memory)
@ 2004-12-17 19:46 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 12+ messages in thread
From: Tom Lane @ 2004-12-17 19:46 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: Pailloncy Jean-Gerard <jg@rilk.com>; pgsql-performance
Josh Berkus <josh@agliodbs.com> writes:
> Jean-Gerard,
>> When backend hits the tsearch2 index, SIZE/RES grows until it reachs
>> 1GB, where I got the error.
>> PID UID PRI NICE SIZE RES STATE WAIT TIME CPU COMMAND
>> 20461 503 -5 0 765M 824M sleep biowai 4:26 33.20% postgres
> OK, next thing to try is upgrading to 7.4.7. Since you have 7.4.2, this
> should be a straightforward binary replacement.
This looks like it must be a memory leak in the gist indexing code
(either gist itself or tsearch2). I don't see any post-release fixes in
the 7.4 branch that look like they fixed any such thing :-(, so it's
probably still there in 7.4.7, and likely 8.0 too.
Jean-Gerard, can you put together a self-contained test case? I suspect
it need only look like "put some data in a table, make a tsearch2 index,
delete half the rows in the table, VACUUM FULL". But I don't have time
to try to cons up a test case right now, and especially not to figure
out what to do to duplicate your problem if it doesn't happen on the
first try.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Error in VACUUM FULL VERBOSE ANALYZE (not enough memory)
@ 2004-12-17 19:52 Josh Berkus <josh@agliodbs.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 12+ messages in thread
From: Josh Berkus @ 2004-12-17 19:52 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Pailloncy Jean-Gerard <jg@rilk.com>; pgsql-performance
Tom,
> Jean-Gerard, can you put together a self-contained test case? I suspect
> it need only look like "put some data in a table, make a tsearch2 index,
> delete half the rows in the table, VACUUM FULL". But I don't have time
> to try to cons up a test case right now, and especially not to figure
> out what to do to duplicate your problem if it doesn't happen on the
> first try.
Might be hard. I have 2 databases with Tsearch2 on 7.4, and haven't seen any
such problem. Including one that blows away about 3000 rows a day.
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Error in VACUUM FULL VERBOSE ANALYZE (not enough memory)
@ 2004-12-17 19:59 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 12+ messages in thread
From: Tom Lane @ 2004-12-17 19:59 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: Pailloncy Jean-Gerard <jg@rilk.com>; pgsql-performance
Josh Berkus <josh@agliodbs.com> writes:
>> Jean-Gerard, can you put together a self-contained test case? I suspect
>> it need only look like "put some data in a table, make a tsearch2 index,
>> delete half the rows in the table, VACUUM FULL". But I don't have time
>> to try to cons up a test case right now, and especially not to figure
>> out what to do to duplicate your problem if it doesn't happen on the
>> first try.
> Might be hard. I have 2 databases with Tsearch2 on 7.4, and haven't seen any
> such problem. Including one that blows away about 3000 rows a day.
Yeah, I'm sure there is some particular thing Jean-Gerard is doing that
is triggering the problem. He can probably boil his existing table down
to a test case faster than we can guess what the trigger condition is.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Error in VACUUM FULL VERBOSE ANALYZE (not enough memory)
@ 2004-12-17 21:17 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 12+ messages in thread
From: Tom Lane @ 2004-12-17 21:17 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: josh@agliodbs.com, Pailloncy Jean-Gerard <jg@rilk.com>; pgsql-performance
Bruno Wolff III <bruno@wolff.to> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> This looks like it must be a memory leak in the gist indexing code
>> (either gist itself or tsearch2). I don't see any post-release fixes in
>> the 7.4 branch that look like they fixed any such thing :-(, so it's
>> probably still there in 7.4.7, and likely 8.0 too.
> Shouldn't that be 7.4.6?
Right ... I copied Josh's mistake without thinking about it...
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Error in VACUUM FULL VERBOSE ANALYZE (not enough memory)
@ 2004-12-17 21:23 Bruno Wolff III <bruno@wolff.to>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 12+ messages in thread
From: Bruno Wolff III @ 2004-12-17 21:23 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: josh@agliodbs.com, Pailloncy Jean-Gerard <jg@rilk.com>; pgsql-performance
On Fri, Dec 17, 2004 at 14:46:57 -0500,
Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> This looks like it must be a memory leak in the gist indexing code
> (either gist itself or tsearch2). I don't see any post-release fixes in
> the 7.4 branch that look like they fixed any such thing :-(, so it's
> probably still there in 7.4.7, and likely 8.0 too.
Shouldn't that be 7.4.6? I am expecting there to be an eventual 7.4.7
because of some post 7.4.6 fixes that have gone in, but I haven't seen
any other indications that this has already happened.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Error in VACUUM FULL VERBOSE ANALYZE (not enough memory)
@ 2004-12-17 21:59 Pailloncy Jean-Gerard <jg@rilk.com>
parent: Pailloncy Jean-Gerard <pailloncy@ifrance.com>
1 sibling, 1 reply; 12+ messages in thread
From: Pailloncy Jean-Gerard @ 2004-12-17 21:59 UTC (permalink / raw)
To: pgsql-performance
Update to my case:
I drop and recreate the index and there was no problem this time.
Strange...
# DROP INDEX pkpoai.test_metadata_all;
DROP INDEX
# VACUUM FULL VERBOSE ANALYZE pkpoai.metadata;
INFO: vacuuming "pkpoai.metadata"
INFO: "metadata": found 167381 removable, 3133397 nonremovable row
versions in 344179 pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 168 to 2032 bytes long.
There were 13392 unused item pointers.
Total free space (including removable row versions) is 174825268 bytes.
9362 pages are or will become empty, including 0 at the end of the
table.
150433 pages containing 166581084 free bytes are potential move
destinations.
CPU 7.07s/1.50u sec elapsed 209.46 sec.
INFO: index "metadata_pkey" now contains 3133397 row versions in 10501
pages
DETAIL: 88246 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.68s/1.21u sec elapsed 81.89 sec.
INFO: index "metadata_archive_key" now contains 3133397 row versions
in 45268 pages
DETAIL: 88246 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 2.28s/1.66u sec elapsed 364.19 sec.
INFO: index "metadata_oai_identifier" now contains 3133397 row
versions in 36336 pages
DETAIL: 88246 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 1.85s/1.81u sec elapsed 260.82 sec.
INFO: "metadata": moved 188118 row versions, truncated 344179 to
327345 pages
DETAIL: CPU 9.21s/108.65u sec elapsed 1890.56 sec.
INFO: index "metadata_pkey" now contains 3133397 row versions in 10633
pages
DETAIL: 188118 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.64s/0.60u sec elapsed 52.24 sec.
INFO: index "metadata_archive_key" now contains 3133397 row versions
in 45597 pages
DETAIL: 188118 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 2.40s/1.12u sec elapsed 359.17 sec.
INFO: index "metadata_oai_identifier" now contains 3133397 row
versions in 36624 pages
DETAIL: 188118 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 1.82s/0.97u sec elapsed 277.56 sec.
INFO: vacuuming "pg_toast.pg_toast_27007136"
INFO: "pg_toast_27007136": found 1894 removable, 134515 nonremovable
row versions in 25921 pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 37 to 2034 bytes long.
There were 460 unused item pointers.
Total free space (including removable row versions) is 17460524 bytes.
217 pages are or will become empty, including 0 at the end of the table.
22612 pages containing 17416360 free bytes are potential move
destinations.
CPU 0.51s/0.10u sec elapsed 16.05 sec.
INFO: index "pg_toast_27007136_index" now contains 134515 row versions
in 561 pages
DETAIL: 1894 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.01u sec elapsed 1.22 sec.
INFO: "pg_toast_27007136": moved 1806 row versions, truncated 25921 to
25554 pages
DETAIL: CPU 0.03s/0.21u sec elapsed 9.83 sec.
INFO: index "pg_toast_27007136_index" now contains 134515 row versions
in 569 pages
DETAIL: 1806 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO: analyzing "pkpoai.metadata"
INFO: "metadata": 327345 pages, 90000 rows sampled, 3620548 estimated
total rows
VACUUM
# CREATE INDEX test_metadata_all ON pkpoai.metadata USING gist
(to_tsvector('default_english', coalesce(author,'') ||' '||
coalesce(affiliation,'') ||' '|| coalesce(add_authors,'') ||' '||
coalesce(add_affiliations,'') ||' '|| coalesce(title,'') ||' '||
coalesce(abstract,'') ||' '|| coalesce(discipline,'') ||' '||
coalesce(topic,'') ||' '|| coalesce(publisher,'') ||' '||
coalesce(contributors,'') ||' '|| coalesce(approach,'') ||' '||
coalesce(format,'') ||' '|| coalesce(source,'') ||' '||
coalesce(language,'') ||' '|| coalesce(relation,'') ||' '||
coalesce(coverage,'') ));
NOTICE: word is too long
NOTICE: word is too long
NOTICE: word is too long
CREATE INDEX
# VACUUM FULL VERBOSE ANALYZE pkpoai.metadata;INFO: vacuuming
"pkpoai.metadata"INFO: "metadata": found 0 removable, 3133397
nonremovable row versions in 327345 pagesDETAIL: 0 dead row versions
cannot be removed yet.Nonremovable row versions range from 168 to 2032
bytes long.There were 29889 unused item pointers.Total free space
(including removable row versions) is 37861356 bytes.
0 pages are or will become empty, including 0 at the end of the table.
93935 pages containing 28461956 free bytes are potential move
destinations.
CPU 5.81s/1.09u sec elapsed 56.18 sec.
INFO: index "metadata_pkey" now contains 3133397 row versions in 10633
pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.53s/0.94u sec elapsed 20.25 sec.
INFO: index "metadata_archive_key" now contains 3133397 row versions
in 45597 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 2.46s/1.35u sec elapsed 338.74 sec.
INFO: index "metadata_oai_identifier" now contains 3133397 row
versions in 36624 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 1.78s/1.33u sec elapsed 237.07 sec.
INFO: index "test_metadata_all" now contains 3133397 row versions in
93136 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 1.65s/3.47u sec elapsed 167.03 sec.
INFO: "metadata": moved 0 row versions, truncated 327345 to 327345
pages
DETAIL: CPU 0.35s/0.41u sec elapsed 82.11 sec.
INFO: vacuuming "pg_toast.pg_toast_27007136"
INFO: "pg_toast_27007136": found 0 removable, 134515 nonremovable row
versions in 25554 pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 37 to 2034 bytes long.
There were 665 unused item pointers.
Total free space (including removable row versions) is 14468156 bytes.
0 pages are or will become empty, including 0 at the end of the table.
22041 pages containing 14421368 free bytes are potential move
destinations.
CPU 0.52s/0.03u sec elapsed 16.14 sec.
INFO: index "pg_toast_27007136_index" now contains 134515 row versions
in 569 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.01s/0.04u sec elapsed 0.54 sec.
INFO: "pg_toast_27007136": moved 0 row versions, truncated 25554 to
25554 pages
DETAIL: CPU 0.00s/0.03u sec elapsed 2.56 sec.
INFO: analyzing "pkpoai.metadata"
INFO: "metadata": 327345 pages, 90000 rows sampled, 3620548 estimated
total rows
VACUUM
Cordialement,
Jean-Gérard Pailloncy
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Error in VACUUM FULL VERBOSE ANALYZE (not enough memory)
@ 2004-12-17 22:20 Tom Lane <tgl@sss.pgh.pa.us>
parent: Pailloncy Jean-Gerard <jg@rilk.com>
0 siblings, 0 replies; 12+ messages in thread
From: Tom Lane @ 2004-12-17 22:20 UTC (permalink / raw)
To: Pailloncy Jean-Gerard <jg@rilk.com>; +Cc: pgsql-performance
Pailloncy Jean-Gerard <jg@rilk.com> writes:
> Update to my case:
> I drop and recreate the index and there was no problem this time.
> Strange...
Well, that time there wasn't actually any work for VACUUM FULL to do.
I think the bloat is probably driven by having to move a lot of rows
in order to shrink the table. That means creating and deleting a lot
of index entries.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2004-12-17 22:20 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-12-17 17:58 Error in VACUUM FULL VERBOSE ANALYZE (not enough memory) Pailloncy Jean-Gerard <pailloncy@ifrance.com>
2004-12-17 18:15 ` Josh Berkus <josh@agliodbs.com>
2004-12-17 18:25 ` Pailloncy Jean-Gerard <pailloncy@ifrance.com>
2004-12-17 18:32 ` Pailloncy Jean-Gerard <jg@rilk.com>
2004-12-17 18:47 ` Josh Berkus <josh@agliodbs.com>
2004-12-17 19:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-12-17 19:52 ` Josh Berkus <josh@agliodbs.com>
2004-12-17 19:59 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-12-17 21:23 ` Bruno Wolff III <bruno@wolff.to>
2004-12-17 21:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-12-17 21:59 ` Pailloncy Jean-Gerard <jg@rilk.com>
2004-12-17 22:20 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox