Re: Memory leak tsearch2 VACUUM FULL VERBOSE ANALYZE - Mailing list pgsql-performance
From | Pailloncy Jean-Gerard |
---|---|
Subject | Re: Memory leak tsearch2 VACUUM FULL VERBOSE ANALYZE |
Date | |
Msg-id | 233CAA75-5473-11D9-9A44-000A95DE2550@rilk.com Whole thread Raw |
In response to | Re: Error in VACUUM FULL VERBOSE ANALYZE (not enough memory) (Tom Lane <tgl@sss.pgh.pa.us>) |
Responses |
Re: Memory leak tsearch2 VACUUM FULL VERBOSE ANALYZE
Re: Memory leak tsearch2 VACUUM FULL VERBOSE ANALYZE |
List | 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
pgsql-performance by date: