----- "Scott Marlowe" <scott.marlowe@gmail.com> 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
