Re: Optimal configuration for server

Em seg., 7 de mar. de 2022 às 18:10, Luiz Felipph <luizfelipph(at)gmail(dot)com>
escreveu:

> Hi Tomas,
>
> Thank you for your reply!
>
> Thomas,
>
>> You need to monitor shared buffers cache hit rate (from pg_stat_database
>> view) - if that's low, increase shared buffers. Then monitor and tune
>> slow queries - if a slow query benefits from higher work_mem values, do
>> increase that value. It's nonsense to just increase the parameters to
>> consume more memory.
>
>
> Makes perfect sense! The system is a OLTP and unfortunately has some
> issues about how big the single lines are(too many colunms). In some cases
> I have to bring to app 150k lines(in some not so rare cases, 200k ~300k) to
> process in a single transaction, then update and insert new rows. It's
> works fine, except when eventually start to outOfMemory or Connection has
> been closed forcing us to restart the application cluster. Finally I'll
> have access to a performance environment to see how is configured(they
> promised me a production mirror) and then get back to you to provide more
> detailed information.
>
> Thanks for you time!
>
> Ranier,
>
>> Are you using nested connections?
>
>
> What do you mean with "nested connections"? If you are talking about
> nested transactions, then yes, and I'm aware of subtransaction problem but
> I think this is not the case right now (we had, removed multiple points,
> some other points we delivered to God's hands(joking), but know I don't see
> this issue)
>
I mean "nested", even.
Two or more connections opened by app.
If this is case, is need processing the second connection first,
before the first connection.

Just a guess.

regards,
Ranier Vilela