Consultas no optimizadas

[JohanValero]

En el archivo "archihub-backend/app/api/views/services.py" (https://github.com/Archihub-App/archihub-backend/blob/master/app/api/views/services.py) el método "get_view_info()":

Está generando problemas de rendimiento de la consulta:

{"t":{"$date":"2025-09-02T01:05:01.029+00:00"},"s":"W", "c":"QUERY", "id":23799, "ctx":"conn327","msg":"Aggregate command executor error","attr":{"error":{"code":279,"codeName":"ClientDisconnect","errmsg":"operation was interrupted because a client disconnected"},"stats":{"stage":"mkbson","planNodeId":4,"nReturned":0,"opens":1,"closes":0,"saveState":18590,"restoreState":18589,"isEOF":0,"objSlot":15,"fields":[],"projectFields":["_id","n"],"projectSlots":[14,12],"forceNewObject":true,"returnOldObject":false,"inputStage":{"stage":"project","planNodeId":4,"nReturned":0,"opens":1,"closes":0,"saveState":18590,"restoreState":18589,"isEOF":0,"projections":{"14":"1 "},"inputStage":{"stage":"group","planNodeId":4,"nReturned":0,"opens":1,"closes":0,"saveState":18590,"restoreState":18589,"isEOF":0,"groupBySlots":[],"expressions":{"12":"sum(1) ","initExprs":{"12":null}},"mergingExprs":{"13":"sum(s13) "},"usedDisk":false,"spills":0,"spilledRecords":0,"spilledDataStorageSize":0,"inputStage":{"stage":"unique","planNodeId":3,"nReturned":18583302,"opens":1,"closes":0,"saveState":18590,"restoreState":18589,"isEOF":0,"dupsTested":18583302,"dupsDropped":0,"keySlots":[11],"inputStage":{"stage":"union","planNodeId":3,"nReturned":18583302,"opens":1,"closes":0,"saveState":18590,"restoreState":18589,"isEOF":0,"inputSlots":[5,8],"outputSlots":[11],"inputStages":[{"stage":"unique","planNodeId":1,"nReturned":18583302,"opens":1,"closes":1,"saveState":18590,"restoreState":18589,"isEOF":1,"dupsTested":18583302,"dupsDropped":0,"keySlots":[5],"inputStage":{"stage":"cfilter","planNodeId":1,"nReturned":18583302,"opens":1,"closes":1,"saveState":18590,"restoreState":18589,"isEOF":1,"numTested":1,"filter":"(exists(s6) && exists(s7)) ","inputStage":{"stage":"ixseek","planNodeId":1,"nReturned":18583302,"opens":1,"closes":1,"saveState":18590,"restoreState":18589,"isEOF":1,"indexName":"parents.id_1","keysExamined":18583302,"seeks":1,"numReads":18583303,"recordIdSlot":5,"outputSlots":[],"indexKeysToInclude":"00000000000000000000000000000000","seekKeyLow":"s6 ","seekKeyHigh":"s7 "}}},{"stage":"unique","planNodeId":2,"nReturned":0,"opens":1,"closes":0,"saveState":18590,"restoreState":18589,"isEOF":0,"dupsTested":0,"dupsDropped":0,"keySlots":[8],"inputStage":{"stage":"cfilter","planNodeId":2,"nReturned":0,"opens":1,"closes":0,"saveState":18590,"restoreState":18589,"isEOF":0,"numTested":1,"filter":"(exists(s9) && exists(s10)) ","inputStage":{"stage":"ixseek","planNodeId":2,"nReturned":0,"opens":1,"closes":0,"saveState":18590,"restoreState":18589,"isEOF":0,"indexName":"parent.id_1","keysExamined":0,"seeks":0,"numReads":0,"recordIdSlot":8,"outputSlots":[],"indexKeysToInclude":"00000000000000000000000000000000","seekKeyLow":"s9 ","seekKeyHigh":"s10 "}}}]}}}}},"cmd":{"aggregate":"records","pipeline":[{"$match":{"$or":[{"parents.id":"66acc915a58c7bfd59b83353"},{"parent.id":"66acc915a58c7bfd59b83353"}]}},{"$group":{"_id":1,"n":{"$sum":1}}}],"cursor":{},"lsid":{"id":{"$uuid":"0dc1bade-15c9-49d5-8c10-b33881c39871"}},"$clusterTime":{"clusterTime":{"$timestamp":{"t":1756775030,"i":1}},"signature":{"hash":{"$binary":{"base64":"K4+g9R6yO3rMl6uTJYUKT5BLO3Y=","subType":"0"}},"keyId":7497002471869382657}},"$db":"archihub-prod"}}}
{"t":{"$date":"2025-09-02T01:06:04.185+00:00"},"s":"I", "c":"COMMAND", "id":51803, "ctx":"conn310","msg":"Slow query","attr":{"type":"command","ns":"archihub-prod.records","command":{"aggregate":"records","pipeline":[{"$match":{"$or":[{"parents.id":"66acc915a58c7bfd59b83353"},{"parent.id":"66acc915a58c7bfd59b83353"}]}},{"$group":{"_id":1,"n":{"$sum":1}}}],"cursor":{},"lsid":{"id":{"$uuid":"1929744a-dcca-4256-996f-8a44a3d177f2"}},"$clusterTime":{"clusterTime":{"$timestamp":{"t":1756775070,"i":1}},"signature":{"hash":{"$binary":{"base64":"1MnjdKv/tUTmASsAqUJ10kDhGY8=","subType":"0"}},"keyId":7497002471869382657}},"$db":"archihub-prod"},"planSummary":"IXSCAN { parents.id: 1 }, IXSCAN { parent.id: 1 }","planningTimeMicros":121,"numYields":18589,"queryHash":"05509619","planCacheKey":"F88CFD61","queryFramework":"sbe","ok":0,"errMsg":"PlanExecutor error during aggregation :: caused by :: operation was interrupted because a client disconnected","errName":"ClientDisconnect","errCode":279,"reslen":326,"locks":{"FeatureCompatibilityVersion":{"acquireCount":{"r":18590}},"Global":{"acquireCount":{"r":18590}}},"readConcern":{"level":"local","provenance":"implicitDefault"},"writeConcern":{"w":"majority","wtimeout":0,"provenance":"implicitDefault"},"cpuNanos":92200410077,"remote":"192.168.7.97:47564","protocol":"op_msg","durationMillis":92885}}

Aun cuando se crearon índices para la tabla por motivos de rendimiento la consulta requiere consultar 18.5 millones de documentos. Pero toma más de 77 segundos de tiempo de CPU generando problemas de timeout de 81.872, 79.638, y 81.305 en diferentes intentos, lo que significa que la consulta está tardando entre 79 y 82 segundos antes de ser interrumpida.

Esto aplica especificamente para un aplicativo con más de 18 millones de registros en productivo.

[nestorandrespe]

Hola Johan, ahora actualizo la consulta para que sea más eficiente. Una pregunta los índices que dices se hicieron para que campos?

db.records.createIndex({ "parent.post_type": 1 });
db.records.createIndex({ "parents.id": 1 });
db.records.createIndex({ "parent.id": 1 });

eso puede ayudar pero no va a ser todo, voy a cambiar la consulta ahora en la mañana y te aviso

[nestorandrespe]

En la rama ajustes_pre_0.9.13 se actualizó la consulta a la base de datos, cuéntanos como te va con esa después de generar los índices de arriba:

https://github.com/Archihub-App/archihub-backend/blob/ajustes_pre_0.9.13/app/api/views/services.py

[nestorandrespe]

Una nota importante: esa versión aún está en desarrollo y no está lista para producción. Intenta usar la consulta independiente de la rama.

[JohanValero]

Sí, los índices creados en nuestro ambientes son:

db.records.createIndex({ "parents.id": 1 });
db.records.createIndex({ "parent.id": 1 });

Se recomienda configurar la obtención de la cantidad de registros de forma separada para mostrarlo en el frontend sin comprometer la información del view.

[nestorandrespe]

Con la consulta actualizada sin hacer la agregación los tiempos mejoran muchísimo. La razón por la cual está en el mismo lugar es porque en la interfaz de administración solo se usa en ese punto. Si estas trabajando en una interfaz a parte y la solución propuesta no sirve podemos mejorarlo :) quedo atento