improve grid proxy queries

[xmonader]

proper indices and queries, make sure to include performance changes

[xmonader]

What is the status here? it's linked to a closed PR?

if already addressed in another PR, please make sure to link it here and update the project to 3.12

[MarioBassem]

the pr tried to introduce a way to improve contracts endpoint performance, but it was closed in favor of #356

this is already addressed in some issues:

• 347

• 265

• 418

• there should be an issue to improve farms endpoint performance

i did a pr #419 that did a few things:

• database schema changes
• added basic id indices (farm_id, node_id, contract_id, ...)
• update/improvement on farms query

[Omarabdul3ziz]

WIP: reviewed the mentioned pr and was working on separating the schema change part in another branch cause it may need updates in graphql and data migrationsm or mario was suggesting to create the aggregated tables on setup query.

[xmonader]

Status?

[Omarabdul3ziz]

there is no progress here yet, there were higher-priority issues I was working on. I started investigating the options we can do to optimize the queries and will keep the issue updated

[Omarabdul3ziz]

update (wip):

• created indices on the IDs for the (node/farm/contract/twin) tables, the most used column with where/join clauses.
• explored the possibility of combining Count and Scan queries it seems that each request happens twice one time to get the count and the other to get the rows itself. tried to minimize this by combining the two calls in one but there were some issues:
  • broked the paginations params page and size since the limit/offset will conflict with the count clause.
  • anyway gorm will handle this q.Count(&count).Limit(size).Offset((page - 1) * size).Find(&nodes).Error as two calls and will not introduce any enhancements so things are as is.
• started experimenting with adding some materialized views which will precompute and store the results of some of the complex queries like farmTableQuery/nodeTableQuery and then it will be faster to filter, this will require a refresher every now and then so we may add
  • a triggered refresher by the effected columns in this view
  • a scheduled refresher that repeatedly works on interval

[Omarabdul3ziz]

update (wip):

• most of the farm query slowness comes from joining a big node table subquery that already has a lot of joins. this joins is needed to do some filterations on farms based on node fields. was working on filtering the nodes first before join it on the table farm

[Omarabdul3ziz]

notes:

• we can try to get the count with a raw query no need to use the gorm count method.
• if we gonna go with the materialized views suggestion we should consider the refreshing trigger to not be too frequent because regularly updated fields like uptime and updatedAt on node table

[xmonader]

please make sure the queries that affect the clients and the dashboard get more priority

[MarioBassem]

i got up to speed with @Omarabdul3ziz , tried to fix the farms query, but for some reason some queries with specific filters tend to take too much time, still investigating why...

[MarioBassem]

dividing this into smaller issues:

• [ ] #555
• [ ] #556

[MarioBassem]

worked with @Omarabdul3ziz on creating a small cache table to prevent repeated calculations. the results are promising without adding any additional indices. todo:

• add triggers to keep the cache table updated
• execute table and triggers creation transaction at server startup

[Omarabdul3ziz]

update (wip) continued working with mario today on:

• adding another cache table for the public_ips, it aggregate all the info will need for public-ips on nodes/farms query and query looks clearer.
• fixing the filters to use the new cache tables.

[MarioBassem]

update:

• we added triggers to update the cache tables
• we also added tests to make sure triggers work correctly

we still need to improve tests quality, and add benchmarks

[Omarabdul3ziz]

update:

• we were working on writing modifiers to fire the created triggers to update the cache tables, we faced some edge cases will this and handled them on the trigger functions.
• part of the modifiers was a reuse of the generate script which needed some modifications to be customizable and work on both generate script and modifiers running with tests
• tests are runs fine now on the generated data. we still working on testing against devnet db dump

[Omarabdul3ziz]

https://github.com/threefoldtech/tfgrid-sdk-go/pull/533 is now merged and released with v0.13.0 and deployed on dev