Error in VACUUM FULL VERBOSE ANALYZE (not enough memory) - Mailing list pgsql-performance

From Pailloncy Jean-Gerard
Subject Error in VACUUM FULL VERBOSE ANALYZE (not enough memory)
Date
Msg-id 3D4B6E62-5055-11D9-96E2-000A95DE2550@ifrance.com
Whole thread Raw
Responses Re: Error in VACUUM FULL VERBOSE ANALYZE (not enough memory)
Re: Error in VACUUM FULL VERBOSE ANALYZE (not enough memory)
List 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

pgsql-performance by date:

Previous
From: Tom Lane
Date:
Subject: Re: Seqscan rather than Index
Next
From: Josh Berkus
Date:
Subject: Re: Error in VACUUM FULL VERBOSE ANALYZE (not enough memory)