Re: serious problems with vacuuming databases

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: serious problems with vacuuming databases
Дата
Msg-id 12734.1144608837@sss.pgh.pa.us
обсуждение исходный текст
Ответ на serious problems with vacuuming databases  (Tomas Vondra <tv@fuzzy.cz>)
Ответы Re: serious problems with vacuuming databases  (Tomas Vondra <tv@fuzzy.cz>)
Список pgsql-performance
Tomas Vondra <tv@fuzzy.cz> writes:
> 1) drop, create and fill table B (aggregated data from A, C, D)
> 2) copy 'old' data from A, C and D to A_old, C_old a D_old
> 3) delete old data from A, C, D
> 4) dump data from A_old, C_old and D_old
> 5) truncate tables A, C, D
> 6) vacuum full analyze tables A, C, D, A_old, C_old and D_old

Steps 3/5/6 make no sense at all to me: why bother deleting data retail
when you are about to truncate the tables, and why bother vacuuming a
table you just truncated?  Is the above *really* what you did?

> The problem is this - today, we run a scheduled VACUUM FULL ANALYZE for
> the whole database, and it runs for about 10 hours already, which is
> much more than usual (and it is still running).

Is it actually grinding the disk, or is it just blocked waiting for
someone's lock?  If it's actually doing work, which table is it working
on?  (You should be able to figure that out by looking in pg_locks,
or by strace'ing the process to see which files it's touching.)

            regards, tom lane

В списке pgsql-performance по дате отправления:

Предыдущее
От: Alvaro Herrera
Дата:
Сообщение: Re: serious problems with vacuuming databases
Следующее
От: Bruce Momjian
Дата:
Сообщение: Re: Scaling up PostgreSQL in Multiple CPU / Dual Core