Manso Hack: Speedup Google App Engine SDK SQLite Database

I was trying to initialize a local database using the Google App Engine SDK, and I was going crazy.

–use_sqlite parameter was not even solving my problem. Inserts on the database were really slow, like 10 per second. A nightmare.

OK, ok, it is an SDK, it emulates the server… it is intended to be used to develop your application, speed is not the most important thing… I agree. But if you have to wait 10 minutes to insert 1000 data entries, then you can desperate. If they were like 1 million in 10 minutes, then I would understand, it would be still slow, but I will understand, but 1000 inserts? 10 minutes? are we all crazy?

Anyway, assuming SQLite seems to be a good database and works usually great, I though I could get that speed improved. Looking at the SQLite FAQ I found following question:

(19) INSERT is really slow – I can only do few dozen INSERTs per second

Basically it seems SQLite should be fine doing 50,000 inserts per second in a normal computer, BUT to guarantee the data integrity it locks inserts until it makes sure they have been written to disk, so if your computer hangs your data remains safe.

These are good news because I don’t care of my local data in the local develoment environment, so in the event of a crash I can just rebuild it in a minute, instead of rebuild it in an hour.

Finally the solution is to use “PRAGMA synchronous=OFF“, and the only way of using this pragma is to edit a line in de Google App Engine SDK, after initializing SQLite database

So you’ll have to edit following file:

google_appengine/google/appengine/datastore/datastore_sqlite_stub.py

Look for the “__init__” constructor and after the connection to the database gets initialized “self.__connection = sqlite3.connect […]” you can place following statment:

self.__connection.execute(‘PRAGMA synchronous=OFF’)

Changing that line I went from about 10 inserts/second to more than 100 inserts/second. More than a 10x gain for changing a single line. Great!

Now I can continue on the thing I was working on without getting crazy.

Trackback URL

, , , , ,

Comments are closed.