Тюнинг скорости sqlite при работе через SQLAlchemy

2013-07-30 02:33

Некоторые наверняка сталкивались с крайне низкой скоростью инсертов в sqlite с дефолтными настройками, хотя везде пишут, что БД должна работать очень быстро, тем не менее если использовать SQLAlchemy с драйвером pysqlite "из коробки", при большом кол-ве элементов и внушительном объеме данных на каждом инсерте, скорость становится просто невыносимой. Есть возможность тюнить различные параметры через директиву PRAGMA, но типичное применение sqlite (у меня по крайней мере) - это временное хранилище данных, что подразумевает переодическое удаление, после чего при запуске скрипта SQLAlchemy создает новую БД уже без всякого тюнинга. В конце концов я разрбрался и нашел элегантное решение.

Я использую (последнее время редко) SQLAlchemy с микрофрэймворком Flask (почти всегда, но уже с MongoDB), поэтому приведу пример config.py файла "живого" проекта, в данном случае это парсер данных с твиттера.

import os
import sys
import logging
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine

from models import Base

# add project setting.py file folder to path
PROJECT_PATH = os.path.dirname(os.path.realpath(__file__))
if not PROJECT_PATH in sys.path:
    sys.path.insert(0, PROJECT_PATH)


DEBUG = False
LOG_LEVEL = logging.INFO


# To get this go to https://dev.twitter.com/apps
# full consumer key and secret by
# Consumer key  and Consumer secret values
# then go to OAuth Tool tab and get
# Consumer key and Consumer secret
# or using "Your access token""
API_CONSUMER_KEY = ''
API_CONSUMER_SECRET = ''
ACCESS_TOKEN = ''
ACCESS_SECRET = ''


def _engine():
    sqlite_path = os.path.join(PROJECT_PATH, 'db/twitter.sqlite')
    connection_string = 'sqlite+pysqlite:///%s' % sqlite_path
    return create_engine(connection_string, encoding='utf-8')


def init_engine():
    db_engine = _engine()
    Base.metadata.create_all(db_engine)
    return db_engine


def reset_db():
    db_engine = _engine()
    Base.metadata.drop_all(db_engine)


from sqlalchemy.engine import Engine
from sqlalchemy import event

# далее происходит привязка функции к событию (коннект к БД)
# с использование декоратора @event
# и затем для БД устанавливаются нужные настройки
@event.listens_for(Engine, "connect")
def set_sqlite_pragma(dbapi_connection, connection_record):
    cursor = dbapi_connection.cursor()
    cursor.execute("PRAGMA foreign_keys = ON")
    cursor.execute("PRAGMA synchronous = OFF")
    cursor.execute("PRAGMA journal_mode = MEMORY")
    cursor.close()


# create database connection and session
db_engine = init_engine()
Session = sessionmaker(bind=db_engine)

В данном случае включается поддержка foreign_keys, отключается проверка записи данных (synchronous) т.е. драйвер slqite передает задачу операционной системе и не проверяет правильность записи, тем самым экономя кучу времени, но в случае сбоя питания база скорее всего будет повреждена, а данные потеряны (подробнее по ссылке http://www.sqlite.org/pragma.html#pragma_synchronous ) и journal_mode - журнал переносится в память. Т.е. фактически мы жертвуем целостностью данных (что нужно не всегда) и получаем выйграш в скорости.

Само собой существует множество вариантов более тонкой настройки, можно поиграться с такими параметрами как locking_mode, cache_size, page_size и т.д.

Полный список директив PRAGMA можно найти в документации http://www.sqlite.org/pragma.html так же есть серия статей на хабре http://habrahabr.ru/post/149356/

На данный момент крайне редко использую sqlite, а если вдруг понадобится какое-то решение embedded database то скорее всего буду смотреть в сторону http://labs.codernity.com/codernitydb/

Social Profiles

My Projects

Тюнинг скорости sqlite при работе через SQLAlchemy