Sqlite (read: Sqlite3) is an embedded file-based database. Why not embed it into a web application? It might seem stupid, however, there is a large range of projects that don’t need an outstanding performance on the DB layer and don’t require much scalability. There are lots of web apps hanging out there in the Internet that get a dozen of hits a day, like personal blogs, etc. It’s not too clever to set up a database engine to serve these apps, is it? To make sqlite work well in a web application requires understanding of its multithreading limitations, and awareness of Java web container multithreading nature. I will write more on the issues that need to be considered when embedding sqlite into a web application in my next post. As for benchmarking it was performed on a relatively slow machine (1GHz Mobile P4 Celeron) running latest (2.6.31.3) vanilla Linux with Tomcat 5.5 on latest (1.6.0_16) Sun JDK. Sqlite JDBC driver was used with the native access mode. A simple web application was made with a single JSP page which 1. called one insert query 2. called one ‘get last inserted id’ Sqlite native query (to get the id of the just inserted record) 3. called one select query (to select the inserted value by its id and to compare it to the original – just to make sure that my approaches to solve sqlite’s multithreading issues worked). JMeter was running on another machine. Four scenarios were tested. All four can be considered as a non-realistic / stress scenarios as there’s not a big chance for a small app on the Internet to get user activity of this kind. If you get 20 users to hit your app each 10 seconds you’ll become a millionaire selling ad space very soon 🙂 So, the first scenario was 20 users hitting the page each 10 seconds: 20 users 10 seconds. The system could serve more than 6000 requests per minute. The second scenario was 20 frenzy users – they hit the page each 3 seconds: 20 users 3 seconds. Still ~6200 requests/minute, but the average request time increased. It must be noted that the median didn’t change though – 50% of requests still took less than 5ms to serve. The third scenario is an extra popular page – 100 users hit it each 10 seconds: 100 users 10 seconds. It results in 5300 requests/minute, median is 90 ms – not as good as with 20 users, but still hell good for a web page with such a load. The fourth scenario is the ultimate stress test for the embedded sqlite – 100 frenzy users hit the page each 3 seconds! 100 users 3 seconds. This one I looped for 1000 times per user instead of 500 for the previous tests. The results are: 5477 requests per minute, median is 100 ms. Outcome is: sqlite is very usable with web applications in terms of performance. It is not as easy to use in terms of implementation – you need to take care of multithreading issues, but that’s easy. Talk to you about the implementation patterns in the next post! PS: the java process never consumed more than 60% of CPU during the test, it was HDD to blame for any bottleneck. Which was pretty much expected for a file-based DB. And I bet my ass that sqlite will perform at least twice as fast on SSD. Software Development