0

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&amp;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 ?

2
  • Juste coming back to my initial question, I tried to use JDBCPool by addin in Context.xml the following params factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" initialSize="34" maxActive="377" maxIdle="233" minIdle="89" timeBetweenEvictionRunsMillis="34000" minEvictableIdleTimeMillis="55000" validationQuery="SELECT 1" validationInterval="34000" testOnBorrow="true" removeAbandoned="true" removeAbandonedTimeout="55" but I have the same results
    – tiamat
    Mar 4, 2020 at 10:05
  • I also tried to see if I had any errors in the logs but I'm not able to see anything
    – tiamat
    Mar 4, 2020 at 10:06

0

You must log in to answer this question.

Browse other questions tagged .