Oracle: a compiled Cypher statement kept in the driver's statement cache holds megabytes of PGA, and a few dozen run the instance out (ORA-04036)

Fermée
  • Quelque chose ne va pas ?
#218 0 commentaires 0 réactions 0 personnes assignées Voir sur GitHub

Les mainteneurs répondent en général sous 1 jour

Une pull request liée a déjà été fusionnée.

  • #221 par @jasperblues — fusionnée

Évaluation

Difficulté
4/5
Temps estimé
3-5 jours
Accessibilité débutants
57/100
Type d'issue
Bug
Clarté
Clairement spécifiée
Activité
Active
Stack technique
postgresql, python
Domaine
database, performance

Piste de recherche

Start with drivine/connection/oracle/oracle_connection.py and drivine/sync/connection/oracle/oracle_connection.py, then check how OracleConnection.query and open_cursor prepare and execute Cypher statements. Port the described long-text memory test, measuring session PGA through v$sesstat as system; done means Cypher statements avoid the driver's statement cache and the test stays below 200 MB.

Rédigé par le modèle d'indexation à partir du texte de l'issue.

Description

Port of liberation-data/drivine4j#313.

What happens here now. On Oracle, Oracle keeps a cursor's memory for as long as the driver keeps its statement in its statement cache. In drivine4j a compiled Cypher statement held about 4 MB of PGA while cached, 7 MB at 5,000 characters, and 10 to 24 MB when it read a text past 32 KB. A few dozen distinct statements took the instance from 300 MB to over 1.3 GB on one session. With more sessions that runs past Oracle Free's 2 GB PGA_AGGREGATE_LIMIT (ORA-04036). The memory is held on the server, not in the client, so any driver that caches the statement does the same.

OracleConnection.query and open_cursor (drivine/connection/oracle/oracle_connection.py:61 and :88, and drivine/sync/connection/oracle/oracle_connection.py:63 and :90) run a compiled Cypher statement with python-oracledb's default statement cache (stmtcachesize 20 per connection). The pool is set up in oracle_connection_provider.py with max=pool_max. So up to 20 compiled statements per session stay cached, each holding megabytes. This isn't measured here yet, but the cause is on the server side.

The fix in drivine4j (liberation-data/drivine4j#352): a statement compiled from Cypher isn't kept in the driver's cache on Oracle (statement.isPoolable = false), so it is closed when it ends. Drivine's own statements stay cached. The python-oracledb equivalent is cursor.prepare(statement, cache_statement=False) before execute, done only when the statement came from Cypher.

Tests to port.

  • OracleLongTextMemoryTest: 90 distinct long-text statements (given WITH $t AS t, stored MATCH (n) WITH n.t AS t, made WITH $a + $b AS t, each with left(t, size(t) - i)) on one session. It reads that session's session pga memory from v$sesstat as system and asserts it grew by less than 200 MB. In drivine4j it grew by 1,134 MB before the fix and by 37 MB after.
  • The long-text test now runs stored and made texts at 40,000 characters too, not only given ones.
Langage dominant
Python
Étoiles
1
Forks
0
Merge moyen
1 h 28 min
PR mergées (30 j)
144

Préparer son environnement

Ce projet ne fournit ni conteneur de développement, ni Dockerfile, ni guide de contribution : l'installation est à votre charge. Commencez par son README, et consultez notre guide de la première contribution pour les étapes générales.

Par où commencer

  1. Lisez l'issue en entier, puis le guide de contribution du projet.
  2. Signalez en commentaire que vous la prenez — cela évite que deux personnes fassent le même travail.
  3. Forkez le dépôt et travaillez sur une branche.
  4. Ouvrez une pull request qui référence le numéro de l'issue.

Autres issues de liberation-data/drivine-python

Toutes les issues de liberation-data/drivine-python

Issues similaires

Plus d'issues Python

Recevez les nouvelles issues par e-mail

Un résumé court des issues GitHub adaptées aux débutants.