在 django 中使用原始 sql 进行初始插入后 MySQL LOAD DATA INFILE 速度变慢
我正在使用以下自定义处理程序在 django 中使用原始 sql 以及带有 innodb 表的 MySQLdb 后端进行批量插入：
def handle_ttam_file_for(f, subject_pi):
import datetime
write_start = datetime.datetime.now()
print "write to disk start: ", write_start
destination = open('temp.ttam', 'wb+')
for chunk in f.chunks():
destination.write(chunk)
destination.close()
print "write to disk end", (datetime.datetime.now() - write_start)
subject = Subject.objects.get(id=subject_pi)
def my_custom_sql():
from django.db import connection, transaction
cursor = connection.cursor()
statement = "DELETE FROM ttam_genotypeentry WHERE subject_id=%i;" % subject.pk
del_start = datetime.datetime.now()
print "delete start: ", del_start
cursor.execute(statement)
print "delete end", (datetime.datetime.now() - del_start)
statement = "LOAD DATA LOCAL INFILE 'temp.ttam' INTO TABLE ttam_genotypeentry IGNORE 15 LINES (snp_id, @dummy1, @dummy2, genotype) SET subject_id=%i;" % subject.pk
ins_start = datetime.datetime.now()
print "insert start: ", ins_start
cursor.execute(statement)
print "insert end", (datetime.datetime.now() - ins_start)
transaction.commit_unless_managed()
my_custom_sql()
上传的文件有 500k 行，大小约为 15M。
随着文件的添加，加载时间似乎逐渐变长。
Insert times:
1st: 30m
2nd: 50m
3rd: 1h20m
4th: 1h30m
5th: 1h35m
我想知道随着添加恒定大小（# 行）的文件，加载时间变长是否正常，以及是否有任何方法可以提高批量插入的性能。
I'm using the following custom handler for doing bulk insert using raw sql in django with a MySQLdb backend with innodb tables:
def handle_ttam_file_for(f, subject_pi):
import datetime
write_start = datetime.datetime.now()
print "write to disk start: ", write_start
destination = open('temp.ttam', 'wb+')
for chunk in f.chunks():
destination.write(chunk)
destination.close()
print "write to disk end", (datetime.datetime.now() - write_start)
subject = Subject.objects.get(id=subject_pi)
def my_custom_sql():
from django.db import connection, transaction
cursor = connection.cursor()
statement = "DELETE FROM ttam_genotypeentry WHERE subject_id=%i;" % subject.pk
del_start = datetime.datetime.now()
print "delete start: ", del_start
cursor.execute(statement)
print "delete end", (datetime.datetime.now() - del_start)
statement = "LOAD DATA LOCAL INFILE 'temp.ttam' INTO TABLE ttam_genotypeentry IGNORE 15 LINES (snp_id, @dummy1, @dummy2, genotype) SET subject_id=%i;" % subject.pk
ins_start = datetime.datetime.now()
print "insert start: ", ins_start
cursor.execute(statement)
print "insert end", (datetime.datetime.now() - ins_start)
transaction.commit_unless_managed()
my_custom_sql()
The uploaded file has 500k rows and is ~ 15M in size.
The load times seem to get progressively longer as files are added.
Insert times:
1st: 30m
2nd: 50m
3rd: 1h20m
4th: 1h30m
5th: 1h35m
I was wondering if it is normal for load times to get longer as files of constant size (# rows) are added and if there is anyway to improve performance of bulk inserts.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我发现批量插入到 innodb 表的主要问题是我忽略了 mysql innodb 设置。
对于我的 mysql 版本，
innodb_buffer_pool_size的设置默认为 8M，随着表大小的增长，速度会大幅减慢。innodb-performance-optimization-basicschoosing-innodb_buffer_pool_size如果使用专用 mysql 服务器，根据文章建议的大小是内存的 70% 到 80%。增加缓冲池大小后，我的插入时间从一个多小时缩短到不到 10 分钟，没有其他任何变化。
我能够做出的另一个更改是在 LOAD DATA 语句中使用 LOCAL 参数（感谢 @f00）。我之前的问题是，当我尝试让 mysql 访问 django 上传的文件时，我不断收到文件未找到或无法获取统计错误的信息。
事实证明，这与使用 ubuntu 以及这个 bug。
I found the main issue with bulk inserting to my innodb table was a mysql innodb setting I had overlooked.
The setting for
innodb_buffer_pool_sizeis default 8M for my version of mysql and causing a huge slow down as my table size grew.innodb-performance-optimization-basicschoosing-innodb_buffer_pool_sizeThe recommended size according to the articles is 70 to 80 percent of the memory if using a dedicated mysql server. After increasing the buffer pool size, my inserts went from an hour+ to less than 10 minutes with no other changes.
Another change I was able to make was getting ride of the LOCAL argument in the LOAD DATA statement (thanks @f00). My problem before was that i kept getting file not found, or cannot get stat errors when trying to have mysql access the file django uploaded.
Turns out this is related to using ubuntu and this bug.