Thread: Error in VACUUM FULL VERBOSE ANALYZE (not enough memory)
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
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
>> 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
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
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
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
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
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
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.
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
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
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
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
Pailloncy Jean-Gerard <jg@rilk.com> writes:
> I think I have a test case for 7.4.2
Can you send me the test data (off-list)?
regards, tom lane
Pailloncy Jean-Gerard <jg@rilk.com> 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 */