Cbioportal: GENIE Results query never finishes

[inodb]

https://bit.ly/3rBgaAO

There is a call that does not seem to finish and ends with 504 to https://genie.cbioportal.org/api/mutations/fetch?projection=DETAILED

Reported by user: https://groups.google.com/g/cbioportal/c/bJ-9VnfzQb8/m/pkQ-oIBsCAAJ

[averyniceday]

show full processlist;

For the potential problem query (stuck in 'Statistics' state) this will return: ERROR 2020 (HY000): Got packet bigger than 'max_allowed_packet' bytes

select info from information_schema.processlist where id = 26218;

This will show query up to a certain point, but will end prematurely. Still trying to figure out how to get the entire query out without redeploying GENIE with logging or anything like that.

Potentially change max_allowed_packet to 1GB

[averyniceday]

Possibility 1: Hanging while optimizing the query optimizer_search_depth
https://mariadb.com/resources/blog/setting-optimizer-search-depth-in-mysql/
https://stackoverflow.com/questions/17797191/sql-query-stuck-in-statistics-state

Problem I'm running into is we need super privileges to change this setting for testing, but none of the dbs we work on have an account with super privileges. So we need to dump genie or an equivalently big db (or just genie) and load into another db. (Manda has privileges on dashidev!!! We're saved!! and msk but we don't do that)

Possibility 2: It's just inevitable performance problems with the query dealing with a large amount of samples.

Possibility 3: This worked before, but using DETAILED projection from frontend introduces additional complexity.
To address this, might need to check with frontend or see new PRs that may have changed how calls are passed.

Likely that the mapper code we're looking at is in here: https://github.com/cBioPortal/cbioportal/blob/master/persistence/persistence-mybatis/src/main/resources/org/cbioportal/persistence/mybatis/MutationMapper.xml

Note: Karthik says that this problem is gene-specific. But I haven't seen this - I tested a similar query to the one above provided by Ino but with TP53 and then with APC (as opposed to TET2) and saw same 504/slow performance problem.

[inodb]

@averyniceday Thanks for the detailed analysis!

That looks like some db misconfiguration (the max_allowed_packet). Agree with your list of possibilities. Gonna try to up the max_allowed_packet for now to see if that can be a quick fix

It was

So changed it to 512MB for now. Guess we could also try 1GB as you proposed

EDIT: Tried 1GB as well but that didn't seem to solve it

I'm not entirely sure where to see the max_allowed_packet error btw. Let me know if you still see it

[mandawilson]

On Rob's AWS mysql (not RDS) instance I ran:

mysql> SET GLOBAL max_allowed_packet=1073741824;
Query OK, 0 rows affected (0.00 sec)

• [ ] DELETE cbioportal_importer@pipelines:/data/portal-cron/cbio-portal-data/portal-configuration/k8s-config-vars/public/config_map_genie_debug.yaml

• [ ] Remove config map kubectl delete -f /data/portal-cron/cbio-portal-data/portal-configuration/k8s-config-vars/public/config_map_genie_debug.yaml

• [ ] DELETE cbioportal_importer@pipelines:/data/portal-cron/git-repos/knowledgesystems-k8s-deployment/cbioportal/cbioportal_backend_genie_debug.yaml

• [ ] Remove pods kubectl delete -f cbioportal_backend_genie_debug.yaml

• [ ] Make sure Rob and Avery know I changed the password for cgds_user@% on 107.22.28.88

Created config map cbioportal-genie-debug

kubectl apply -f config_map_genie_debug.yaml 
configmap/cbioportal-genie-debug created

cbioportal_importer@pipelines:/data/portal-cron/git-repos/knowledgesystems-k8s-deployment/cbioportal> cp cbioportal_backend_genie_public.yaml cbioportal_backend_genie_debug.yaml

[inodb]

@mandawilson did increasing the max_allowed_packet solve the issue?

Currently using 1024000000, but that still results in 504 error

[mandawilson]

@inodb We are working in another database because to change the max_allowed_packet in the genie database I think we would have to change it through the Amazon website and restart the database server. And we think changing it would only allow us to actually get the full query for testing using show full processlist. Right now we can't even get the full query because it is too big. I am still in the process of connecting a cbioportal to the new database to see if we can reproduce the error there.

[inodb]

@mandawilson gotcha that makes sense. You can btw change the parameters without restarting. I changed production in the AWS RDS dashboard to:

+--------------------+------------+
| Variable_name      | Value      |
+--------------------+------------+
| max_allowed_packet | 1024000000 |
+--------------------+------------+
1 row in set (0.99 sec)

But that didn't seem to solve the issue unf

[mandawilson]

I turned off authentication/authorization.

kubectl exec -it cbioportal-backend-genie-debug-77c78c99b9-rtbqh /bin/bash

apt-get update
apt-get install curl


curl "http://localhost:8888/results/cancerTypesSummary?cancer_study_list=brca_akt1_genie_2019%2Cglioma_dfci_2020%2Cgenie_public_6_1%2Cgenie_public%2Cmbc_genie_2020&Z_SCORE_THRESHOLD=2.0&RPPA_SCORE_THRESHOLD=2.0&data_priority=0&profileFilter=0&case_set_id=all&gene_list=TET2&geneset_list=%20&tab_index=tab_visualize&Action=Submit"

[mandawilson]

@averyniceday Ino changed the max_allowed_packet to 1G but we still get the same error.

I have a portal set up with Rob's mysql database for testing. Connect with:

kubectl exec -it cbioportal-backend-genie-debug-77c78c99b9-rtbqh /bin/bash

The above curl command doesn't work but it would better to query the API directly I expect. Although we would need to get that results_body.json file onto the pod and then use the curl command you sent me. Actually do you still need the session id to get to that URL I tried above even without authentication? I can't remember.

Rob's mysql instance has all the studies that are queried in our example that fails so we should be able to reproduce this.

I looked a bit at the frontend code and I don't think that using DETAILED is new. We could just try changing it to SUMMARY and seeing if it works.

I will try to answer any questions you have in slack.

[averyniceday]

Starting to look at this into now, just finished reading through the chain.

For @inodb , the error with the max_allowed_packet only affects finding the exact query being run and not the general performance/page loading. Can be observed as follows:

When hitting the query page, Show processlist eventually shows a hanging query inside the sql db with state statistics. It might take some time to pinpoint the exact one but once the hanging process appears, show full processlist will return the error because I'm guessing the query is too large.

For @mandawilson For the sessionid I think this is dependent on whether or not auth is turned on. If we have any time of auth, I think we will need a session id. We could also generate a data access token but not sure if that will work (if using keycloak auth) without some finagling. Both of these might require an actual frontend/publicly accessible website that we can hit and then pull a session id from the network tab.

Just confirmed that there's no auth and we do not need to pass in a sessionid/token to hit the backend api. Tested with the study endpoint
curl localhost:8888/api/studies/

Alternatively we could hack by using old auth method (non-keycloak) and uuid dat method and manually inserting something into the data_access_token db.

Or we could turn off auth. (this is probably easiest)

I am going to start looking into your deployed cbio!

[kalletlak]

Problem might be related to having huge in clause for multiple fields. Here is the temporary fix https://github.com/cBioPortal/cbioportal/pull/8495

[averyniceday]

Has this been confirmed to work for GENIE @inodb ? Should I close this issue?

I'm still trying to get reliable behavior in testing but seems like I'm not getting the same behavior for the endpoint on our test portal (which might be using a newer sql version) . Should I continue looking into it?

[inodb]

@averyniceday yes we can close

[inodb]

Let's maybe have a meeting about GENIE specific performance improvement, we can look at these issues: https://github.com/cBioPortal/cbioportal/issues?q=is%3Aopen+is%3Aissue+label%3Aperformance