V8-archive: TODO: Optimizations

[wellingguzman]

• [ ] Caching schema objects
• [ ] Caching Routes callable
• [ ] Remote database connection (#119)
• [ ] Large queries extreme slow down (#117, #156)
• [ ] Add a way to generate cache data beforehand (#171)
• [ ] Improve database schema. (Columns order and indexes) (#124)
• [ ] Implement a caching layer for schema objects (#21)
• [ ] Caching the router callable prevent the api for looking the callable on every request (#26)
• [ ] Implement response cache completely (#20)

Reordering columns, and adding index where is needed can improve database performance.
_More details will be added later when is time to work on these performance points._

The API takes a solid 5550ms to retrieve 1000 items. Even when fetching just the id field, it takes 4.5 seconds.
Querying the same data set in MySQL directly takes 8.8ms. I don't know what the API is doing, but it's adding a lot of latency 😬

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[sayore]

This.

My 1GB Server with 1 CPU should usually easily take on multiple MySQL Databases, but Directus seems to really suck out the performance even when the Server is idle.

[benydc]

@rijkvanzanten @benhaynes
I was able to debug this and found this execution $result = $statement->execute(); from AbstractTableGateway.php, takes 2 seconds, doesn't matter the statement or query used.
I have uploaded a video of the issue:
https://streamable.com/fj32f
screenshot:
https://imgur.com/a/NYe0Xey

I'm using remote MySQL 8 server with Directus Docker API/APP locally

[benhaynes]

As of now the PHP API is completely beholden to the ZendDB library, and our codebase has been carried along version-to-version for a while now... leading to a bit of piecemeal. We're looking into doing a larger refactor starting in the next month or so which would give complete focus to stability and performance.

The question now is if we should switch to a Node API — since that is our internal team's specialty and we're having a difficult time maintaining the PHP codebase. Would love any conceptual/strategic thoughts from the community while we review our options.

[janholas]

What is the status of this? We are thinking about using Directus as our main production CMS (thousands of articles, blogs and custom content widgets with a medium volume traffic), because we really like the admin GUI and the system capabilities and flexibility. I checked the generated DB schemas and I am a bit worried about performance because there are no indexes or FKs. Would adding them manually work? Would the deleting of items (constraints) or modifying collection schema still work?

[benhaynes]

We're wrapping up the final API bug fixes before moving into optimizations. That said, optimizations are never "complete" so this will likely be an ongoing effort.

Directus is a database wrapper, so you can add any indexes (etc) directly to the database and Directus will work alongside those edits. We just don't have a way to manipulate them through the API/App yet.

[benhaynes]

To achieve better clarity/visibility, we are now tracking feature requests within the Feature Request project board.

This issue being closed does not mean it's not being considered.