Re: work_mem in high transaction rate database

Список
Период
Сортировка
От Flavio Henrique Araque Gurgel
Тема Re: work_mem in high transaction rate database
Дата
Msg-id 13904207.99291236170787203.JavaMail.root@mail.4linux.com.br
обсуждение исходный текст
Ответ на work_mem in high transaction rate database  (Flavio Henrique Araque Gurgel)
Ответы Re: work_mem in high transaction rate database  (Scott Carey)
Список pgsql-performance
Дерево обсуждения
work_mem in high transaction rate database  (Flavio Henrique Araque Gurgel, )
 Re: work_mem in high transaction rate database  (Scott Marlowe, )
  Re: work_mem in high transaction rate database  (Akos Gabriel, )
  Re: work_mem in high transaction rate database  (Dimitri Fontaine, )
 Re: work_mem in high transaction rate database  (Flavio Henrique Araque Gurgel, )
  Re: work_mem in high transaction rate database  (Scott Carey, )
   Re: work_mem in high transaction rate database  (Scott Marlowe, )
----- "Scott Marlowe" <> escreveu:
> Oh my lord, that is a foot gun waiting to go off.  Assuming 2k
> connections, and somehow a fair number of them went active with big
> sorts, you'd be able to exhaust all physical memory  with about 8 to
> 16 connections.  Lower work_mem now. To something like 1 to 4 Meg.  Do
> not pass go.  If some oddball query really needs a lot of work_mem,
> and benchmarks show something larger work_mem helps, consider raising
> the work_mem setting for that one query to something under 1G (way
> under 1G) That makes it noticeably faster.  Don't allocate more than a
> test shows you helps.

Thanks a lot Scott. That's what I thought in the beginning but was very doubtful since the documentation is a bit odd regarding this point and several bloggers talk about increasing this value up to 250MB. I really think that separating regular non pooled distributed applications and pooled application servers makes a lot of difference in this point.

> Vacuum does not block transactions.  unless you're dropping tables or something.

I'll try to separate things and check if the DELETE queries have something related here.

(...)
> what you think they mean.  Post the first 20 or so lines from top to
> show us.

Unfortunately I can't do it. The data there is very sensitive (it's a public company here in Brazil) and the server is operated only by selected personal. I just ask for information and give written recomendations. Anyway, I'm going to pay some more attention in this topic.

This is a very interesting implementation of PostgreSQL (3 large databases, heavy load, things growing all the time) and I'll let you all know what happened when tuning it. I'll feedback you after lowering work_mem and changing related settings.

Thanks
Flavio

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

Предыдущее
От: Brad Nicholson
Дата:
Сообщение: Long Running Commits
Следующее
От: Scott Carey
Дата:
Сообщение: Re: work_mem in high transaction rate database