una - tablas en memoria sql server

Eficiencia de memoria(constante) e iteración optimizada de velocidad en una gran mesa en Django

Question

Tengo una mesa muy grande. Actualmente se encuentra en una base de datos MySQL. Yo uso django.

Necesito iterar sobre cada elemento de la tabla para calcular previamente algunos datos en particular (tal vez, si fuera mejor, podría hacer lo contrario, pero ese no es el punto).

Me gustaría mantener la iteración lo más rápido posible con un uso constante de la memoria.

Como ya está claro en Limitar el uso de memoria en un * Large * Django QuerySet y ¿Por qué iterar a través de un gran Django QuerySet consume grandes cantidades de memoria? , una simple iteración sobre todos los objetos en django matará a la máquina, ya que recuperará TODOS los objetos de la base de datos.

Hacia una solución

En primer lugar, para reducir el consumo de memoria, debe asegurarse de que DEBUG sea False (o mono parchee el cursor: desactive el registro SQL mientras mantiene los ajustes. ¿ERROR? ) Para asegurarse de que django no está almacenando cosas en las connections para la depuración.

Pero incluso con eso,

for model in Model.objects.all()

es un no ir.

Ni siquiera con la forma ligeramente mejorada:

for model in Model.objects.all().iterator()

El uso de iterator() le ahorrará algo de memoria al no almacenar el resultado de la caché internamente (¡aunque no necesariamente en PostgreSQL!); pero, al parecer, recuperará todos los objetos de la base de datos.

Una solución ingenua

La solución en la primera pregunta es dividir los resultados basados ​​en un contador por un chunk_size . Hay varias formas de escribirlo, pero básicamente todos se reducen a una consulta OFFSET + LIMIT en SQL.

algo como:

qs = Model.objects.all()
counter = 0
count = qs.count()
while counter < count:     
    for model in qs[counter:counter+count].iterator()
        yield model
    counter += chunk_size

Si bien esto es eficiente desde el punto de vista de la memoria (uso de memoria constante proporcional a chunk_size ), es muy pobre en términos de velocidad: a medida que OFFSET crece, tanto MySQL como PostgreSQL (y probablemente la mayoría de los DB) comenzarán a ahogarse y desacelerarse.

Una mejor solución

Una mejor solución está disponible en esta publicación de Thierry Schellenbach. Filtra en el PK, que es mucho más rápido que la compensación (qué tan rápido probablemente dependa del DB)

pk = 0
last_pk = qs.order_by(''-pk'')[0].pk
queryset = qs.order_by(''pk'')
while pk < last_pk:
    for row in qs.filter(pk__gt=pk)[:chunksize]:
        pk = row.pk
        yield row
    gc.collect()

Esto comienza a ser satisfactorio. Ahora Memoria = O (C), y Velocidad ~ = O (N)

Problemas con la solución "mejor"

La mejor solución solo funciona cuando PK está disponible en QuerySet. Desafortunadamente, ese no es siempre el caso, en particular cuando QuerySet contiene combinaciones de distintos (group_by) y / o valores (ValueQuerySet).

Para esa situación, la "mejor solución" no se puede usar.

¿Podemos hacerlo mejor?

Ahora me pregunto si podemos ir más rápido y evitar el problema con QuerySets sin PK. Quizás usando algo que encontré en otras respuestas, pero solo en SQL puro: usando cursores .

Como soy bastante malo con SQL en bruto, en particular en Django, aquí viene la verdadera pregunta:

¿Cómo podemos construir un mejor Iterator de Django QuerySet para tablas grandes?

Mi opinión de lo que he leído es que deberíamos usar cursores del lado del servidor (aparentemente (ver referencias) usando un Cursor Django estándar no conseguiría el mismo resultado, porque por defecto ambos conectores python-MySQL y psycopg almacenan en caché los resultados).

¿Esta sería realmente una solución más rápida (y / o más eficiente)?

¿Se puede hacer esto usando SQL sin formato en django? ¿O deberíamos escribir un código python específico según el conector de la base de datos?

Cursores del lado del servidor en PostgreSQL y en MySQL

Eso es todo lo que pude obtener por el momento ...

un Django chunked_iterator()

Ahora, por supuesto, lo mejor sería que este método funcionara como queryset.iterator() , en lugar de iterate(queryset) , y ser parte de django core o al menos una aplicación conectable.

Actualización Gracias a "T" en los comentarios para encontrar un boleto django que contenga información adicional. Las diferencias en el comportamiento de los conectores hacen que, probablemente, la mejor solución sea crear un método de chunked específico en lugar de un iterator extienda de manera transparente (parece un buen enfoque para mí). Existe un trozo de implementación, pero no ha habido ningún trabajo en un año, y no parece que el autor esté listo para saltar sobre eso todavía.

Refs adicionales:

1. ¿Por qué MYSQL LIMIT offset más alto ralentiza la consulta?
2. ¿Cómo puedo acelerar una consulta MySQL con una compensación grande en la cláusula LIMIT?
3. http://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/
4. postgresql: offset + limit llega a ser muy lento
5. Mejora del rendimiento de OFFSET en PostgreSQL
6. http://www.depesz.com/2011/05/20/pagination-with-fixed-order/
7. Cómo obtener un ResultSet MySQL fila por fila en el Piton Server Side Cursor en MySQL

Ediciones:

Django 1.6 está agregando conexiones de bases de datos persistentes

Conexiones persistentes de la base de datos Django

Esto debería facilitar, en algunas condiciones, el uso de cursores. Todavía está fuera de mis habilidades actuales (y el tiempo para aprender) cómo implementar una solución de este tipo.

Además, la "mejor solución" definitivamente no funciona en todas las situaciones y no se puede usar como un enfoque genérico, solo se puede adaptar un stub caso por caso ...

Answer 1

Hay otra opción disponible. No haría la iteración más rápida (de hecho, probablemente la desaceleraría), pero haría que utilizara mucha menos memoria. Dependiendo de tus necesidades, esto puede ser apropiado.

large_qs = MyModel.objects.all().values_list("id", flat=True)
for model_id in large_qs:
    model_object = MyModel.objects.get(id=model_id)
    # do whatever you need to do with the model here

Solo los ID se cargan en la memoria, y los objetos se recuperan y descartan según sea necesario. Tenga en cuenta la mayor carga de la base de datos y el tiempo de ejecución más lento, ambas compensaciones para la reducción en el uso de la memoria.

Lo he usado cuando ejecuto tareas programadas asincrónicas en instancias de trabajo, para las cuales no importa si son lentas, pero si intentan utilizar demasiada memoria, pueden bloquear la instancia y, por lo tanto, abortar el proceso.

Answer 2

La respuesta esencial: use SQL sin procesar con cursores del lado del servidor .

Lamentablemente, hasta Django 1.5.2 no hay forma formal de crear un cursor MySQL del lado del servidor (no estoy seguro acerca de otros motores de base de datos). Así que escribí un código mágico para resolver este problema.

Para Django 1.5.2 y MySQLdb 1.2.4, el siguiente código funcionará. Además, está bien comentado.

Precaución: Esto no se basa en API públicas, por lo que probablemente se rompa en futuras versiones de Django.

# This script should be tested under a Django shell, e.g., ./manage.py shell

from types import MethodType

import MySQLdb.cursors
import MySQLdb.connections
from django.db import connection
from django.db.backends.util import CursorDebugWrapper


def close_sscursor(self):
    """An instance method which replace close() method of the old cursor.

    Closing the server-side cursor with the original close() method will be
    quite slow and memory-intensive if the large result set was not exhausted,
    because fetchall() will be called internally to get the remaining records.
    Notice that the close() method is also called when the cursor is garbage 
    collected.

    This method is more efficient on closing the cursor, but if the result set
    is not fully iterated, the next cursor created from the same connection
    won''t work properly. You can avoid this by either (1) close the connection 
    before creating a new cursor, (2) iterate the result set before closing 
    the server-side cursor.
    """
    if isinstance(self, CursorDebugWrapper):
        self.cursor.cursor.connection = None
    else:
        # This is for CursorWrapper object
        self.cursor.connection = None


def get_sscursor(connection, cursorclass=MySQLdb.cursors.SSCursor):
    """Get a server-side MySQL cursor."""
    if connection.settings_dict[''ENGINE''] != ''django.db.backends.mysql'':
        raise NotImplementedError(''Only MySQL engine is supported'')
    cursor = connection.cursor()
    if isinstance(cursor, CursorDebugWrapper):
        # Get the real MySQLdb.connections.Connection object
        conn = cursor.cursor.cursor.connection
        # Replace the internal client-side cursor with a sever-side cursor
        cursor.cursor.cursor = conn.cursor(cursorclass=cursorclass)
    else:
        # This is for CursorWrapper object
        conn = cursor.cursor.connection
        cursor.cursor = conn.cursor(cursorclass=cursorclass)
    # Replace the old close() method
    cursor.close = MethodType(close_sscursor, cursor)
    return cursor


# Get the server-side cursor
cursor = get_sscursor(connection)

# Run a query with a large result set. Notice that the memory consumption is low.
cursor.execute(''SELECT * FROM million_record_table'')

# Fetch a single row, fetchmany() rows or iterate it via "for row in cursor:"
cursor.fetchone()

# You can interrupt the iteration at any time. This calls the new close() method,
# so no warning is shown.
cursor.close()

# Connection must be close to let new cursors work properly. see comments of
# close_sscursor().
connection.close()

Answer 3

Si todo lo que quiere hacer es iterar sobre todo en la tabla una vez, lo siguiente es muy eficiente en recursos y mucho más rápido que el iterador básico. Tenga en cuenta que la paginación por clave primaria es necesaria para una implementación eficiente debido al tiempo lineal de la operación de compensación.

def table_iterator(model, page_size=10000):
    try: max = model.objects.all().order_by("-pk")[0].pk
    except IndexError: return 
    pages = int(max / page_size) + 1
    for page_num in range(pages):
        lower = page_num * page_size
        page = model.objects.filter(pk__gte=lower, pk__lt=lower+page_size)
        for obj in page:
            yield obj

El uso se ve así:

for obj in table_iterator(Model):
    # do stuff