wydajny w pamięci wbudowany iterator / generator SqlAlchemy?

Mam ~ 10M rekordową tabelę MySQL, z którą współpracuję przy użyciu SqlAlchemy. Zauważyłem, że zapytania dotyczące dużych podzbiorów tej tabeli zajmują zbyt dużo pamięci, mimo że myślałem, że używam wbudowanego generatora, który inteligentnie pobierał fragmenty zbioru danych o wielkości ułamka:

for thing in session.query(Things):
    analyze(thing)

Aby tego uniknąć, muszę zbudować własny iterator, który gryzie na kawałki:

lastThingID = None
while True:
    things = query.filter(Thing.id < lastThingID).limit(querySize).all()
    if not rows or len(rows) == 0: 
        break
    for thing in things:
        lastThingID = row.id
        analyze(thing)

Czy to normalne, czy jest coś, czego mi brakuje w odniesieniu do wbudowanych generatorów SA?

Odpowiedź na to pytanie wydaje się wskazywać, że nie należy się spodziewać zużycia pamięci.

Większość implementacji DBAPI w pełni buforuje wiersze podczas ich pobierania - tak więc zwykle, zanim SQLAlchemy ORM nawet zatrzyma jeden wynik, cały zestaw wyników jest w pamięci.

Ale sposób Querydziała tak, że w pełni ładuje podany zestaw wyników domyślnie przed zwróceniem do ciebie twoich obiektów. Uzasadnienie tutaj dotyczy zapytań, które są czymś więcej niż prostymi instrukcjami SELECT. Na przykład, w połączeniach z innymi tabelami, które mogą zwracać tę samą tożsamość obiektu wiele razy w jednym zestawie wyników (często w przypadku zachłannego ładowania), pełny zestaw wierszy musi znajdować się w pamięci, aby można było zwrócić poprawne wyniki, w przeciwnym razie kolekcje itp. może być tylko częściowo zaludniony.

QueryOferuje więc opcję zmiany tego zachowania za pomocą yield_per(). To wywołanie spowoduje, że będzie Querygenerować wiersze w partiach, jeśli podasz rozmiar partii. Jak stwierdza dokumentacja, jest to właściwe tylko wtedy, gdy nie wykonujesz żadnego gorliwego ładowania kolekcji, więc w zasadzie jest to, jeśli naprawdę wiesz, co robisz. Ponadto, jeśli bazowy DBAPI wstępnie buforuje wiersze, nadal będzie ten narzut pamięci, więc podejście skaluje się tylko nieco lepiej niż nieużywanie go.

Rzadko kiedy używam yield_per(); zamiast tego używam lepszej wersji podejścia LIMIT, które sugerujesz powyżej, używając funkcji okna. LIMIT i OFFSET mają ogromny problem, ponieważ bardzo duże wartości OFFSET powodują, że zapytanie staje się wolniejsze i wolniejsze, ponieważ OFFSET o N powoduje, że przegląda N wierszy - to tak, jakby wykonać to samo zapytanie pięćdziesiąt razy zamiast jednego, za każdym razem, gdy czytasz coraz większa liczba rzędów. Stosując podejście funkcji okna, wstępnie pobieram zestaw wartości „okna”, które odnoszą się do fragmentów tabeli, które chcę zaznaczyć. Następnie emituję indywidualne instrukcje SELECT, które są pobierane z jednego z tych okien na raz.

Podejście do funkcji okna jest na wiki i używam go z wielkim sukcesem.

Uwaga: nie wszystkie bazy danych obsługują funkcje okien; potrzebujesz Postgresql, Oracle lub SQL Server. IMHO używając przynajmniej Postgresql jest zdecydowanie tego warte - jeśli korzystasz z relacyjnej bazy danych, równie dobrze możesz użyć najlepszych.

Nie jestem ekspertem od baz danych, ale kiedy używam SQLAlchemy jako prostej warstwy abstrakcji Pythona (tj. Nie używam obiektu ORM Query), wymyśliłem satysfakcjonujące rozwiązanie, aby zapytać o 300-milionową tabelę bez eksplodującego użycia pamięci ...

Oto przykład fikcyjny:

from sqlalchemy import create_engine, select

conn = create_engine("DB URL...").connect()
q = select([huge_table])

proxy = conn.execution_options(stream_results=True).execute(q)

Następnie używam fetchmany()metody SQLAlchemy do iteracji po wynikach w nieskończonej whilepętli:

while 'batch not empty':  # equivalent of 'while True', but clearer
    batch = proxy.fetchmany(100000)  # 100,000 rows at a time

    if not batch:
        break

    for row in batch:
        # Do your stuff here...

proxy.close()

Ta metoda pozwoliła mi na wykonanie wszelkiego rodzaju agregacji danych bez niebezpiecznego narzutu pamięci.

NOTE stream_resultswspółpracuje z PostgreSQL i pyscopg2adapterem, ale myślę, że nie będzie współpracować z dowolnym DBAPI, ani z jakiegokolwiek sterownika bazy danych ...

W tym poście na blogu znajduje się ciekawy przypadek użycia, który zainspirował moją powyższą metodę.

Szukałem wydajnego przechodzenia / stronicowania za pomocą SQLAlchemy i chciałbym zaktualizować tę odpowiedź.

Myślę, że możesz użyć wywołania wycinka, aby odpowiednio ograniczyć zakres zapytania i efektywnie użyć go ponownie.

Przykład:

window_size = 10  # or whatever limit you like
window_idx = 0
while True:
    start,stop = window_size*window_idx, window_size*(window_idx+1)
    things = query.slice(start, stop).all()
    if things is None:
        break
    for thing in things:
        analyze(thing)
    if len(things) < window_size:
        break
    window_idx += 1

W duchu odpowiedzi Joela używam następującego:

WINDOW_SIZE = 1000
def qgen(query):
    start = 0
    while True:
        stop = start + WINDOW_SIZE
        things = query.slice(start, stop).all()
        if len(things) == 0:
            break
        for thing in things:
            yield thing
        start += WINDOW_SIZE

Używanie LIMITU / PRZESUNIĘCIA jest złe, ponieważ musisz znaleźć wszystkie kolumny {OFFSET} wcześniej, więc im większe jest PRZESUNIĘCIE - tym dłuższe żądanie otrzymasz. Użycie zapytania okienkowego daje również złe wyniki na dużej tabeli z dużą ilością danych (zbyt długo czekasz na pierwsze wyniki, że w moim przypadku nie jest to dobre dla fragmentarycznej odpowiedzi sieciowej).

Najlepsze podejście podane tutaj https://stackoverflow.com/a/27169302/450103 . W moim przypadku rozwiązałem problem po prostu używając indeksu na polu datetime i pobierając następne zapytanie z datetime> = previous_datetime. Głupie, ponieważ użyłem tego indeksu w różnych przypadkach wcześniej, ale pomyślałem, że do pobierania wszystkich danych zapytanie okienkowe byłoby lepsze. W moim przypadku się myliłem.

AFAIK, pierwszy wariant nadal pobiera wszystkie krotki z tabeli (z jednym zapytaniem SQL), ale podczas iteracji buduje prezentację ORM dla każdej jednostki. Jest więc bardziej wydajne niż tworzenie listy wszystkich jednostek przed iteracją, ale nadal musisz pobrać wszystkie (surowe) dane do pamięci.

Dlatego używanie LIMIT-a na dużych stołach wydaje mi się dobrym pomysłem.