Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration. - Mailing list pgsql-performance

From Pavel Stehule
Subject Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.
Date
Msg-id CAFj8pRAcu=7zA1t8N3e48H+mogOS9pnKpzaN0T8sZcgXPTF5OA@mail.gmail.com
Whole thread Raw
In response to Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.  (aditya desai <admad123@gmail.com>)
List pgsql-performance


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.


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

pgsql-performance by date:

Previous
From: aditya desai
Date:
Subject: Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.
Next
From: aditya desai
Date:
Subject: SHARED LOCKS , EXCLUSIVE LOCKS, ACCESS EXCLUSIVE LOCKS