Sqlite Storage checkpoint issue

[tvg...@gmail.com]

Hi,

We are using sqlite storage engine, we are facing issues with checkpointing (WAL size going into GB.. It looks like by default checkpointing is disabled, so we enabled auto checkpointing..

<pragmas>
wal_autocheckpoint 100
wal_checkpoint FULL
</pragmas>

Now, It looks like its trying to do checkpointing, however due to the connections arleady open, it couldn't do that..

So to give overview of our architecture..

We connect to db from multiple processes

- 2 read process (async read with our async connection pool to limit the connections)

- 1 write process (async write)

Also we have tried to manually run the checkpoint,  for every 1min (but no luck)

conn._storage._load_connection.cursor.execute("PRAGMA wal_checkpoint(3)")

we are getting database locked issue.. so I presume, since read connections are already opened, its unable to complete the process.. I could confirm when read processes are killed and write can execute it.

Traceback (most recent call last):

 File "/opt/app/xxx.py", line 508, in updater

 conn._storage._load_connection.cursor.execute("PRAGMA wal_checkpoint(3)")

 File "/usr/local/lib/python3.7/site-packages/relstorage/adapters/sqlite/drivers.py", line 113, in execute

 return sqlite3.Cursor.execute(self, stmt)

sqlite3.OperationalError: database table is locked

Also I could confirm same behaviour from sqlite3 client.

/opt/app # sqlite3 main.sqlite3

SQLite version 3.28.0 2019-04-16 19:49:53

Enter ".help" for usage hints.

sqlite> PRAGMA wal_checkpoint(3);

0|1148|1148

sqlite>

We have tried closing connections, but zodb connection object close doesn't close the underlying storage connections.. instead it put them in the internal pool.. we could find a way to close storage connections with public APIs.

We have tried to the drop the internal storage conenctions (_load & _store), then it seems to be working well..

(env) venu@venu:~/workspace/xxx$ lsof main.sqlite3

COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME

python 1953 venu 8ur REG 8,2 376709120 14429800 main.sqlite3

python 1953 venu 12ur REG 8,2 376709120 14429800 main.sqlite3

Here is our async pool.. we prefork the connections (we are seeing sometimes db.open is taking longer time to create connections, so we are doing prefork) and use them.

def ctor(self):

        tm = transaction.TransactionManager()

        conn = db.open(tm)

        return conn, tm

store = {

            "read": [asyncio.Semaphore(value=rlimit), []],

            "write": [asyncio.Semaphore(value=wlimit), []],

        }

store["read"][1] = [ctor() for i in range(rlimit)]

store["write"][1] = [ctor() for i in range(wlimit)]

@asynccontextmanager

        async def pooled(mode="read"):

            lock = store[mode][0]

            conns = store[mode][1]

            await lock.acquire()

            try:

                conn = conns.pop(0)

                if conn[0].opened is None:

                    logging.info("connection closed, creating new one")

                    conn = ctor()

                yield conn

            finally:

                conns.append(conn)

                # conn[0].close()

                conn[0]._storage._load_connection.drop()

                conn[0]._storage._store_connection.drop()

                lock.release()

We find this not proper way, kind of hackish and also, lots of stuff is obscure for us still.. 

So would like to confirm with you whether we are missing anything or is there any API's exsits to clear storage conenctions to allow sqlite to do checkpointing..

Thanks in advace..

- Venu