slow call in projects_db.py

[GitHK]

Webserver was restarted and running for a few minutes and this started showing up.

WARNING: servicelib.aiohttp.monitor_slow_callbacks:instrumented(42) - Executing took 0.699 seconds
  _     ._   __/__   _ _  _  _ _/_   Recorded: 15:39:44  Samples:  1
 /_//_/// /_\ / //_// / //_'/ //     Duration: 0.699     CPU time: 0.696
/   _/                      v3.4.2
Program: /home/scu/.venv/bin/gunicorn simcore_service_webserver.cli:app_factory --bind 0.0.0.0:8080 --worker-class aiohttp.GunicornWebWorker --workers=1 --name=webserver_osparc-master-02-master-simcore_master_webserver-1_2022-03-10_15:30:47_7 --log-level=warning --access-logfile=- --access-logformat=%a %t "%r" %s %b [%Dus] "%{Referer}i" "%{User-Agent}i"
0.500 instrumented  servicelib/aiohttp/monitor_slow_callbacks.py:23
└─ 0.500 instrumented  aiodebug/log_slow_callbacks.py:16
   └─ 0.500 _run  asyncio/events.py:79
      └─ 0.500 _remove_single_orphaned_service  simcore_service_webserver/garbage_collector_core.py:299
         └─ 0.500 is_node_id_present_in_any_project_workbench  simcore_service_webserver/projects/projects_api.py:519
            └─ 0.500 get_all_node_ids_from_workbenches  simcore_service_webserver/projects/projects_db.py:811
               └─ 0.500 __anext__  aiopg/sa/result.py:355
                  └─ 0.500 fetchone  aiopg/sa/result.py:388

[GitHK]

another one currently happening @sanderegg @pcrespov

[sanderegg]

Problem is recurring and seems to come from here:

   async def get_all_node_ids_from_workbenches(
        self, project_uuid: str = None
    ) -> Set[str]:
        """Returns a set containing all the workbench node_ids from all projects

        If a project_uuid is passed, only that project's workbench nodes will be included
        """

        if project_uuid is None:
            query = "SELECT json_object_keys(projects.workbench) FROM projects"
        else:
            query = f"SELECT json_object_keys(projects.workbench) FROM projects WHERE projects.uuid = '{project_uuid}'"

        async with self.engine.acquire() as conn:
            result = set()
            query_result = await conn.execute(query)
            async for row in query_result:
                result.update(set(row.values()))

            return result

[sanderegg]

@pcrespov I guess the part with

result.update(set(row.values()))

is synchronous. Maybe we should use fetchall instead...

Also it is funny that suddenly it started showing. I guess this might be linked with the new SQLAlchemy? this would imply it became very slightly slower and it now shows

[sanderegg]

this part of the code does not exist anymore.