SQLite synchronous on engine

[pr64]

Hi,

In order to improve the underlying SQLite performance, I've changed some low level settings with PRAGMA commands:

PRAGMA synchronous=NORMAL; /* instead of default FULL value, see: http://www.sqlite.org/pragma.html#pragma_synchronous */

PRAGMA journal_mode=WAL;       /* http://www.sqlite.org/pragma.html#pragma_journal_mode and http://www.sqlite.org/wal.html */

From an implementation point of view, I did as explained in this thread: https://groups.google.com/forum/?fromgroups#!topic/sqlalchemy/IY5PlUf4VwE. I've got an OrmManager class (which is a singleton) which is used to get new sessions. The bold lines are the ones I added to improve performance.

class OrmManager:

    """

    OrmManager class

    Handles the database and provides an abstraction layer for it

    """

    def  __init__(self, database, metadata, db_type, echo=False):

        self.database = database

        self.session_maker = sessionmaker()

        if db_type == 'file':

            engine = create_engine('sqlite:///' + database, echo=echo,

                    connect_args={'detect_types': sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES},

                    native_datetime=True,

                    poolclass=NullPool

                    )

        elif db_type == 'memory':

            engine = create_engine('sqlite:///' + database, echo=echo,

                    connect_args={'detect_types': sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES},

                    native_datetime=True,

                    poolclass=SingletonThreadPool,

                    pool_size=5

                    )

        else:

            raise Exception("Unknown db_type: %s" % str(db_type))

        metadata.create_all(engine)

        self.session_maker.configure(bind=engine, expire_on_commit=False)

        session = self.session_maker()

        session.connection().execute("PRAGMA journal_mode=WAL")

        session.commit()

        session.close()

    def get_session(self):

        """Gets ORM session"""

        session = self.session_maker()

        session.connection().execute("PRAGMA synchronous=NORMAL")

        return session

I have two questions:

1- journal_mode pragma is persistent (according to sqlite doc) and should be done once but is there a way to pass the synchronous configuration to the  engine and make it global instead of setting it every time my application gets a new session ?

2- Are there any performance settings I can tune at sqlalchemy and/or sqlite level to improve my db access speed ?

Thanks a lot for your feedback,

Pierre

[Michael Bayer]

On Jun 19, 2013, at 10:14 AM, pr64 <pierre...@gmail.com> wrote:

Hi,

In order to improve the underlying SQLite performance, I've changed some low level settings with PRAGMA commands:

PRAGMA synchronous=NORMAL; /* instead of default FULL value, see: http://www.sqlite.org/pragma.html#pragma_synchronous */

PRAGMA journal_mode=WAL;       /* http://www.sqlite.org/pragma.html#pragma_journal_mode and http://www.sqlite.org/wal.html */

From an implementation point of view, I did as explained in this thread: https://groups.google.com/forum/?fromgroups#!topic/sqlalchemy/IY5PlUf4VwE. I've got an OrmManager class (which is a singleton) which is used to get new sessions. The bold lines are the ones I added to improve performance.

        session = self.session_maker()

        session.connection().execute("PRAGMA journal_mode=WAL")

        session.commit()

        session.close()

    def get_session(self):

        """Gets ORM session"""

        session = self.session_maker()

        session.connection().execute("PRAGMA synchronous=NORMAL")

        return session

I have two questions:

1- journal_mode pragma is persistent (according to sqlite doc) and should be done once but is there a way to pass the synchronous configuration to the  engine and make it global instead of setting it every time my application gets a new session ?

you want to use a connect event for that:

from sqlalchemy import event

@event.listens_for(my_engine, "connect")

def on_connect(dbapi_conn, conn_rec, conn_proxy):

    cursor = dbapi_conn.cursor()

    cursor.execute("your pragma here")

    cursor.close()

2- Are there any performance settings I can tune at sqlalchemy and/or sqlite level to improve my db access speed ?

I'm not familiar with the nature of these performance settings, but the sqlite3 DBAPI and SQLite itself is extremely fast, way way faster by itself than if you have any kind of Python code wrapping it and also faster than any other DBAPI I've worked with.   So if you do profiling you will see that the vast majority of time with a SQLite app is taken up by SQLAlchemy Core and ORM.   Like if you look at this profile diagram from 0.7:  http://techspot.zzzeek.org/files/2010/sqla_070b1_large.png , the proportion of time actually spent within SQLite is that dark blue box in the center-left, where you can see "<method 'execute' of sqlite3>" , and theres below it a little maroon box that says "<method>", that's likely the sqlite3 cursor.fetchall() method.    So all of the performance gains these pragmas get you will at most make that one blue box a little smaller.  The rest of the screen represents time spent outside of sqlite3.

Some techniques on profiling can be seen in my stackoverflow answer here: http://stackoverflow.com/questions/1171166/how-can-i-profile-a-sqlalchemy-powered-application/1175677#1175677