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

Re: Memory leak tsearch2 VACUUM FULL VERBOSE ANALYZE

From: Pailloncy Jean-Gerard <jg(at)rilk(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: Memory leak tsearch2 VACUUM FULL VERBOSE ANALYZE
Date: 2004-12-22 23:42:27
Message-ID: 233CAA75-5473-11D9-9A44-000A95DE2550@rilk.com (view raw or flat)
Thread:
Lists: pgsql-performance
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


In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2004-12-23 00:01:26
Subject: Re: Memory leak tsearch2 VACUUM FULL VERBOSE ANALYZE
Previous:From: Litao WuDate: 2004-12-22 21:52:40
Subject: Re: Why so much time difference with a same query/plan?

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