How this performance graph may be interpreted?

Question

My configuration is:

• Compute

  • AWS ECS cluster with a Django based API (Using ORM). 1vCPU and 4GB of memory per container.

• Database

  • RDS MariaDB db.t3.medium instance (2 vCPU, 4GB of memory)
  • gp3 type storage (3000 IOPS provisioned, 125Mib/s base throughput)
  • Tables information:
    • table_name_1: 48 columns of mostly VARCHAR(50~250) with 49977 rows
    • table_name_2: 5 columns of INT and DATETIME with 49977 rows
    • table_name_3: 40 columns of INT, VARCHAR, DOUBLE with 656904 rows
    • table_name_4: 32 columns of INT, VARCHAR, DOUBLE with 727477 rows

Test case:

140 WEB clients are active at same time and continuously sending GET requests for 1 minute at two API URLs (70 clients per URL) with same query parameters per URL:

• First URL is executing only SELECT SQL queries. Most complicated one is:

  SELECT 
    `table_name_2`.`id`,
    `table_name_2`.`table_name_1_id`,
    `table_name_2`.`param_1`,
    `table_name_1`.`id`,
    `table_name_1`.`param_1`,
    `table_name_1`.`param_2`
  FROM `table_name_2`
  INNER JOIN `table_name_1` ON (
    `table_name_2`.`table_name_1_id` = `table_name_1`.`id`
  ) INNER JOIN `table_name_3` ON (
    `table_name_1`.`id` = `table_name_3`.`table_name_1_id`
  ) WHERE (
    `table_name_1`.`param_1` >= 100
    AND `table_name_1`.``param_1` <= 200
    AND `table_name_1`.`param_2` >= 100
    AND `table_name_1`.`param_2` <= 200
    AND `table_name_3`.`param_3` >= 10000
    AND `table_name_2`.`param_4` >= 10000
  ) GROUP BY `table_name_2`.`id` ORDER BY NULL
  

  Query details:
  • total number of resulted rows for SELECT is 234
  • number of rows after first JOIN is 49977
  • number of rows after second JOIN and before GROUP_BY is 998
• Second URL is executing SELECT and UPDATE SQL queries. Where slow UPDATE is:

  UPDATE
    `table_name_4`
     SET `parameter_1` = CASE
       WHEN (`table_name_4`.`id` = 810982) THEN 0
       WHEN (`table_name_4`.`id` = 810983) THEN 0
       WHEN (`table_name_4`.`id` = 811066) THEN 0
       WHEN (`table_name_4`.`id` = 811075) THEN 0
       WHEN (`table_name_4`.`id` = 811076) THEN 0
       WHEN (`table_name_4`.`id` = 811077) THEN 0
       WHEN (`table_name_4`.`id` = 811078) THEN 0
       WHEN (`table_name_4`.`id` = 811079) THEN 0
       WHEN (`table_name_4`.`id` = 811103) THEN 0
       WHEN (`table_name_4`.`id` = 811159) THEN 0
       ELSE NULL
     END, `parameter_2` = CASE
       WHEN (`table_name_4`.`id` = 810982) THEN 0
       WHEN (`table_name_4`.`id` = 810983) THEN 0
       WHEN (`table_name_4`.`id` = 811066) THEN 0
       WHEN (`table_name_4`.`id` = 811075) THEN 0
       WHEN (`table_name_4`.`id` = 811076) THEN 0
       WHEN (`table_name_4`.`id` = 811077) THEN 0
       WHEN (`table_name_4`.`id` = 811078) THEN 0
       WHEN (`table_name_4`.`id` = 811079) THEN 0
       WHEN (`table_name_4`.`id` = 811103) THEN 0
       WHEN (`table_name_4`.`id` = 811159) THEN 0
       ELSE NULL
     END, `parameter_3` = CASE WHEN
  ...
  

  Query details:
  • updating 10 rows
  • Execution time is 10~15ms when executing manually
  • total number of cases is 10

Here is a graph of service response time:

We have 140 active clients and 12s average response time (for 2 containers)
So real average throughput is 140 / 12 = ~12 requests/s
In case of 6 containers average response time is about 6s

Here are two peaks on the DB performance graph:

Two tests with different configuration:

1. 6 running ECS containers
2. 2 running ECS containers

Here is an additional graph for same time range:

Questions:

1. Average active sessions number is bigger for 2 containers. What is means that query per container running time is slower for 2 containers (waiting queries of 2 containers accumulates faster than 6 containers is executing new queries with less wait time). Why may it happen? DB is waiting for some kind of Django query complete confirmation, but Django response is slower in case of 2 containers (because of server load)?
2. In case of 2 containers wait/io/table/sql/handler wait event is extremely slow (this event is related to UPDATE query below), but in case of 6 containers wait/synch/rwlock/innodb/btr_search_latch (related to SELECT) needs much more time than UPDATE query. Why are latency reasons changing depending on application computing performance?
3. Any other suggestions what I should check based on this graph.
4. How this graph should be correctly interpreted (if it possible without knowing other system logs)?

_{Improving of weird SQL queries above is not an object of discussion}