时间戳

首页 > 解决方案 > 通过sqlalchemy重复插入sqlite数据库导致内存泄漏？

python - 通过sqlalchemy重复插入sqlite数据库导致内存泄漏？

问题描述

当通过 sqlalchemy 和 pandasto_sql以及指定的夹头将一个巨大的 pandas 数据框插入到 sqlite 时，我会遇到内存错误。

起初我认为这是一个问题，to_sql但我尝试了一种解决方法，而不是使用我使用的 chunksizefor i in range(100): df.iloc[i * 100000:(i+1):100000].to_sql(...)并且仍然导致错误。

在某些情况下，似乎存在内存泄漏，通过 sqlalchemy 重复插入到 sqlite。

通过一个最小的示例，我很难尝试复制转换数据时发生的内存泄漏。但这非常接近。

import string
import numpy as np
import pandas as pd
from random import randint
import random

def make_random_str_array(size=10, num_rows=100, chars=string.ascii_uppercase + string.digits):
    return (np.random.choice(list(chars), num_rows*size)
            .view('|U{}'.format(size)))

def alt(size, num_rows):
    data = make_random_str_array(size, num_rows=2*num_rows).reshape(-1, 2)
    dfAll = pd.DataFrame(data)
    return dfAll

dfAll = alt(randint(1000, 2000), 10000)

for i in range(330):
    print('step ', i)
    data = alt(randint(1000, 2000), 10000)
    df = pd.DataFrame(data)
    dfAll = pd.concat([ df,  dfAll ])

import sqlalchemy

from sqlalchemy import create_engine
engine = sqlalchemy.create_engine('sqlite:///testtt.db')

for i in range(500):
    print('step', i)
    dfAll.iloc[(i%330)*10000:((i%330)+1)*10000].to_sql('test_table22', engine, index = False, if_exists= 'append')

这是在 Google Colab CPU 环境中运行的。

数据库本身不会导致内存泄漏，因为我可以重新启动我的环境，并且之前插入的数据仍然存在，并且连接到该数据库不会导致内存增加。问题似乎是在某些条件下通过循环重复插入to_sql或to_sql指定了chucksize。

有没有一种方法可以运行此代码而不会最终增加内存使用量？

编辑：

要完全重现错误，请运行此笔记本

https://drive.google.com/open?id=1ZijvI1jU66xOHkcmERO4wMwe-9HpT5OS

笔记本要求您将此文件夹导入 Google Drive 的主目录

https://drive.google.com/open?id=1m6JfoIEIcX74CFSIQArZmSd0A8d0IRG8

笔记本还会挂载您的 Google 驱动器，您需要授权它访问您的 Google 驱动器。由于数据托管在我的 Google 驱动器上，因此导入数据不应占用您分配的任何数据。

标签： pythonpandassqlitememory-leakssqlalchemy

解决方案

Google Colab 实例以大约 12.72GB 的可用 RAM 开始。创建 DataFrame 后theBigList，大约使用了 9.99GB 的 RAM。这已经是一个相当不舒服的情况，因为 Pandas 操作需要与它正在操作的 DataFrame 一样多的额外空间并不罕见。所以我们应该尽可能避免使用这么多的内存，幸运的是有一种简单的方法可以做到这一点：只需加载每个.npy文件并将其数据一次存储在 sqlite 数据库中，而无需创建theBigList（见下文）。

但是，如果我们使用您发布的代码，我们可以看到 RAM 使用量随着块的theBigList迭代存储在数据库中而缓慢增加。

theBigListDataFrame 将字符串存储在 NumPy 数组中。但是在将字符串传输到 sqlite 数据库的过程中，NumPy 字符串被转换为 Python 字符串。这需要额外的内存。

根据这个讨论 Python 内部内存管理的Theano 教程，

为了加快内存分配（和重用），Python 使用了许多用于小对象的列表。每个列表将包含类似大小的对象：将有一个大小为 1 到 8 个字节的对象列表，一个用于 9 到 16 个字节的对象，等等。当需要创建一个小对象时，要么我们重用列表中的空闲块，或者我们分配一个新的。

...重要的一点是这些列表永远不会缩小。

确实：如果一个项目（大小为 x）被释放（由于缺少引用而被释放），它的位置不会返回到 Python 的全局内存池（甚至更少返回到系统），而只是标记为空闲并添加到大小为 x 的物品。如果需要另一个大小兼容的对象，则将重用死对象的位置。如果没有可用的死对象，则会创建新对象。

如果小对象内存从未被释放，那么不可避免的结论是，就像金鱼一样，这些小对象列表只会不断增长，不会缩小，并且您的应用程序的内存占用由在任何给定情况下分配的最大数量的小对象所支配观点。

我相信这准确地描述了您在此循环执行时看到的行为：

for i in range(0, 588):
    theBigList.iloc[i*10000:(i+1)*10000].to_sql(
        'CS_table', engine, index=False, if_exists='append')

尽管许多死对象的位置被重新用于新字符串，但对于本质上随机的字符串（例如theBigList偶尔需要额外空间中的字符串）并因此内存占用不断增长，这并非令人难以置信。

该进程最终达到了 Google Colab 的 12.72GB RAM 限制，并且内核因内存错误而被终止。

在这种情况下，避免大量内存使用的最简单方法是永远不要实例化整个 DataFrame - 相反，一次只加载和处理 DataFrame 的小块：

import numpy as np
import pandas as pd
import matplotlib.cbook as mc
import sqlalchemy as SA

def load_and_store(dbpath):
    engine = SA.create_engine("sqlite:///{}".format(dbpath))    
    for i in range(0, 47):
        print('step {}: {}'.format(i, mc.report_memory()))                
        for letter in list('ABCDEF'):
            path = '/content/gdrive/My Drive/SummarizationTempData/CS2Part{}{:02}.npy'.format(letter, i)
            comb = np.load(path, allow_pickle=True)
            toPD = pd.DataFrame(comb).drop([0, 2, 3], 1).astype(str)
            toPD.columns = ['title', 'abstract']
            toPD = toPD.loc[toPD['abstract'] != '']
            toPD.to_sql('CS_table', engine, index=False, if_exists='append')

dbpath = '/content/gdrive/My Drive/dbfile/CSSummaries.db'
load_and_store(dbpath)

哪个打印

step 0: 132545
step 1: 176983
step 2: 178967
step 3: 181527
...         
step 43: 190551
step 44: 190423
step 45: 190103
step 46: 190551

每行的最后一个数字是matplotlib.cbook.report_memory报告的进程消耗的内存量 。有许多不同的内存使用度量。在 Linux 上，mc.report_memory()是报告 进程的核心映像的物理页面的大小（包括文本、数据和堆栈空间）。

顺便说一句，您可以使用管理内存的另一个基本技巧是使用函数。当函数终止时，函数内部的局部变量会被释放。这减轻了您手动调用del和的负担gc.collect()。

推荐阅读