Prestaties afstemmen voor mssql-django

Dit artikel bevat richtlijnen voor het optimaliseren van django-toepassingsprestaties bij het gebruik van de mssql-django back-end met SQL Server.

Verbindingsoptimalisatie

Verminder de overhead van de verbinding door pooling, persistentie en time-outinstellingen af te stemmen.

Groepsgewijze verbindingen inschakelen

Verbindingspooling is standaard ingeschakeld. Controleer of het niet is uitgeschakeld in uw settings.py:

# Keep this True (or omit it entirely) for best connection performance
DATABASE_CONNECTION_POOLING = True

Gebruik CONN_MAX_AGE

Ingesteld CONN_MAX_AGE om databaseverbindingen open te houden voor aanvragen, waardoor de overhead voor het tot stand brengen van een nieuwe verbinding voor elke aanvraag wordt vermeden:

DATABASES = {
    "default": {
        "ENGINE": "mssql",
        "NAME": "<your-database>",
        "USER": "<your-username>",
        "PASSWORD": "<your-password>",
        "HOST": "<your-server>",
        "PORT": "1433",
        "CONN_MAX_AGE": 600,  # Keep connections open for 10 minutes
        "OPTIONS": {
            "driver": "ODBC Driver 18 for SQL Server",
        },
    },
}

Time-out van query instellen

Voorkom dat langlopende query's onbeperkt systeembronnen verbruiken:

DATABASES = {
    "default": {
        "ENGINE": "mssql",
        "NAME": "<your-database>",
        "USER": "<your-username>",
        "PASSWORD": "<your-password>",
        "HOST": "<your-server>",
        "PORT": "1433",
        "OPTIONS": {
            "driver": "ODBC Driver 18 for SQL Server",
            "query_timeout": 30,
        },
    },
}

Queryoptimalisatie

Verminder het aantal retouren en query's van databases met deze ORM-technieken.

N+1-querypatronen voorkomen

Gebruik select_related voor foreign key-relaties (één JOIN-query) en prefetch_related voor veel-op-veel- of omgekeerde relaties (afzonderlijke query met IN-clausule):

# Bad: N+1 queries
orders = Order.objects.all()
for order in orders:
    print(order.customer.name)  # Each access triggers a query

# Good: Single JOIN query
orders = Order.objects.select_related("customer").all()
for order in orders:
    print(order.customer.name)  # No additional queries

# Good: Two queries instead of N+1
orders = Order.objects.prefetch_related("items").all()
for order in orders:
    for item in order.items.all():  # Uses prefetched data
        print(item.name)

Alleen gebruiken() en uitstellen()

Beperk de opgehaalde kolommen wanneer u niet alle velden nodig hebt:

# Retrieve only specific fields
products = Product.objects.only("name", "price").all()

# Defer loading of large fields
products = Product.objects.defer("description", "metadata").all()

Waarden() en values_list() gebruiken

Wanneer u geen modelinstanties nodig hebt, gebruikt u values() of values_list() voor lichtere query’s:

# Returns dictionaries instead of model instances
prices = Product.objects.values("name", "price")

# Returns tuples
names = Product.objects.values_list("name", flat=True)

Werken binnen de parameterlimiet van 2100

SQL Server beperkt elke query tot 2100 parameters. Django genereert geparameteriseerde query's, dus bewerkingen die grote IN-clausules of bulklijsten met waarden produceren, kunnen tegen deze limiet aanlopen.

Automatische optimalisatie voor grote IN-clausules:

Wanneer een filter(field__in=list) aanroep meer dan 2048 waarden heeft, worden de waarden automatisch door de mssql-django back-end ingevoegd in een tijdelijke tabel (in batches van 1000) en wordt de query herschreven als WHERE field IN (SELECT params FROM #Temp_params). Deze optimalisatie voorkomt de parameterlimiet zonder codewijzigingen. Het is van toepassing op alle __in zoekacties, met inbegrip van de zoekopdrachten die zijn gegenereerd door prefetch_related(). De drempelwaarde van 2048 wordt ingesteld door de back-end max_in_list_size() om veilig te blijven onder de parameterlimiet van 2100 van SQL Server.

Deze herschrijving heeft een prijs: het maken en vullen van #Temp_params veroorzaakt extra round-trips en tempdb-activiteit. Voor lijsten in de buurt van de drempelwaarde kunt u beide benaderingen in uw workload benchmarken.

Wanneer handmatige interventie nog steeds nodig is:

De automatische optimalisatie van temp-table verwerkt __in zoekopdrachten, maar deze bewerkingen kunnen nog steeds de parameterlimiet van 2100 bereiken omdat elke veldwaarde een afzonderlijke parameter is:

• bulk_create() of bulk_update() met veel objecten en veel velden
• Complexe Q() expressies met veel gekoppelde voorwaarden
• Gevallen waarin u de heen-en-weercommunicatie wilt vermijden die nodig is om #Temp_params te vullen (bijvoorbeeld wanneer een kleinere lijst en een gewone IN (...) sneller zouden zijn)

Oplossingen:

1. Gebruiken batch_size bij bulkbewerkingen om elke batch onder de limiet te houden:

   # Backend cap with 10 fields: min(1000, 2050 // 10 // 2) = 102 rows per batch
   # The backend applies the conservative // 2 divisor for both bulk_create and bulk_update.
   Product.objects.bulk_create(products, batch_size=100)
   

2. Grote INquery’s opsplitsen wanneer u het mechanisme voor tijdelijke tabellen wilt omzeilen:

   from itertools import islice
   
   def chunked_filter(queryset, field, values, chunk_size=2000):
       """Filter a queryset in chunks to stay within the 2,100 parameter limit."""
       results = []
       it = iter(values)
       while chunk := list(islice(it, chunk_size)):
           results.extend(queryset.filter(**{f"{field}__in": chunk}))
       return results
   
   # Returns a list of model instances, not a QuerySet
   products = chunked_filter(Product.objects, "pk", large_id_list)
   

3. Gebruik subquery's in plaats van id-lijsten te materialiseren:

   # Instead of: Order.objects.filter(product_id__in=list(Product.objects.values_list("id", flat=True)))
   # Use a subquery (Django generates a single SQL statement with no parameter explosion)
   Order.objects.filter(product__in=Product.objects.filter(active=True))
   

4. Gebruik Prefetch met gefilterde querysets om het aantal doorgegeven id's te beperken:prefetch_related()

   from django.db.models import Prefetch
   
   orders = Order.objects.prefetch_related(
       Prefetch("items", queryset=OrderItem.objects.select_related("product"))
   )[:500]  # Limit parent queryset size
   

Bulkbewerkingen

Gebruik bulkbewerkingen om het aantal interacties met de database te verminderen:

from decimal import Decimal

from myapp.models import Product

# Bulk create
new_products = [Product(name=f"Item {i}", price=Decimal("1.99") * i) for i in range(1000)]
Product.objects.bulk_create(new_products, batch_size=500)

# Bulk update: refetch so each instance has a primary key
products = list(Product.objects.filter(name__startswith="Item "))
for product in products:
    product.price *= Decimal("1.10")
Product.objects.bulk_update(products, ["price"], batch_size=500)

Zie return_rows_bulk_insert voor meer informatie over de default en parameters.

Indexstrategieën

Django maakt automatisch indexen voor ForeignKey, OneToOneFielden velden met db_index=True. Gebruik Meta.indexesvoor aanvullende indexen:

from django.db import models

class Product(models.Model):
    name = models.CharField(max_length=100, db_index=True)
    category = models.CharField(max_length=50)
    price = models.DecimalField(max_digits=10, decimal_places=2)
    created_at = models.DateTimeField(auto_now_add=True)

    class Meta:
        indexes = [
            models.Index(fields=["category", "price"]),
            models.Index(fields=["-created_at"]),
        ]

Gebruik onbewerkte SQL in migraties voor SQL Server-specifieke indexen (zoals indexen met INCLUDE kolommen):

from django.db import migrations

class Migration(migrations.Migration):
    dependencies = [("myapp", "0001_initial")]
    operations = [
        migrations.RunSQL(
            sql="CREATE INDEX IX_product_category ON myapp_product (category) INCLUDE (name, price);",
            reverse_sql="DROP INDEX IX_product_category ON myapp_product;",
        ),
    ]

De mssql-django backend ondersteunt dekkende indexen (supports_covering_indexes = True in mssql/features.py). Op alle Django-versies die worden ondersteund door mssql-django (3.2 en hoger), kunt u de include parameter models.Index gebruiken in plaats van onbewerkte SQL:

class Product(models.Model):
    name = models.CharField(max_length=100)
    category = models.CharField(max_length=50)
    price = models.DecimalField(max_digits=10, decimal_places=2)

    class Meta:
        indexes = [
            models.Index(fields=["category"], include=["name", "price"], name="ix_product_cat_cover"),
        ]

Plaatsing van bestandsgroepen

De mssql-django backend vertaalt Django's db_tablespace naar SQL Server's ON filegroup-clausule. Gebruik deze optie om tabellen of indexen op specifieke bestandsgroepen te plaatsen:

class LargeAuditLog(models.Model):
    timestamp = models.DateTimeField(auto_now_add=True)
    message = models.TextField()

    class Meta:
        db_tablespace = "ARCHIVE_FG"

Hiermee wordt het volgende gegenereerd: CREATE TABLE ... ON [ARCHIVE_FG].

Vensterfuncties

De back-end ondersteunt de vensterfuncties van SQL Server (supports_over_clause = True). Gebruik django-expressies Window voor classificatie, het uitvoeren van totalen en gepartitioneerde berekeningen:

from django.db.models import F, Window
from django.db.models.functions import Rank, RowNumber

# Rank products by price within each category
products = Product.objects.annotate(
    price_rank=Window(
        expression=Rank(),
        partition_by=F("category"),
        order_by=F("price").desc(),
    )
)

# Row numbers across the full result set
products = Product.objects.annotate(
    row_num=Window(
        expression=RowNumber(),
        order_by=F("created_at").asc(),
    )
)

Prestaties van query monitoren

Gebruik de ingebouwde logboekregistratie van Django om trage query's te identificeren tijdens de ontwikkeling:

LOGGING = {
    "version": 1,
    "handlers": {
        "console": {
            "class": "logging.StreamHandler",
        },
    },
    "loggers": {
        "django.db.backends": {
            "level": "DEBUG",
            "handlers": ["console"],
        },
    },
}

Voor faserings- en productieworkloads gebruikt u SQL Server prestatiehulpprogramma's om de SQL te analyseren die Django genereert:

1. Begin met ingebouwde prestatierapporten voordat u rechtstreeks DMV's opvraagt.

   • Gebruik prestatiedashboard in SQL Server Management Studio voor SQL Server en Azure SQL Managed Instance.
   • Voor Azure SQL Database gebruikt u Query Performance Insight voor Azure SQL Database.

   Deze rapporten zijn meestal de snelste manier om dure query's, wachttijden, blokkeringen en resourcedruk te vinden, met minder ruimte voor fouten dan ad-hoc DMV-query's.

2. Gebruik Query Store om query's te identificeren die de meeste resources verbruiken en query's waarvan de prestaties onlangs zijn verslechterd.

3. Gebruik de weergaven voor query's met het hoogste resourceverbruik, query's met teruggevallen prestaties en wachttijdstatistieken voor query's in SQL Server Management Studio om te bepalen of de bottleneck bij de CPU, I/O, het geheugen of wachttijden ligt. Raadpleeg Best practices voor het bewaken van workloads met Query Store voor richtlijnen.

4. Open een daadwerkelijk uitvoeringsplan voor de langzame instructie om te zoeken naar scans, dure sleutelzoekacties, onjuiste schattingen van rijen en ontbrekende indexen.

5. Als een query langzamer werd na een implementatie- of schemawijziging, vergelijkt u de plannen in Query Store voordat u toepassingscode wijzigt. Een DBA kan tijdelijk een plan afdwingen waarvan bekend is dat het goed werkt, terwijl u het onderliggende probleem met de index, de statistieken of de queryvorm oplost.

Als Query Store wachttijden weergeeft in plaats van een hoge CPU-tijd, gebruikt u Knelpunten identificeren om CPU, geheugen, schijf-I/O, verbindingsdruk en blokkeringsproblemen te scheiden.

Verwante inhoud

• Bulkbewerkingen met mssql-django
• Groepsgewijze verbindingen in mssql-django
• Logica en verbindingstolerantie opnieuw proberen met mssql-django
• naslaginformatie voor mssql-django-configuratie
• Prestatiedashboard
• Inzicht in queryprestaties voor Azure SQL Database
• Prestaties bewaken met behulp van de Query Store
• Een werkelijk uitvoeringsplan analyseren
• Optimalisatie van Django-databases