On Tue, 15 Jun 2021 at 21:13, Ayub Khan wrote:
>
>
> Would it be a cursor issue on postgres, as there seems to be a difference
in how cursors are handled in postgres and Oracle database. It seems
cursors are returned as buffers to the client side. Below are the steps we
take from jdbc side
i did
Would it be a cursor issue on postgres, as there seems to be a
difference in how cursors are handled in postgres and Oracle database. It
seems cursors are returned as buffers to the client side. Below are the
steps we take from jdbc side
below is the stored procedure code:
CREATE OR REPLACE PROCE
Vijay,
below is the benchmark result when executed against bench_mark database
instead of running the test with slow query on the application database.
This shows that it might not be an issue with MTU but some issue with the
application database itself and the query.
postgres@localhost:~$ pgben
Vijay,
I did not change the MTU on the network interface but created incoming
rule on the security group as per the below documentation:
PMTUD enables the receiving host to respond to the originating host with
the following ICMP message: Destination Unreachable: fragmentation needed
and DF set
thanks.
>latency average = 2480.042 ms
that latency is pretty high, even after changing the mtu ? for a query that
takes 5ms to run (from your explain analyze above) and returns a few 100
rows.
so it does look like a network latency, but it seems strange when you said
the same query from the sam
Ranier,
I did the MTU change and it did seem to bring down the clientWrite waits to
half.
The change I did was to enable ICMP to have Destination Unreachable
fragmentation needed and DF set
"When there is a difference in the MTU size in the network between two
hosts, first make sure that your ne
Ranier, Vijay,
Sure will try and check out pgbench and MTU
--Ayub
On Wed, 9 Jun 2021, 17:47 Ayub Khan, wrote:
> attached is the screenshot of RDS performance insights for AWS and it
> shows high waiting client writes. The api performance is slow. I read that
> this might be due to IOPS on RDS.
since you are willing to try out options :)
if your setup runs the same test plan queries on jmeter against oracle and
postgresql
and only postgresql shows waits or degraded performance I think this is
more then simply network.
can you simply boot up an ec2 ubuntu/centos and install postgresql.
a
Em sáb., 12 de jun. de 2021 às 05:20, Ayub Khan
escreveu:
> Ranier,
>
> This issue is only with queries which are slow, if it's an MTU issue then
> it should be with all the APIs.
>
> I tried on Aurora db and I see same plan and also same slowness
>
I think it is more indicative that the problem
Ranier,
This issue is only with queries which are slow, if it's an MTU issue then
it should be with all the APIs.
I tried on Aurora db and I see same plan and also same slowness
On Wed, 9 Jun 2021, 17:47 Ayub Khan, wrote:
> attached is the screenshot of RDS performance insights for AWS and it
Jeff,
Both tomcat vm and RDS vms have 25Gbps
Postgresql Db class is db.r6g.16xlarge
Tomcat vm is c5.9xlarge
--Ayub
On Wed, 9 Jun 2021, 17:47 Ayub Khan, wrote:
> attached is the screenshot of RDS performance insights for AWS and it
> shows high waiting client writes. The api performance is slo
On Fri, Jun 11, 2021 at 12:28 PM Ayub Khan wrote:
> Vijay,
>
> Both tomcat and postgresql are on the same region as that of the database
> server. It is an RDS so I do not have shell access to it.
>
> Jeff,
>
> The tomcat profile is suggesting that it's waiting for a response from the
> database
Ranier,
Both production and test vms are running on Ubuntu:
the below command when executed from client VM shows that its using
PMTU 9001.
# tracepath dns-name-of-rds
1?: [LOCALHOST] pmtu 9001
On Wed, Jun 9, 2021 at 5:47 PM Ayub Khan wrote:
> attached
Em sex., 11 de jun. de 2021 às 15:19, Ayub Khan
escreveu:
> Ranier,
>
> I verified the link you gave and also checked AWS documentation and found
> the exact output as shown in AWS:
>
> https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/network_mtu.html
>
> [ec2-user ~]$ tracepath amazon.com
>
Ranier,
I verified the link you gave and also checked AWS documentation and found
the exact output as shown in AWS:
https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/network_mtu.html
[ec2-user ~]$ tracepath amazon.com
1?: [LOCALHOST] pmtu 9001
1: ip-xxx-xx-xx-1.us-west-1.compute.interna
Em sex., 11 de jun. de 2021 às 14:59, Ayub Khan
escreveu:
> Ranier,
>
> I tried to VACCUM ANALYZE the tables involved multiple times and also
> tried the statistics approach as well
>
Ayub you can try by the network side:
https://stackoverflow.com/questions/50298447/postgres-jdbc-client-getting-
Ranier,
I tried to VACCUM ANALYZE the tables involved multiple times and also tried
the statistics approach as well
Pavan,
I upgraded to 42.2.21 version of jdbc driver and using HikariCp connection
pool management 3.1.0
jProfiler shows the threads are stuck with high cpu usage on.
org.postgres
Em sex., 11 de jun. de 2021 às 13:59, Ayub Khan
escreveu:
> Pavan,
>
> In jProfiler , I see that most cpu is consumed when the Tomcat thread is
> stuck at PgPreparedStatement.execute. I am using version 42.2.16 of JDBC
> driver.
>
>
> Ranier,
>
> EXPLAIN ANALYZE
>
> SELECT a.menu_item_id, a.menu_
Pavan,
In jProfiler , I see that most cpu is consumed when the Tomcat thread is
stuck at PgPreparedStatement.execute. I am using version 42.2.16 of JDBC
driver.
Ranier,
EXPLAIN ANALYZE
SELECT a.menu_item_id, a.menu_item_name, a.menu_item_category_id,
b.menu_item_category_desc, c.menu_item_vari
Em sex., 11 de jun. de 2021 às 13:28, Ayub Khan
escreveu:
> Vijay,
>
> Both tomcat and postgresql are on the same region as that of the database
> server. It is an RDS so I do not have shell access to it.
>
> Jeff,
>
> The tomcat profile is suggesting that it's waiting for a response from the
> d
Hi Ayub
So, i understand the client are blocked waiting on a write to the database!
What does the blocked thread signature say?
Are you pre-creating any partitions?
Are you experiencing Timed outs??
What is the driver you are using now? If you are using Jdbc, can you update
your driver to the
Vijay,
Both tomcat and postgresql are on the same region as that of the database
server. It is an RDS so I do not have shell access to it.
Jeff,
The tomcat profile is suggesting that it's waiting for a response from the
database server.
Tomcat and RDS are in the same availability region as eu-
Ayub,
Ideally when i have to deal with this,
i run a pgbench stress test locally on the db server on lo interface
which does not suffer mtu / bandwidth saturation issues.
then run the same pgbench from a remote server in the same subnet as the
app and record the results and compare.
that helps me
On Thu, Jun 10, 2021 at 4:06 AM Ayub Khan wrote:
> I did profiling of the application and it seems most of the CPU
> consumption is for executing the stored procedure. Attached is the
> screenshot of the profile
>
That is of your tomcat server? If that is really a profile of your CPU
time (rath
I did profiling of the application and it seems most of the CPU consumption
is for executing the stored procedure. Attached is the screenshot of the
profile
--Ayub
On Wed, Jun 9, 2021 at 5:47 PM Ayub Khan wrote:
> attached is the screenshot of RDS performance insights for AWS and it
> shows hi
@Magnus
There is an EC2 tomcat server which communicates to postgresql. This is a
replica of our production server except that in this case the test database
is postgres RDS and our production is running oracle on EC2 instance.
On Wed, Jun 9, 2021 at 5:47 PM Ayub Khan wrote:
> attached is the s
On Wed, Jun 9, 2021 at 4:47 PM Ayub Khan wrote:
>
> attached is the screenshot of RDS performance insights for AWS and it shows
> high waiting client writes. The api performance is slow. I read that this
> might be due to IOPS on RDS. However we have 80k IOPS on this test RDS.
>
ClientWrite mea
27 matches
Mail list logo
