📘

大量のデータを取得する際に気を付けること

2020/12/11に公開

Python

初心者

tech

この投稿は Django Advent Calendar 2020 - Qiita と
PyLadies Japan Advent Calendar 2020 の11日目の記事です。

さて、以下のようなコードを実行して、サーバーがフリーズしたことはありませんか？

for instance in SomeModel.objects.all():
     # do something

私はフリーズしました。焦りました。

all()や、filter()で取得するデータ件数が多いと、結果セット全体をメモリにロードするため、サーバーのspecによってはメモリ不足によりフリーズしてしまいます。
そんな時はさくっとサーバーのspecをあげるのも有効な手段です。（実際、私はそれで解決しました；）

ただ、そんなさくっとあげれないよ！という場合もあると思いますので、そんな時がきた時のために調べてみました。

Djangoのドキュメントをみると、iterator()を使うとメモリ消費が減りそうな感じにみえます。

for instance in SomeModel.objects.iterator():
     # do something

が、接続しているデータベースよって違いがありました。

OracleとPostgreSQLの場合、サーバーサイドのカーソルを使用して、結果セット全体をメモリにロードせずに、データベースから結果をストリーミングするため、iterator()をつかえば解決しそうです。

*PostgreSQLでは、サーバー側カーソルはDISABLE_SERVER_SIDE_CURSORS設定がFalseの場合にのみ使用されるとあります。

しかし、MySQLの場合は、ストリーミング結果をサポートしていないため、Pythonデータベースドライバは結果セット全てをメモリにロードします と書いてあります。そのため、たとえiterator()を使用したとしても、メモリの消費量は all() とほぼ同じになるようです。

ではMySQLの場合一体どうすれば！

MySQLの場合の対策としては、2000件ずつなど適切なチャンクに区切って取得するよう自分で実装する必要があります。
実装の具体的なコードは以下が参考になりそうです。（動作未確認です...)

また、単純に全件のある特定のカラムだけを参照したいケースであればQuerySet の values(), values_list()を使えば、必要な値だけを取得できるのでメモリ消費量という点では有効かもしれません。

ちなみに、上記解決法の一例として、載っていたpagination。
この記事は第10回モグモグDjango Advent Calendar 執筆会で書いていたのですが、その時にpaginationの処理が遅いという意見を聞いたので、ちょっと本題とはずれますが、あわせて調べてみました。

paginationの時実際に発行されているSQLをみてみると、limit & offsetが使用されています。

>>> from django.db import connection

>>> paginator = Paginator(SomeModel.objects.all(), 1000) # chunks of 1000
>>> for page_idx in range(1, paginator.num_pages):
>>>     for row in paginator.page(page_idx).object_list:
>>>         # do something
>>> for history in connection.queries:
...     print(history)
... 
{'sql': 'SELECT 'some_model'.xx, ....  FROM `some_model` LIMIT 1000', 'time': '0.036'}}
{'sql': 'SELECT 'some_model'.xx, ....  FROM `some_model` LIMIT 1000',  OFFSET 2000', '0.042'}
{'sql': 'SELECT 'some_model'.xx, ....  FROM `some_model` LIMIT 1000',  OFFSET 3000', '0.053'}
	；

MySQLのlimitを使用した場合、オフセット部分が増えるとパフォーマンスが低下するとのことです。
実際に30万件のデータで計測したところ、確かにoffset値が増えると取得までの時間がふえています。(mysql:5.7で確認）

SELECT * FROM some_model LIMIT 1000 OFFSET 20000 ...  75.77ms
SELECT * FROM some_model LIMIT 1000 OFFSET 40000 ...  120.00ms
SELECT * FROM some_model LIMIT 1000 OFFSET 200000 ... 392.12ms
SELECT * FROM some_model LIMIT 1000 OFFSET 300000 ... 564.77ms

ということで、最後話がそれましたが、うっかりデータ量の多いテーブルの全件を取得すると、心臓に悪いことが起こる可能性があるので気をつけてください！！というエントリでした。

解決方法としては、サーバーのスペックで解決！から、どのDBと接続しているか、取得したい情報、取得してからどんな処理をするか、などなど様々な要因が関わってくると思いますので、都度最適な方法を模索してみてください。

参考

MySQLのlimitで大量データのパフォーマンスが遅い重い！

参考

Discussion