Настройка производительности для mssql-django

В этой статье приводятся рекомендации по оптимизации производительности приложений Django при использовании серверной части mssql-django с SQL Server.

Оптимизация подключения

Уменьшение затрат на подключение путем настройки пула, сохраняемости и параметров времени ожидания.

Включение пула подключений

Пул подключений включен по умолчанию. Убедитесь, что это не отключено в вашей учетной записи settings.py:

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

Используйте CONN_MAX_AGE

Установите значение CONN_MAX_AGE, чтобы подключения к базе данных оставались открытыми между запросами, избегая накладных расходов на установку нового подключения для каждого запроса:

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

Установка времени ожидания запроса

Предотвратите, чтобы длительно выполняющиеся запросы потребляли ресурсы бесконечно:

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

Оптимизация запросов

Уменьшите количество обходов и запросов базы данных с помощью этих методов ORM.

Избегайте шаблонов запросов N+1

Используйте select_related для связей по внешнему ключу (один JOIN-запрос) и prefetch_related для связей «многие ко многим» или обратных связей (отдельный запрос с оператором 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)

Используйте only() и defer()

Ограничьте количество получаемых столбцов, если вам не нужны все поля:

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

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

Использование values() и values_list()

Если экземпляры модели не нужны, используйте values() или values_list() для более легких запросов:

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

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

Работа в пределах ограничения 2100 параметров

SQL Server ограничивает каждый запрос 2100 параметрами. Django создает параметризованные запросы, поэтому операции, которые создают большие IN предложения или списки массовых значений, могут достигнуть этого предела.

Автоматическая оптимизация больших выражений IN:

Когда у вызова filter(field__in=list) более 2 048 значений, серверный компонент mssql-django автоматически вставляет эти значения во временную таблицу (пакетами по 1 000) и переписывает запрос в виде WHERE field IN (SELECT params FROM #Temp_params). Эта оптимизация позволяет избежать ограничения параметров без каких-либо изменений кода. Это применяется ко всем запросам __in, включая созданные с помощью prefetch_related(). Пороговое значение 2048 задается серверной max_in_list_size() частью, чтобы безопасно оставаться в пределах 2100 параметров SQL Server.

Но у этой переработки есть цена: создание и заполнение #Temp_params добавляет дополнительные обращения к серверу и активность в tempdb. Для списков, близких к пороговому значению, сравните производительность обоих подходов на своей рабочей нагрузке.

Если вмешательство вручную по-прежнему необходимо:

Автоматическая оптимизация временных таблиц обрабатывает операции поиска __in, но эти операции по-прежнему могут упираться в ограничение в 2 100 параметров, поскольку каждое значение поля передаётся как отдельный параметр:

  • bulk_create() или bulk_update() со многими объектами и многими полями
  • Сложные Q() выражения с множеством цепочки условий
  • Случаи, когда вы хотите избежать обходных путей, необходимых для заполнения #Temp_params (например, когда меньший список и обычный IN (...) будет быстрее)

Решения:

  1. Использование batch_size при массовых операциях для поддержания каждого пакета в пределах ограничения:

    # 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. Разбивайте большие IN запросы на части, если требуется обойти механизм автоматического создания временных таблиц:

    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. Используйте вложенные запросы вместо материализации списков идентификаторов:

    # 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. Используйте Prefetch с отфильтрованными наборами запросов , чтобы ограничить количество идентификаторов, передаваемых 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

пакетные операции

Используйте массовые операции, чтобы сократить число обращений к базе данных:

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

При использовании bulk_create или bulk_update установите batch_size в зависимости от количества полей в каждом объекте. Серверная bulk_batch_size() часть ограничивает каждый пакет на 1000 строк и применяет консервативное 2050 / (fields * 2) ограничение параметров для обоихbulk_create и bulk_update. Дополнительное значение / 2 зарезервировано для двух параметров на каждое поле, которые использует bulk_update (один для CASE-сопоставления, один для значения), и тот же делитель применяется к bulk_create, поэтому один и тот же кодовый путь безопасен для обеих операций.

Если опущено batch_size, серверная часть автоматически вычисляет безопасное значение. Вы также можете указать batch_size, а серверная часть дополнительно ограничит его до безопасного предела.

Дополнительные сведения о параметрах return_rows_bulk_insert и default см. в статье Массовые операции с mssql-django.

Стратегии индексирования

Django автоматически создает индексы для ForeignKey, OneToOneField и полей с db_index=True. Для дополнительных индексов используйте 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"]),
        ]

Для индексов SQL Server (например, индексов с INCLUDE столбцами) используйте необработанный SQL в миграциях:

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 часть поддерживает охватывающие индексы (supports_covering_indexes = True в mssql/features.py). Для всех версий Django, поддерживаемых mssql-django (3.2 и выше), можно использовать параметр include в models.Index вместо чистого 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"),
        ]

Размещение группы файлов

Серверная часть mssql-django сопоставляет конструкцию Django db_tablespace с конструкцией SQL Server ON filegroup. Используйте это для размещения таблиц или индексов в определенных файловых группах:

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

    class Meta:
        db_tablespace = "ARCHIVE_FG"

Это приводит к следующему: CREATE TABLE ... ON [ARCHIVE_FG].

Important

Перед запуском migrateфайловая группа должна существовать в базе данных SQL Server. Создайте его с помощью ALTER DATABASE [<your-database>] ADD FILEGROUP [ARCHIVE_FG] и добавьте в него хотя бы один файл.

Функции окна

Серверная часть поддерживает функции окна SQL Server (supports_over_clause = True). Используйте выражения Django Window для ранжирования, выполнения итогов и секционированных вычислений:

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 не поддерживаетNTH_VALUE(). Используйте FIRST_VALUE, LAST_VALUE или обходное решение с подзапросом вместо этого. См. ограничения и неподдерживаемые функции в mssql-django.

Мониторинг производительности запросов

Используйте встроенное ведение журнала запросов Django для выявления медленных запросов во время разработки:

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

Для предпроизводственных и производственных нагрузок используйте средства анализа производительности SQL Server, чтобы анализировать SQL, который генерирует Django:

  1. Начните со встроенных отчетов о производительности, прежде чем напрямую обращаться к DMV.

    Как правило, эти отчёты — самый быстрый способ находить ресурсоёмкие запросы, ожидания, блокировки и нехватку ресурсов, при этом вероятность ошибок здесь ниже, чем при использовании ad hoc-запросов DMV.

  2. Используйте хранилище запросов, чтобы выявить запросы, потребляющие больше всего ресурсов, и запросы, производительность которых недавно ухудшилась.

  3. Используйте представления Запросы с наибольшим потреблением ресурсов, Регрессировавшие запросы и Статистика ожиданий запросов в SQL Server Management Studio, чтобы определить, что является узким местом: ЦП, ввод-вывод, память или ожидания. Рекомендации см. в статье Рекомендации по мониторингу рабочих нагрузок с помощью хранилище запросов.

  4. Откройте фактический план выполнения для медленного запроса, чтобы выявить сканирования, дорогостоящие операции поиска по ключу, неточные оценки числа строк и отсутствие индексов.

  5. Если запрос стал выполняться медленнее после развертывания или изменения схемы, сравните планы его выполнения в хранилище запросов, прежде чем изменять код приложения. Администратор базы данных может временно принудительно использовать заведомо корректный план выполнения, пока вы устраняете проблему с индексом, статистикой или структурой запроса.

Если хранилище запросов показывает ожидания вместо высокой загрузки ЦП, используйте Определение узких мест, чтобы различить проблемы, связанные с ЦП, памятью, дисковым вводом-выводом, нагрузкой из-за подключений и блокировками.

Связанный контент

  • Last updated on