search

directus / v8-archive

Directus Database API — Wraps Custom SQL Databases with a REST/GraphQL API
https://docs.directus.io/api/reference.html
505 stars 204 forks source link

DB performance optimization #1924

Open westhouseit opened 4 years ago

westhouseit commented 4 years ago

We have one table that we've managed to reduce to only 300k records but are still having a number of performance issues with the DB queries. Below we'll add details of each concern as we investigate. My apologies if these were dealt with elsewhere, but I couldn't find any.

Directus 8.7.2 Ubuntu 18.04 4.15.0-101-generic mysql Ver 14.14 Distrib 5.7.30, for Linux (x86_64) using EditLine wrapper

  1. Full table scans for result counts https://github.com/directus/api/blob/dfb0b68dda5a6ef22a227ee48e8551bb2dc7e48f/src/core/Directus/Database/TableGateway/RelationalTableGateway.php#L2503

It seems that full table scans are done for most, if not all, queries to get result counts. Can this be limited to queries where the count metadata is requested?

  1. Full index scan for PK lookups. https://github.com/directus/api/blob/5587a6228ddbe389d043309e088526a23ed2f75b/src/core/Directus/Database/TableGateway/RelationalTableGateway.php#L2031

directus_files lookups by PK are done with a CAST on the wrong side of the comparison which prevents a single row index lookup. In our case simply moving the CAST from CAST(directus_files.id as CHAR) IN ('236235') to directus_files.id IN (CAST('236235' AS UNSIGNED)) reduces the query cost from 49602.20 to 1.00 and is more than an order of magnitude faster.

westhouseit commented 4 years ago

Re:2. https://github.com/directus/api/pull/901

The issue above indicates that a better solution than type-casting is needed, so perhaps default to no explicit casting, reject non-int, and enable a collection level option for non-numeric PKs. Especially considering this CAST above is done on a Directus table that should never be concerned with a non-numeric PK.

benhaynes commented 4 years ago

We're almost done rewriting the entire API in laravel... so hopefully @WoLfulus has considered these optimizations. 😄 Thanks for the insight!

westhouseit commented 4 years ago

Ah, brilliant! Great news about Laravel too. I had heard some mention of this but have been too busy to look into it further.

DB optimizations are easily missed as most web devs don't come from a DBA background. I'll see if I can help @WoLfulus with some testing using our data.

benhaynes commented 4 years ago

That would be awesome! We really appreciate any help we can get — especially on the DB optimization side. Thank you @westhouseit, and feel free to join us on our community Slack too... if you're interested in helping the core team with any of these optimizations, I'm happy to invite you into some of the private team chats.

https://directus.chat