Mise en commun de connexions avec mssql-python

Le pooling de connexions améliore les performances des applications en réutilisant les connexions de base de données au lieu d’en créer de nouvelles pour chaque requête. Ouvrir une connexion implique plusieurs étapes chronophages :

• Le pilote établit une prise réseau.
• Le pilote termine la poignée de main TLS.
• Le pilote s’authentifie auprès du serveur.
• Le pilote valide les paramètres de connexion.

Le pooling de connexion maintient les connexions ouvertes et disponibles pour réutilisation, afin que votre application n’ait pas à répéter ces étapes pour chaque requête.

Comportement par défaut

Le pooling de connexions est activé par défaut lorsque vous créez votre première connexion. Les paramètres par défaut sont :

import mssql_python

# Pooling is automatically enabled with defaults
conn = mssql_python.connect(connection_string)

Configurer le regroupement de connexions

Configurez le pooling avant de créer des connexions :

import mssql_python

# Configure custom pool settings
mssql_python.pooling(max_size=50, idle_timeout=300)

# Now create connections
conn = mssql_python.connect(connection_string)

Parameters

La pooling() fonction accepte les paramètres suivants :

Désactiver le pooling de connexions

Pour désactiver le pooling, appelez pooling() avec enabled=False avant de créer des connexions :

import mssql_python

mssql_python.pooling(enabled=False)

# Connections are now created and destroyed per use
conn = mssql_python.connect(connection_string)

Fonctionnement de la mutualisation

Isolation des chaînes de connexion

Chaque chaîne de connexion unique dispose de son propre pool de connexions indépendant. Les pools ne partagent pas les connexions entre différentes chaînes de connexions :

# These use separate pools
conn1 = mssql_python.connect("Server=<server1>;Database=<database1>;...")
conn2 = mssql_python.connect("Server=<server2>;Database=<database2>;...")

Cycle de vie de la connexion

Acquisition (création d’une connexion) :

1. Le pool supprime les connexions périmées (expirées pour cause d’inactivité).
2. Le pool tente de réutiliser une connexion existante :
   • Il vérifie si la connexion est active.
   • Cela réinitialise l’état de connexion.
   • Si les deux vérifications aboutissent, il renvoie la connexion.
3. Si aucune connexion réutilisable n’existe et que le pool est sous max_size, le pilote crée une nouvelle connexion.
4. Si le pool de connexions a atteint sa capacité maximale alors qu’il n’existe aucune connexion valide, le pilote renvoie une erreur.

Libérer (restitution d’une connexion) :

1. Si la piscine a de la capacité, elle stocke la connexion pour la réutilisation.
2. Si le pool est à max_size, le pilote ferme immédiatement la connexion.

Contrôles de santé des connexions

Le pilote effectue des vérifications de santé de la connexion avant de réutiliser une connexion en pool.

1. Vérification en direct : Garantit que la connexion réseau est toujours valide.
2. Vérification de réinitialisation : Réinitialise l’état de la session (niveau d’isolement, paramètres) pour une réutilisation propre.

Si l’une ou l’autre des vérifications échoue, le pool de connexions rejette la connexion et en crée une nouvelle.

Nettoyage automatique

• Délai d’inactivité : Le pilote ferme les connexions restées inutilisées pendant une durée supérieure à la valeur idle_timeout.
• Sortie du processus : Un atexit gestionnaire ferme toutes les connexions en pool lorsque le processus Python se termine.

Bonnes pratiques

Dimensionnez votre piscine de manière appropriée

Faites correspondre la taille de votre pool à la concurrence de votre application.

# For a web application with 20 concurrent requests
mssql_python.pooling(max_size=25)  # Slightly more than expected concurrency

Utiliser des gestionnaires de contexte

Les gestionnaires de contexte s’assurent que vous renvoyez correctement les connexions au pool.

with mssql_python.connect(connection_string) as conn:
    cursor = conn.cursor()
    cursor.execute("SELECT TOP 5 Name, ListPrice FROM Production.Product")
    rows = cursor.fetchall()
# Connection returned to pool

Gardez les chaînes de connexion cohérentes

Différents paramètres dans les chaînes de connexion créent des pools séparés.

# These create THREE separate pools (inefficient)
conn1 = mssql_python.connect("Server=<server>;Database=<database>;Encrypt=yes;")
conn2 = mssql_python.connect("SERVER=<server>;DATABASE=<database>;ENCRYPT=yes;")  # Different case
conn3 = mssql_python.connect("Server=<server>;Database=<database>;Encrypt=yes;", timeout=30)  # Extra parameter

# Use a constant connection string instead
CONNECTION_STRING = "Server=<server>;Database=<database>;Encrypt=yes;"
conn1 = mssql_python.connect(CONNECTION_STRING)
conn2 = mssql_python.connect(CONNECTION_STRING)  # Same pool

Considérez les limites de connexion Azure SQL

Azure SQL Database applique les limites de connexion en fonction du niveau de service. Les valeurs suivantes sont approximatives ; Consultez la documentation liée pour connaître les limites actuelles :

Réduisez votre max_size valeur en dessous de ces limites.

# For Azure SQL Standard S2 (120 limit)
mssql_python.pooling(max_size=100)  # Leave headroom

Ajustez le délai d’inactivité pour votre charge de travail

• Connexions fréquentes : utilisez une valeur plus longue idle_timeout pour maintenir les connexions au chaud.
• Connexions sporadiques : Utilisez une valeur plus idle_timeout courte pour libérer des ressources.

# High-frequency API: keep connections warm
mssql_python.pooling(idle_timeout=1800)  # 30 minutes

# Batch job running every hour: release between runs
mssql_python.pooling(idle_timeout=60)  # 1 minute

Limitations

L’implémentation actuelle présente certaines limites par rapport à d’autres pilotes :

Exemple : Modèle d’application web

L’exemple suivant de Flask montre comment les connexions sont regroupées de manière transparente entre les requêtes :

import mssql_python
from flask import Flask, g

app = Flask(__name__)

# Configure pooling at startup
mssql_python.pooling(max_size=20, idle_timeout=300)

def get_db():
    if 'db' not in g:
        g.db = mssql_python.connect(app.config['DATABASE_URL'])
    return g.db

@app.teardown_appcontext
def close_db(error):
    db = g.pop('db', None)
    if db is not None:
        db.close()  # Returns to pool

@app.route('/products')
def list_products():
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute("SELECT TOP 5 Name, ListPrice FROM Production.Product")
    return cursor.fetchall()

Reconnaissez l’épuisement de la piscine

Lorsque toutes les connexions de la piscine sont utilisées et que vous demandez une nouvelle connexion, vous constatez des symptômes tels que :

• Les connexions se bloquent ou expirent en attendant qu’une connexion se libère.
• Le débit des applications chute soudainement sous charge.
• La consommation de mémoire augmente à mesure que le pilote crée des connexions qu’il ne peut pas réutiliser.

Causes courantes :

• Les connexions ne sont pas renvoyées dans la piscine. Fermez toujours les connexions une fois terminé, ou utilisez des gestionnaires de contexte. Une connexion qui n’est pas fermée reste vérifiée.
• La piscine est trop petite pour la charge de travail. Si vous avez 50 requêtes simultanées mais seulement max_size=20, 30 requêtes sont en attente.
• Les requêtes de longue durée monopolisent des connexions. Interrompez les longues opérations ou utilisez des connexions dédiées pour le travail en lot.

Procédure de résolution :

# 1. Always use context managers to guarantee return
with mssql_python.connect(connection_string) as conn:
    cursor = conn.cursor()
    cursor.execute("SELECT ...")
    rows = cursor.fetchall()
# Connection returned to pool here, even if an exception occurs

# 2. Size the pool to match your concurrency
mssql_python.pooling(max_size=50)  # Match or slightly exceed expected concurrent connections

# 3. Reduce idle timeout if connections go stale
mssql_python.pooling(idle_timeout=120)

Contenu connexe

• Gestion des connexions
• Chaînes de connexion
• Réglage des performances