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
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
