Skip site navigation (1) Skip section navigation (2)

Error in VACUUM FULL VERBOSE ANALYZE (not enough memory)

From: Pailloncy Jean-Gerard <pailloncy(at)ifrance(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Error in VACUUM FULL VERBOSE ANALYZE (not enough memory)
Date: 2004-12-17 17:58:21
Message-ID: 3D4B6E62-5055-11D9-96E2-000A95DE2550@ifrance.com (view raw or flat)
Thread:
Lists: 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

Responses

pgsql-performance by date

Next:From: Josh BerkusDate: 2004-12-17 18:15:54
Subject: Re: Error in VACUUM FULL VERBOSE ANALYZE (not enough memory)
Previous:From: Tom LaneDate: 2004-12-17 17:44:41
Subject: Re: Seqscan rather than Index

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group