Search Postgresql Archives

Re: ltree + gist index performance degrades significantly over a night

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



--- Scott Marlowe <smarlowe@xxxxxxxxxxxxxxxxx> wrote:

> Are you vacuuming regularly, are your fsm settings high enough, and what
> does vacuum verbose say?

Autovacuum is running, but I do a nightly vacuum analyze. When I just do a
vacuum analyze on the table I get:

data=# vacuum analyze verbose search;
INFO:  vacuuming "search"
INFO:  index "search_id_pkey" now contains 1344661 row versions in 5134 pages
DETAIL:  9 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.09s/0.07u sec elapsed 4.91 sec.
INFO:  index "search_vector_idx" now contains 1344672 row versions in 47725
pages
DETAIL:  9 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 1.77s/0.37u sec elapsed 407.55 sec.
INFO:  index "search_item_id_idx" now contains 1344690 row versions in 6652
pages
DETAIL:  9 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.24s/0.08u sec elapsed 45.62 sec.
INFO:  "search": removed 9 row versions in 2 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.05 sec.
INFO:  "letter_search": found 9 removable, 1344661 nonremovable row versions in
33984 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 141 unused item pointers.
0 pages are entirely empty.
CPU 2.41s/0.62u sec elapsed 483.06 sec.
INFO:  vacuuming "pg_toast.pg_toast_174918394"
INFO:  index "pg_toast_174918394_index" now contains 0 row versions in 1 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  "pg_toast_174918394": found 0 removable, 0 nonremovable row versions in
0 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  analyzing "search"
INFO:  "search": scanned 3000 of 33984 pages, containing 119035 live rows and 0
dead rows; 3000 rows in sample, 1348428 estimated total rows
VACUUM

max_fsm_pages = 60000               # min max_fsm_relations*16, 6 bytes each
max_fsm_relations = 2000            # min 100, ~70 bytes each




__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux