pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Error 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