Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.

ne 4. 4. 2021 v 12:39 odesílatel aditya desai <admad123@gmail.com> napsal:

Hi Pavel,

Notes thanks. We have 64 core cpu and 320 GB RAM.

ok - this is probably good for max thousand connections, maybe less (about 6 hundred). Postgres doesn't perform well, when there are too many active queries. Other databases have limits for active queries, and then use an internal queue. But Postgres has nothing similar.

Regards,

Aditya.

On Sat, Apr 3, 2021 at 11:21 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:

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)

Regards

Pavel

On Sat, Apr 3, 2021 at 11:12 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:

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.

https://scalegrid.io/blog/postgresql-connection-pooling-part-4-pgbouncer-vs-pgpool/

Regards

Pavel

 

again.

Regards,

Aditya.

On Sat, Apr 3, 2021 at 9:14 PM aditya desai <admad123@gmail.com> wrote:

Thanks Justin. Will review all parameters and get back to you.

On Sat, Apr 3, 2021 at 9:11 PM Justin Pryzby <pryzby@telsasoft.com> wrote:

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.

--
Justin