Более быстрая вставка записей в таблицу с SQLAlchemy
Я анализирую журнал и вставляю его или в MySQL или в SQLite, использующий SQLAlchemy и Python. Прямо сейчас я открываю соединение с DB, и поскольку я циклично выполняюсь по каждой строке, я вставляю его после того, как это анализируется (Это - всего одна большая таблица прямо сейчас, не очень опытный с SQL). Я затем закрываю соединение, когда цикл сделан. Итоговый код:
log_table = schema.Table('log_table', metadata,
schema.Column('id', types.Integer, primary_key=True),
schema.Column('time', types.DateTime),
schema.Column('ip', types.String(length=15))
....
engine = create_engine(...)
metadata.bind = engine
connection = engine.connect()
....
for line in file_to_parse:
m = line_regex.match(line)
if m:
fields = m.groupdict()
pythonified = pythoninfy_log(fields) #Turn them into ints, datatimes, etc
if use_sql:
ins = log_table.insert(values=pythonified)
connection.execute(ins)
parsed += 1
Мои два вопроса:
- Существует ли способ ускорить вставки в этой основной платформе? Возможно, Очередь вставок и некоторых потоков вставки, имеет своего рода объем, вставляет, и т.д.?
- Когда я использовал MySQL приблизительно для ~1.2 миллионов записей, время вставки составляло 15 минут. С SQLite время вставки составляло немногим более, чем час. Та разница во времени между механизмами дб кажутся о праве, или это означает, что я делаю что-то очень неправильно?
3 ответа
Главное, что вы должны попробовать, - это разместить транзакцию вокруг нескольких вставок, поскольку фиксация базы данных на диск действительно занимает много времени. Вам нужно будет выбрать уровень пакетной обработки, но первая грубая попытка будет заключаться в том, чтобы обернуть транзакцию вокруг всего лота.
Я сделал следующее, чтобы добиться некоторой пакетной обработки:
inserts = []
insert_every = 1000
for line in file_to_parse:
m = line_regex.match(line)
if m:
fields = m.groupdict()
if use_sql: #This uses Globals, Ick :-/
inserts.append(pythonified)
if (parsed % insert_every) == 0:
connection.execute(log_table.insert(), inserts)
inserts = []
parsed += 1
if use_sql:
if len(inserts) > 0:
connection.execute(log_table.insert(), inserts)
Это не использует транзакции, но очень ленивым образом это позволило мне переключить этап вставки / синтаксического анализа с ~ 13 секунд на около ~ 2 секунд с серверной частью mysql, используя меньшую выборку. Я посмотрю, в чем разница между mysql и sqlite теперь с этим изменением, используя полный образец.
Я нашел основную информацию для этого здесь .
Результаты:
Механизм: Время несгруппированной вставки в минутах: Время сгруппированной вставки в минутах
Sqlite: 61: 8
MySql: 15: 2.5
Я не сбрасывал мой кеш между mysql и sqlite, который, возможно, имел бы исходный текстовый файл, но я не думаю, что это будет относительно существенной разницей.
Не зная движка таблиц (MyISAM? InnoDB?), Схемы и индексов, трудно комментировать особенности между двумя базами данных, которые вы там используете.
Однако при таком использовании MySQL вы, вероятно, обнаружите, что гораздо быстрее записать ваши данные во временный текстовый файл, а затем использовать синтаксис LOAD DATA INFILE , чтобы загрузить все это в свой база данных. Похоже, вы можете вызвать метод execute в своем объекте подключения , чтобы запустить SQL, необходимый для этого.
Кроме того, если вы твердо настроены добавлять элементы построчно и каждый раз заново создаете таблицу, вы можете проверить ключевые ограничения в своей программе и добавить эти ограничения только после того, как все строки были вставлены, сохраняя БД. время проверки ограничений для каждой вставки.