Re: Performance large tables.

Поиск
Список
Период
Сортировка
От William Yu
Тема Re: Performance large tables.
Дата
Msg-id dnhgcl$1bhk$1@news.hub.org
обсуждение исходный текст
Ответ на Re: Performance large tables.  (Benjamin Arai <barai@cs.ucr.edu>)
Список pgsql-general
Benjamin Arai wrote:
> For the most part the updates are simple one liners.  I currently commit
> in large batch to increase performance but it still takes a while as
> stated above.  From evaluating the computers performance during an
> update,  the system is thrashing both memory and disk.  I am currently
> using Postgresql 8.0.3.
>
> Example command "UPDATE data where name=x and date=y;".

Before you start throwing the baby out with the bathwater by totally
revamping your DB architecture, try some simple debugging first to see
why these queries take a long time. Use explain analyze, test
vacuuming/analyzing mid-updates, fiddle with postgresql.conf parameters
(the wal/commit settings especially). Try using using commit w/
different amounts of transactions -- the optimal # won't be the same
across all development tools.

My own experience is that periodic vacuuming & analyzing are very much
needed for batches of small update commands. For our batch processing,
autovacuum plus 1K-10K commit batches did the trick in keeping
performance up.

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

Предыдущее
От: Hannes Dorbath
Дата:
Сообщение: TSearch2: Auto identify document language?
Следующее
От: Frank van Vugt
Дата:
Сообщение: PL/pgSQL : notion of deferred execution