Thread: Error in VACUUM FULL VERBOSE ANALYZE (not enough memory)

From:
Pailloncy Jean-Gerard
Date:

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

From:
Josh Berkus
Date:

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

From:
Pailloncy Jean-Gerard
Date:

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


From:
Pailloncy Jean-Gerard
Date:

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


From:
Josh Berkus
Date:

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

From:
Tom Lane
Date:

Josh Berkus <> 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

From:
Josh Berkus
Date:

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

From:
Tom Lane
Date:

Josh Berkus <> 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

From:
Bruno Wolff III
Date:

On Fri, Dec 17, 2004 at 14:46:57 -0500,
  Tom Lane <> 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.

From:
Tom Lane
Date:

Bruno Wolff III <> writes:
> Tom Lane <> 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

From:
Pailloncy Jean-Gerard
Date:

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


From:
Tom Lane
Date:

Pailloncy Jean-Gerard <> 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

From:
Pailloncy Jean-Gerard
Date:

I think I have a test case for 7.4.2

So I have a 3 millions of rows table "metadata" with a tsearch2 index.
I had memory leak in "vacuum full verbose analyze"

I drop the index, run "vacuum full verbose analyze", recreate the index
and re-run "vacuum full verbose analyze".

The I run my script to insert near 15000 of rows, and run "vacuum full
verbose analyze".

The backend starts with res=4Mb of ram.
And grows before the first output line to res=69Mb.
and runs staying at res=69Mb.
Then after writing INFO:  index "metadata_oai_identifier" and before
INFO:  index "test_metadata_all" which is the tsearch2 index, the
memory usage grows to size=742Mb res=804Mb. (Hopefully I have 1 GB of
RAM, with 1 GB of swap).
The usage stay at res=804MB until INFO:  "pg_toast_27007136": found,
then drop back to res=69Mb.
When INFO:  "pg_toast_27007136": moved memory usage grows to res=200MB.
And did not drop back even after vacuum finished.

Cordialement,
Jean-Gérard Pailloncy

# vacuum full verbose analyze pkpoai.metadata;
INFO:  vacuuming "pkpoai.metadata"
INFO:  "metadata": found 15466 removable, 3141229 nonremovable row
versions in 330201 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 168 to 2032 bytes long.
There were 29868 unused item pointers.
Total free space (including removable row versions) is 54151896 bytes.
496 pages are or will become empty, including 0 at the end of the table.
98834 pages containing 44826736 free bytes are potential move
destinations.
CPU 6.10s/1.03u sec elapsed 69.36 sec.
INFO:  index "metadata_pkey" now contains 3141229 row versions in 10666
pages
DETAIL:  15466 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.56s/0.92u sec elapsed 40.45 sec.
INFO:  index "metadata_archive_key" now contains 3141229 row versions
in 45733 pages
DETAIL:  15466 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 2.36s/1.44u sec elapsed 362.57 sec.
INFO:  index "metadata_oai_identifier" now contains 3141229 row
versions in 36736 pages
DETAIL:  15466 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 2.04s/1.25u sec elapsed 244.82 sec.
INFO:  index "test_metadata_all" now contains 3141229 row versions in
93922 pages
DETAIL:  15466 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 1.81s/3.76u sec elapsed 196.50 sec.
INFO:  "metadata": moved 14151 row versions, truncated 330201 to 328285
pages
DETAIL:  CPU 2.65s/59.67u sec elapsed 251.01 sec.
INFO:  index "metadata_pkey" now contains 3141229 row versions in 10686
pages
DETAIL:  14151 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.68s/0.29u sec elapsed 67.42 sec.
INFO:  index "metadata_archive_key" now contains 3141229 row versions
in 45774 pages
DETAIL:  14151 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 2.28s/0.54u sec elapsed 347.82 sec.
INFO:  index "metadata_oai_identifier" now contains 3141229 row
versions in 36784 pages
DETAIL:  14151 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 2.02s/0.39u sec elapsed 248.27 sec.
INFO:  index "test_metadata_all" now contains 3141229 row versions in
94458 pages
DETAIL:  14151 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 1.76s/2.93u sec elapsed 173.22 sec.
INFO:  vacuuming "pg_toast.pg_toast_27007136"
INFO:  "pg_toast_27007136": found 5790 removable, 135159 nonremovable
row versions in 26847 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 24067284 bytes.
559 pages are or will become empty, including 0 at the end of the table.
23791 pages containing 24026432 free bytes are potential move
destinations.
CPU 0.54s/0.12u sec elapsed 19.60 sec.
INFO:  index "pg_toast_27007136_index" now contains 135159 row versions
in 593 pages
DETAIL:  5790 index row versions were removed.
3 index pages have been deleted, 3 are currently reusable.
CPU 0.01s/0.03u sec elapsed 0.77 sec.
INFO:  "pg_toast_27007136": moved 5733 row versions, truncated 26847 to
25695 pages
DETAIL:  CPU 0.13s/0.34u sec elapsed 15.25 sec.
INFO:  index "pg_toast_27007136_index" now contains 135159 row versions
in 611 pages
DETAIL:  5733 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.01u sec elapsed 0.01 sec.
INFO:  analyzing "pkpoai.metadata"
INFO:  "metadata": 328285 pages, 90000 rows sampled, 3631229 estimated
total rows
VACUUM


From:
Tom Lane
Date:

Pailloncy Jean-Gerard <> writes:
> I think I have a test case for 7.4.2

Can you send me the test data (off-list)?

            regards, tom lane

From:
Tom Lane
Date:

Pailloncy Jean-Gerard <> writes:
> I think I have a test case for 7.4.2

Try the attached patch.

It looked to me like there were some smaller leaks going on during COPY
and CREATE INDEX, which I will look into later --- but this seems to be
the problem for VACUUM FULL.

            regards, tom lane

Index: vacuum.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/vacuum.c,v
retrieving revision 1.263
diff -c -r1.263 vacuum.c
*** vacuum.c    2 Oct 2003 23:19:44 -0000    1.263
--- vacuum.c    23 Dec 2004 22:37:57 -0000
***************
*** 2041,2046 ****
--- 2041,2047 ----
                          ExecStoreTuple(&newtup, slot, InvalidBuffer, false);
                          ExecInsertIndexTuples(slot, &(newtup.t_self),
                                                estate, true);
+                         ResetPerTupleExprContext(estate);
                      }

                      WriteBuffer(cur_buffer);
***************
*** 2174,2179 ****
--- 2175,2181 ----
              {
                  ExecStoreTuple(&newtup, slot, InvalidBuffer, false);
                  ExecInsertIndexTuples(slot, &(newtup.t_self), estate, true);
+                 ResetPerTupleExprContext(estate);
              }
          }                        /* walk along page */