Penyetelan performa untuk mssql-django

Artikel ini memberikan panduan tentang mengoptimalkan performa aplikasi Django saat menggunakan mssql-django backend dengan SQL Server.

Pengoptimalan koneksi

Kurangi beban koneksi dengan menyesuaikan pengaturan pooling, persistensi, dan batas waktu.

Aktifkan pengumpulan koneksi

Pengumpulan koneksi diaktifkan secara default. Pastikan fitur tersebut tidak dinonaktifkan di settings.py:

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

Gunakan CONN_MAX_AGE

Atur CONN_MAX_AGE untuk menjaga koneksi database tetap terbuka di seluruh permintaan, menghindari overhead membuat koneksi baru untuk setiap permintaan:

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",
        },
    },
}

Mengatur batas waktu kueri

Cegah kueri yang berjalan lama menghabiskan sumber daya tanpa batas waktu:

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,
        },
    },
}

Pengoptimalan kueri

Kurangi perjalanan pulang-pergi database dan jumlah kueri dengan teknik ORM ini.

Hindari pola kueri N+1

Gunakan select_related untuk hubungan kunci asing (kueri JOIN tunggal) dan prefetch_related untuk hubungan banyak ke banyak atau terbalik (kueri terpisah dengan klausa IN):

# 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)

Gunakan only() dan defer()

Batasi kolom yang diambil saat Anda tidak memerlukan semua bidang:

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

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

Gunakan nilai() dan values_list()

Saat Anda tidak memerlukan instans model, gunakan values() atau values_list() untuk kueri yang lebih ringan:

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

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

Bekerja dalam batas parameter 2.100

SQL Server membatasi setiap kueri hingga 2.100 parameter. Django menghasilkan kueri berparameter, sehingga operasi yang menghasilkan klausul besar IN atau daftar nilai massal dapat mencapai batas ini.

Pengoptimalan otomatis untuk klausa IN besar:

Saat sebuah panggilan filter(field__in=list) memiliki lebih dari 2,048 nilai, backend mssql-django secara otomatis memasukkan nilai-nilai tersebut ke dalam tabel sementara (dalam batch 1,000) dan menulis ulang kueri menjadi WHERE field IN (SELECT params FROM #Temp_params). Pengoptimalan ini menghindari batas parameter tanpa perubahan kode apa pun. Ini berlaku untuk semua __in pencarian, termasuk yang dihasilkan oleh prefetch_related(). Ambang batas 2.048 diatur oleh backend max_in_list_size() untuk tetap aman di bawah batas parameter 2.100 SQL Server.

Penulisan ulang ini memiliki biaya: membuat dan mengisi #Temp_params menambahkan aktivitas pulang pergi dan tempdb tambahan. Untuk daftar yang mendekati ambang batas, uji tolok ukur kedua pendekatan pada beban kerja Anda.

Ketika intervensi manual masih diperlukan:

Pengoptimalan tabel temp otomatis menangani __in pencarian, tetapi operasi ini masih dapat mencapai batas parameter 2.100 karena setiap nilai bidang adalah parameter terpisah:

  • bulk_create() atau bulk_update() dengan banyak objek dan banyak bidang
  • Ekspresi kompleks Q() dengan banyak kondisi berantai
  • Kasus di mana Anda ingin menghindari perjalanan pulang pergi yang diperlukan untuk mengisi #Temp_params (misalnya, ketika daftar yang lebih kecil dan normal IN (...) akan lebih cepat)

Solusi:

  1. Menggunakan batch_size pada operasi massal untuk menjaga setiap batch di bawah batas:

    # 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. Gugus kueri besar IN saat Anda ingin melewati mekanisme tabel sementara otomatis:

    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. Gunakan subkueri alih-alih mewujudkan daftar ID:

    # 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. Gunakan Prefetch dengan set kueri yang difilter untuk membatasi jumlah ID yang diteruskan ke 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

Operasi massal

Gunakan operasi massal untuk mengurangi jumlah perjalanan pulang-pergi database:

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)

Important

Saat menggunakan bulk_create atau bulk_update, atur batch_size berdasarkan jumlah bidang per objek. Backend bulk_batch_size() membatasi setiap batch hingga 1.000 baris dan menerapkan batas parameter yang konservatif 2050 / (fields * 2) pada keduanyabulk_create dan bulk_update. Tambahan / 2 disisihkan untuk dua parameter per kolom yang digunakan oleh bulk_update (satu untuk pencocokan CASE, satu untuk nilainya), dan pembagi yang sama juga diterapkan pada bulk_create sehingga jalur kode yang sama aman digunakan untuk kedua operasi.

Jika Anda menghilangkan batch_size, backend menghitung nilai aman secara otomatis. Anda juga dapat menentukan batch_size, dan sistem backend akan membatasinya lebih lanjut hingga batas aman.

Untuk informasi selengkapnya tentang parameter return_rows_bulk_insert dan default, lihat Operasi massal dengan mssql-django.

Strategi indeks

Django membuat indeks secara otomatis untuk ForeignKey, OneToOneField, dan kolom dengan db_index=True. Untuk indeks tambahan, gunakan Meta.indexes:

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"]),
        ]

Untuk indeks khusus SQL Server (seperti indeks dengan INCLUDE kolom), gunakan SQL mentah dalam migrasi:

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;",
        ),
    ]

mssql-django backend mendukung indeks pencakup (supports_covering_indexes = True dalam mssql/features.py). Pada semua versi Django yang didukung oleh mssql-django (3.2 dan yang lebih baru), Anda dapat menggunakan include parameter pada models.Index alih-alih SQL mentah:

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"),
        ]

Penempatan grup file

backend mssql-django memetakan db_tablespace milik Django ke klausul ON filegroup milik SQL Server. Gunakan ini untuk menempatkan tabel atau indeks pada grup file tertentu:

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

    class Meta:
        db_tablespace = "ARCHIVE_FG"

Ini menghasilkan: CREATE TABLE ... ON [ARCHIVE_FG].

Important

Grup file harus sudah ada di database SQL Server sebelum Anda menjalankan migrate. Buat dengan ALTER DATABASE [<your-database>] ADD FILEGROUP [ARCHIVE_FG] dan tambahkan setidaknya satu file ke dalamnya.

Fungsi jendela

Backend mendukung fungsi jendela SQL Server (supports_over_clause = True). Gunakan ekspresi Django Window untuk peringkat, total berjalan, dan perhitungan yang dipartisi:

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(),
    )
)

Note

SQL Server tidak mendukung NTH_VALUE(). Gunakan FIRST_VALUE, LAST_VALUE, atau solusi subkueri sebagai gantinya. Lihat Batasan dan fitur yang tidak didukung di mssql-django.

Memantau kinerja kueri

Gunakan pengelogan kueri bawaan Django untuk mengidentifikasi kueri lambat selama pengembangan:

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

Untuk beban kerja penahapan dan produksi, gunakan alat performa SQL Server untuk menganalisis SQL yang dihasilkan Django:

  1. Mulailah dengan laporan performa bawaan sebelum Anda mengkueri DMV secara langsung.

    Laporan ini biasanya menjadi cara tercepat untuk menemukan kueri berbiaya tinggi, waktu tunggu, pemblokiran, dan tekanan sumber daya, dengan kemungkinan kesalahan yang lebih kecil dibandingkan kueri DMV ad hoc.

  2. Gunakan Query Store untuk mengidentifikasi kueri yang paling banyak menggunakan sumber daya dan kueri yang baru-baru ini mengalami regresi.

  3. Gunakan tampilan Top Resource Consuming Queries, Regressed Queries, dan Query Wait Statistics di SQL Server Management Studio untuk menentukan apakah bottleneck disebabkan oleh CPU, I/O, memori, atau waktu tunggu. Untuk panduan, lihat Praktik terbaik untuk memantau beban kerja dengan Query Store.

  4. Buka rencana eksekusi aktual untuk pernyataan yang lambat guna mencari operasi pemindaian, pencarian kunci yang mahal, perkiraan jumlah baris yang tidak akurat, dan indeks yang hilang.

  5. Jika kueri menjadi lebih lambat setelah penyebaran atau perubahan skema, bandingkan rencananya di Query Store sebelum mengubah kode aplikasi. DBA dapat untuk sementara memaksakan rencana eksekusi yang sudah terbukti baik sembari Anda memperbaiki masalah pada indeks, statistik, atau struktur kueri yang mendasarinya.

Jika Query Store menunjukkan waktu tunggu alih-alih waktu CPU yang tinggi, gunakan Identifikasi hambatan untuk memisahkan masalah CPU, memori, I/O disk, tekanan koneksi, dan pemblokiran.

Konten terkait

  • Last updated on