Mémoire efficace (constante) et itération optimisée en vitesse sur une grande table dans Django

Question

J'ai une très grande table. Il est actuellement dans une base de données MySQL. J'utilise django.

J'ai besoin d'itérer sur chaque élément de la table de pré-calculer certaines données particulier (peut-être que si j'étais mieux que je pouvais faire autrement, mais ce n'est pas le point).

J'aimerais garder l'itération aussi vite que possible avec une utilisation constante de la mémoire.

Comme il est déjà clairement en Limitant l'Utilisation de la Mémoire dans un *Grand* Django QuerySet et Pourquoi est-à itérer un grand Django QuerySet consommer d'énormes quantités de mémoire?, une simple itération sur tous les objets django va tuer la machine car il permettra de récupérer TOUS les objets de la base de données.

Vers une solution

Tout d'abord, afin de réduire votre consommation de mémoire, vous devriez être sûr de DÉBOGAGE est Faux (ou le singe patch le curseur: désactiver la journalisation SQL tout en gardant les paramètres.DEBUG?) pour être sûr de django n'est pas de stocker des trucs en connections pour le débogage.

Mais même avec ça,

for model in Model.objects.all()

est un no go.

Pas de même avec la légère amélioration de la forme:

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

À l'aide de iterator() vous fera économiser de la mémoire en ne stockant pas le résultat de la mémoire cache interne (mais pas nécessairement sur PostgreSQL!); mais toujours récupérer l'ensemble des objets de la base de données, apparemment.

Une solution naïve

La solution à la première question est de couper les résultats basés sur un compteur par un chunk_size. Il y a plusieurs façons de l'écrire, mais, fondamentalement, ils viennent tous à un OFFSET + LIMIT de requêtes en SQL.

quelque chose comme:

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

Alors que c'est efficace en terme de mémoire (constante de l'utilisation de la mémoire proportionnelle à l' chunk_size), c'est vraiment pauvre en terme de vitesse: OFFSET grandit, MySQL et PostgreSQL (et probablement la plupart des DBs) va commencer d'étouffement et de ralentir.

Une meilleure solution

Une meilleure solution est disponible dans ce post par Thierry Schellenbach. Il filtre sur le PK, qui est beaucoup plus rapide que la compensation (à quelle vitesse dépend probablement sur le 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()

Cela commence à être satisfaisante. Maintenant la Mémoire = O(C), et la Vitesse ~= O(N)

Des problèmes avec la "meilleure" solution

La meilleure solution ne fonctionne que lorsque le PK est disponible dans le QuerySet. Malheureusement, ce n'est pas toujours le cas, en particulier lorsque le QuerySet contient des combinaisons distinctes (group_by) et/ou de valeurs (ValueQuerySet).

Pour cette situation, la "meilleure solution" ne peut pas être utilisé.

Pouvons-nous faire mieux?

Maintenant je me demande si on peut aller plus vite et d'éviter le problème de la QuerySets sans PK. Peut-être à l'aide de quelque chose que j'ai trouvé dans d'autres réponses, mais seulement en pur SQL: à l'aide de curseurs.

Depuis que je suis très mauvais avec SQL brut, en particulier dans Django, voici la vraie question:

comment pouvons-nous construire un meilleur Django QuerySet Itérateur pour les grandes tables

De mon point de vue de ce que j'ai lu, c'est que nous devrions utiliser les curseurs côté serveur (apparemment (voir les références) à l'aide d'un standard de Django Curseur ne permettrait pas d'atteindre le même résultat, car, par défaut, python-MySQL et psycopg connecteurs de mettre en cache les résultats).

Serait-ce vraiment un plus rapide (et/ou plus efficace) solution?

Cela peut être fait à l'aide de matières SQL dans django? Ou devrions-nous écrire spécifiques du code python en fonction du connecteur de base de données?

Des curseurs Côté serveur dans PostgreSQL et MySQL

C'est tout ce que j'ai pu obtenir pour le moment...

Django chunked_iterator()

Maintenant, bien sûr, le mieux serait d'avoir cette méthode de travail en tant que queryset.iterator(), plutôt que d' iterate(queryset), et de faire partie de django de base ou au moins une enfichable de l'application.

Mise à jour Grâce à "T" dans les commentaires pour trouver un django billet qui transportent des informations supplémentaires. Les différences dans le connecteur comportements de faire en sorte que, probablement, la meilleure solution serait de créer l' chunked méthode plutôt que d'étendre de façon transparente iterator (sonne comme une bonne approche pour moi). Une mise en œuvre stub existe, mais il n'y a pas eu de travail dans une année, et il ne ressemble pas à l'auteur est prêt à sauter sur celui-là.

Supplémentaires Refs:

1. Pourquoi MYSQL LIMITE supérieure de décalage ralentir la requête vers le bas?
2. Comment puis-je accélérer une requête MySQL avec un grand décalage dans la clause LIMIT?
3. http://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/
4. postgresql: offset + limite devient très lent
5. L'amélioration de DÉCALAGE de performances dans PostgreSQL
6. http://www.depesz.com/2011/05/20/pagination-with-fixed-order/
7. Comment obtenir une ligne-par-ligne MySQL ResultSet en python Curseur Côté Serveur MySQL

Modifications:

Django 1.6 est l'ajout de connexions persistantes aux bases de données

Django Base De Données Les Connexions Persistantes

Cela devrait faciliter, sous certaines conditions, l'utilisation de curseurs. Néanmoins, il est hors de mes compétences actuelles (et de temps pour apprendre) comment mettre en œuvre une telle solution..

Aussi, la "meilleure solution" certainement ne fonctionne pas dans toutes les situations et ne peut pas être utilisé comme une approche générique, à seulement un tampon pour être adapté au cas par cas...

Answer 1

L'essentiel réponse: utiliser SQL brut avec les curseurs côté serveur.

Malheureusement, jusqu'à ce que Django 1.5.2 il n'existe pas de manière formelle pour créer un serveur MySQL curseur (pas sûr sur les autres moteurs de base de données). J'ai donc écrit un peu de magie de code pour résoudre ce problème.

Pour Django 1.5.2 et MySQLdb 1.2.4, le code suivant fonctionne. Aussi, il est bien commenté.

Attention: Ce n'est pas basée sur des Api publiques, de sorte qu'il sera probablement pause dans les futures versions 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 2

Si tout ce que vous voulez faire est d'itérer une fois sur tout dans le tableau, ce qui suit est très efficace en ressources et bien plus rapide que l'itérateur de base. Notez que la pagination par clé primaire est nécessaire pour une implémentation efficace en raison du temps linéaire de l'opération de décalage.

 def table_iterator(model, page_size=10000):
    try: max = model.objects.all().order_by("-pk")[0].pk
    except IndexError: return 
    pages = 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
 

L'utilisation ressemble à:

 for obj in table_iterator(Model):
    # do stuff
 

Answer 3

Il est une autre option disponible. Ça ne ferait pas de l'itération plus rapide, (en fait, il serait probablement ralentir), mais cela permettrait de l'utiliser beaucoup moins de mémoire. Selon vos besoins, cela peut être approprié.

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

Seulement les id sont chargés en mémoire, et les objets sont récupérés et jetés en tant que de besoin. Remarque l'augmentation de la charge de base de données et le ralentissement de l'exécution, à la fois des compromis pour la réduction de l'utilisation de la mémoire.

J'ai utilisé lors de l'exécution asynchrone des tâches planifiées sur les travailleurs des cas, pour lesquels il n'a pas vraiment d'importance si elles sont lents, mais s'ils essaient d'utiliser trop de mémoire qu'ils peuvent crash de l'instance et, par conséquent, abandonner le processus.