Re: DB Tuning Notes for comment...
От | Scott Shattuck |
---|---|
Тема | Re: DB Tuning Notes for comment... |
Дата | |
Msg-id | 3DF51181.10305@technicalpursuit.com обсуждение исходный текст |
Ответ на | DB Tuning Notes for comment... (Philip Warner <pjw@rhyme.com.au>) |
Ответы |
Re: DB Tuning Notes for comment...
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-hackers |
Tom Lane wrote: > Scott Shattuck <ss@technicalpursuit.com> writes: > >>Robert Treat wrote: >> >>>I don't think this is entirely true. On tables that have large numbers >>>of inserts, but no updates or deletes, you do not need to run vacuum. >> > >>In my experience I've seen tables with numerous indexes continue to >>benefit greatly from vacuum/vacuum full operations when large volumes of >>inserts are performed. This is true even when the update/delete activity >>on the base table itself is manageable. > > > This is hard to believe, as VACUUM does not even touch the indexes > unless it has found deletable tuples --- and I am quite certain that > btree indexes, at least, do not do any VACUUM-time reorganization beyond > deleting deletable entries. (I wouldn't swear to it one way or the > other for GiST though.) Robert's opinion coincides with what I know of > the code. > Willing to learn here but skipping a vacuum full has caused some issues for us. Here's some data from a recent 3 day test run that was done with regular vacuums but not vacuum fulls. When running with vacuum full the indexes remain in line: nsuite-10=# select relname, relpages, reltuples from pg_class where relname not like 'pg_%' order by reltuples desc; -[ RECORD 1 ]------------------------------ relname | directory_fullpath_ix relpages | 96012 reltuples | 1.38114e+06 -[ RECORD 2 ]------------------------------ relname | directory_pkey relpages | 16618 reltuples | 1.38114e+06 -[ RECORD 3 ]------------------------------ relname | directory relpages | 23924 reltuples | 59578 <snip> Needless to say, the system performance was pathetic but the test did serve to highlight this index issue. Anyone want to give a quick summary of index maintenance or give me a pointer into the codebase where someone who's not a C expert might still get a sense of what's being done? I'd really like to understand how an index can get so completely out of whack after a weekend of testing. It seems you're telling me that the data here "proves" there's an update or delete going on somewhere in the system, even though this test is of a database initialization driven by a stored procedure with no update or delete operations targeting the directory table. There may be some operations being done external to that process that I've not been made aware of but I'm still curious to learn more about indexing behavior so I know why something like this happens in the first place. ss
В списке pgsql-hackers по дате отправления: