I'm using RDS MySQL database for both preproduction and production environment but I'm facing performance issues on the production server while accessing RDS instance through JDBC (db.m5.large) with a Tomcat server (Apache Tomcat/9.0.26).
Here is the test I'm performing:
If I'm performing a query in command line:
mysql> explain select * from contrat;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | contrat | NULL | ALL | NULL | NULL | NULL | NULL | 116 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
116 rows in 0.00 sec, no problem.
but If I'm performing the same query through JDBC:
Started request at: 25-02-2020 08:40:57
Ended request at: 25-02-2020 08:41:16
it takes almost 20 seconds....and for only 116 rows which is unacceptable for a production server.
But what is very strange with my preproduction RDS database I do not have the same performance issue...JDBC query is executed in a second whereas the RDS preprod is based on a smaller config (db.t3.micro). Configuration is similar, here it is:
context.xml
<Resource name="jdbc/mydb" auth="Container" type="javax.sql.DataSource"
maxTotal="500" maxIdle="30" maxWaitMillis="1000"
username="mydblogin" password="mydbpassword" driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://mydb.myurlaws.rds.amazonaws.com:3306/mydb?useUnicode=yes&characterEncoding=utf8"/>
web.xml
<resource-ref>
<description>MySQL Datasource</description>
<res-ref-name>jdbc/mydb</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
important to notice that for both preprod and prod RDS instances encryption is disabled and both are based on SSD storage.
I do not understand why I'm getting such lack of performance...
would you please tell me which parameter shall I set on my JDBC connection to improve performances on database access ?