How bad is SQLite concurrent access?

SQLite is fairly light – this has been justified by me by running it on a cheap plan which has 512MB memory with a number of other services up and running. At its peak time I had about 5k pageviews/day with each view involving a number of DB reads – I know that traffic is probably nothing, but the point is I didn’t see any type of sluggishness so that’s good.

But that’s only some database SELECT. Now I am building a new website with concurrent read/write. Will SQLite live up to the load?

After reading extensively on Stackoverflow.com I believe SQLite is not suitable if you have multiple users with parallel writes. Since each write will put a lock on it, the data pipeline can easily get congested.

Out of curiosity, I did the following experiment to validate the above statement.

In my backend code I have a function, say insert() that inserts into DB a record of 6 fields, with one field of about 100 chars and others of various types (timestamp, id, etc.). And I have another function foo() that runs this insert() every 10 milliseconds. I made a web link that triggers this function foo() from the browser so I can start as many as I like. I started it in 45 tabs, meaning in theory there are 45 inserts going on every 10 milliseconds. That’s more traffic than 99% of the websites out there I believe. I watched the backend was going like crazy. Concerned by the fact that slow I/O might hinder the overall performance, I grepped ‘ERROR’ only which occurs when something is wrong. I kept it running for a few minutes and about 1 million lines were inserted and I only saw 10 ERRORs (“Error: SQLITE_BUSY: database is locked”). This is not bad at all.

Then I further tested it with benchmark tool ab: ab -n 5 -c 2 [url]. It failed to handle well. Maybe http module from node.js isn’t scalable or SQLite could. I am not sure exactly which one caused it.

Update: turns out there is a bug in Mac’s ab that’s why I  got ‘send request failed’ message. I did another experiment this time visit will Insert one record with a number of Select:

I did it on a Linux box again with -n 1000 -c [10,20,50,100,200,500,1000] and following graph shows the result

Overall MySQL responses faster than SQLite when -c increases and SQLite failed to respond when -c > 200. I’m not sure how many websites will ever reach this level of concurrency although faster response seems always better if you can afford the memory. However, SQLite isn’t too bad consider its light resource requirement and convenience of use. People seem to be against it when it comes to using it for production but I don’t see the reason why.

https://www.sqlite.org/whentouse.html
https://www.sqlite.org/lockingv3.html

A few ways of inserting datetime objects in SQLite

This has been bugging me for a while. First of all there’s no such column type TIMESTAMP or DATETIME in SQLite (https://www.sqlite.org/datatype3.html). Most likely you’ll end up with using just TEXT or INTEGER and oftentimes you found yourself inserting content of different kinds in that column. To make it easier after searching I found a few ways:

1. Make current system timestamp default value in table schema:
   Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
   Haven’t tried.
2. use SQLite’s built-in datetime functions
   e.g. to insert timestamp, use strftime(‘%s’,’now’)
   similarly, to retrieve it: date(ctime,  “unixepoch”, “localtime”) or time/datetime depending on what you want
   see: https://www.sqlite.org/lang_datefunc.html
3. In javascript, to get timestamp in seconds:
   Math.round(new Date().getTime() / 1000)
   And there are ways of retriving it in different formats: http://www.elated.com/articles/working-with-dates/

Personally I like to use timestamp everywhere so I don’t have to deal with strings until rendering.

Programming your mouse’s 4th button

I use an Apple mouse and its 4th button is idle. I browse the Web a lot and there’s no easy way to ‘Back’ unless you click the left-arrow button. I want to merge the two into one. So here’s what I did:

1. Open AppleScript Editor and type following:

 tell application "Google Chrome"
 activate
 tell application "System Events" to key code 33 using command down
 end tell

Save it as an application, say ‘~/chromegoback.app’.

2. Open ‘System preferences’ -> ‘Mouse’. Bind 4th button to the Application above.

3. Now enjoy your one-click going back.

The only problem is the application will be first opened and then closed so each time there’s a flash involved. I don’t have a good solution so far.

References:
1. All Key Code:
/System/Library/Frameworks/Carbon.framework/Versions/A/Frameworks/HIToolbox.framework/Versions/A/Headers/Events.h

2. Some AppleScript guidance: http://dougscripts.com/itunes/itinfo/keycodes.php