[backend] performance issues with list runs API

[deepk2u]

Environment

• How did you deploy Kubeflow Pipelines (KFP)?
  full kubeflow deployment using manifests repo
• KFP version:
  sdk-2.0.0b7
• KFP SDK version:
  sdk-2.0.0b7

Steps to reproduce

1. create more than 200k runs in at least one namespace
2. other namespaces can have any number of runs
3. click on the runs tab on Kubeflow UI, it starts to timeout

I did some digging and found that in case if the number of runs is really high, then the select query starts to take more than a minute.

select * from run_details where Namespace = 'namespace-with-200k-runs' limit 1; this query took 1 minute 46 sec

I tried to query using the experiment tab, where we pass the experiment id, and that query is still performing as expected.
select * from run_details where ExperimentUUID = 'a0dd8afa-d481-4c83-b2c2-31ef4b3d12ec' limit 1; this query is taking milliseconds

On a side note, 200k runs are really high, and I checked. Someone created a few Recurring Runs for that particular run using a cron schedule, which was running every second. That was a mistake. I feel we should add some warning below the cron schedule box if it is per minute or per second, or there should be a mechanism to not allow these kinds of cron schedules for administrators.

Expected result

Listing runs should not time out, irrespective of how much data we have in the database.

Materials and Reference

Below is a snapshot of the data we have in the run_details table.

mysql> select count(*) from run_details;
+----------+
| count(*) |
+----------+
|   227025 |
+----------+
1 row in set (1.91 sec)

mysql> SELECT Namespace,COUNT(*) as count FROM run_details GROUP BY Namespace ORDER BY count DESC;
+-----------------------------------------------------+--------+
| Namespace                                           | count  |
+-----------------------------------------------------+--------+
| n1      | 219437 |
| n2       |   2032 |
| n3                 |   1478 |
| n4            |   1090 |
| n5        |    384 |
| n6      |    367 |
| n7            |    285 |
| n8    |    283 |
.....
56 rows in set (1 min 45.73 sec)

To fix this query, I manually created an Index on the Namespace column and everything seems to be running fine for now.

mysql> show indexes from run_details;
+-------------+------------+-------------------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name                                  | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+-------------------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| run_details |          0 | PRIMARY                                   |            1 | UUID            | A         |      108032 |     NULL | NULL   |      | BTREE      |         |               |
| run_details |          1 | experimentuuid_createatinsec              |            1 | ExperimentUUID  | A         |         148 |     NULL | NULL   |      | BTREE      |         |               |
| run_details |          1 | experimentuuid_createatinsec              |            2 | CreatedAtInSec  | A         |      112366 |     NULL | NULL   |      | BTREE      |         |               |
| run_details |          1 | experimentuuid_conditions_finishedatinsec |            1 | ExperimentUUID  | A         |         150 |     NULL | NULL   |      | BTREE      |         |               |
| run_details |          1 | experimentuuid_conditions_finishedatinsec |            2 | Conditions      | A         |         254 |     NULL | NULL   |      | BTREE      |         |               |
| run_details |          1 | experimentuuid_conditions_finishedatinsec |            3 | FinishedAtInSec | A         |      112366 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------------+------------+-------------------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.00 sec)

mysql> CREATE INDEX namespace ON run_details (Namespace);
Query OK, 0 rows affected (8 min 52.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> show indexes from run_details;
+-------------+------------+-------------------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name                                  | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+-------------------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| run_details |          0 | PRIMARY                                   |            1 | UUID            | A         |      108032 |     NULL | NULL   |      | BTREE      |         |               |
| run_details |          1 | experimentuuid_createatinsec              |            1 | ExperimentUUID  | A         |         148 |     NULL | NULL   |      | BTREE      |         |               |
| run_details |          1 | experimentuuid_createatinsec              |            2 | CreatedAtInSec  | A         |      112367 |     NULL | NULL   |      | BTREE      |         |               |
| run_details |          1 | experimentuuid_conditions_finishedatinsec |            1 | ExperimentUUID  | A         |         150 |     NULL | NULL   |      | BTREE      |         |               |
| run_details |          1 | experimentuuid_conditions_finishedatinsec |            2 | Conditions      | A         |         254 |     NULL | NULL   |      | BTREE      |         |               |
| run_details |          1 | experimentuuid_conditions_finishedatinsec |            3 | FinishedAtInSec | A         |      112367 |     NULL | NULL   | YES  | BTREE      |         |               |
| run_details |          1 | namespace                                 |            1 | Namespace       | A         |          55 |     NULL | NULL   |      | BTREE      |         |               |
+-------------+------------+-------------------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.00 sec)

---

Impacted by this bug? Give it a 👍.

[Linchin]

Hi @deepk2u, thank you for bringing this issue up and offer a solution! If you would like to create a PR to fix this problem, it would be great.

[tam0201]

We also impacted by this issue, most of our namespace contains > 200k runs. Anh now we cannot view any run on UI. Hope there will be a fix soon!

[deepk2u]

@Linchin @tam0201 I have tried to fix it in my PR #9806 . Please take a look when you have time.