so 3. 4. 2021 v 19:45 odesílatel aditya desai <admad123@gmail.com> napsal:
Yes. I have made suggestions on connection pooling as well. Currently it is being done from Application side.
It is usual - but the application side pooling doesn't solve well overloading. The behaviour of the database is not linear. Usually opened connections are not active. But any non active connection can be changed to an active connection (there is not any limit for active connections), and then the performance can be very very slow. Good pooling and good setting of max_connections is protection against overloading. max_connection should be 10-20 x CPU cores (for OLTP)
so 3. 4. 2021 v 19:37 odesílatel aditya desai <admad123@gmail.com> napsal:
Hi Justin/Bruce/Pavel,
Thanks for your inputs. After setting force_parallel_mode=off Execution time of same query was reduced to 1ms from 200 ms. Worked like a charm. We also increased work_mem to 80=MB. Thanks
super.
The too big max_connection can cause a lot of problems. You should install and use pgbouncer or pgpool II.
On Sat, Apr 03, 2021 at 11:39:19AM -0400, Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > On Sat, Apr 3, 2021 at 08:38:18PM +0530, aditya desai wrote: > >> Yes, force_parallel_mode is on. Should we set it off? > > > Yes. I bet someone set it without reading our docs: > > > https://www.postgresql.org/docs/13/runtime-config-query.html#RUNTIME-CONFIG-QUERY-OTHER > > > --> Allows the use of parallel queries for testing purposes even in cases > > --> where no performance benefit is expected. > > > We might need to clarify this sentence to be clearer it is _only_ for > > testing. > > I wonder why it is listed under planner options at all, and not under > developer options.
Because it's there to help DBAs catch errors in functions incorrectly marked as parallel safe.