The curious case of multithreading Sqlite

In a recent project, I've embarked upon the process of saving information in a quick and lightweight manner for which the serverless database, sqlite was ideal. This is not only a flexible database supporting dynamic data types, but also an extendible one used from embedded web browsers and mobile platforms to rails based websites(good riddiance, mysql) .The database is so lightweight that you'll find yourself exclaiming it as db. It is less than 100 kb in some cases and can be used as a memory resident database for the performance conscious folks and applications.
However, the support of transactions and scalability leave a lot to desire as the documentation cleary states the goals of this database is not these.
In my case, getting started was just the matter of finding the jdbc driver and it s documentation from its website. However, upon customization, there was a need for normalization which in turn required a sequential read/write operations. As soon as this was done, the multi-threaded application that was accessing started experiencing concurrency issues around the database.
This was resolved via using a little used jni wrapper that I discovered, sqlite4java. This not only solved the concurrency issues, but also enabled transactions. The beauty of this approach is also the fact that it uses a single connection as opposed to a pooled/fresh connection per every request, which tends to be a real performance bottleneck as we scale up our application.

For such a nice framework, it is a surprise that these changes have not permeated to the java/native jdbc drivers so far and the users have to cope up with its api at the moment. This could be probably because of its tight integration with the underlying C-based api that is quite performant as compared to its
alternatives in python and java, effectively making it non compliant with the jdbc specification.

As the single connection served entire applications, exposing it as a singleton was the easiest approach :


static SQLiteQueue queue = new SQLiteQueue(new File("BiddingsDB"));

static {
queue.start();
}

For providing a transaction-like support, there was a need to wrap the existing operations in this queue :

public static void databaseInsert(final List

Why performance matters #1

One of the many issues that i've faced as an enterprise java developer has been performance. It is important to understand the issues surrounding performance and address them before the problems escalate into unresolvable issues. It is probably the fixation of architectural design patterns and conventions with the enterprise java community that lead us to overlook various performance related issues.

The community has today moved a long way from MVC frameworks and monolithic j2ee 1.4 servers to increasing adoption of various open source frameworks and protals. The enterprise applications too have evolved from websites having component based solutions to portals and coarse granular SOA applications, built on top of web services. However, one thing remains constant, that is the layered execution model of any application. This can be demonstrated as follows :

Our Java EE Application
Application Framework
Application Server
Java Runtime Environment
Operating System
Hardware

It is important to note that the performance of the solution does'nt depends on the application alone, rather, it depends on correct performance tuning and optimization of all the layers associated with the application. This form of issue is known as Vertical complexity.

In actual applications, however, there are a lot of discrete components that are present, each with its own 'stack' of complexity. Together, this forms into another complexity known as horizontal complexity. These issues are apparant only when the application faces heavy load in its lifecycle or when it begins to operate beyond a single JVM. This can result in the following problems :-

• Slow execution of application- beyond the aggreed upon threshold in SLAs
• Application performance degradation over a period of time- memory leaks, resource allocation bugs
• Erratic CPU utilization and application freezing
• Performance anomalies which occur in production- hard to reproduce, which escape load testing

So, it is not hard to imagine why organizations and teams look forward to solve the problems. Solution to these problems has also a fancy name, “Application Performance Management” which is a set of recepies and approaches meant to address the issues listed above. In development, this is applied to memory analysis, code profiling and test coverage. Memory analysis can be done using any standard debugger and most IDEs usually have a nice interface to do so. Profiling, however involves some serious investment into the profiler that you are planning to use. Fortunately, some IDEs like netbeans have in built profilers, but external profilers like yourkit are more relied upon. A code coverage tool does the same thing for performance testing as it normally does for unit testing- identifying whether a given code is covered under performace testing or not.

Returning to our 'stack' for a moment, we can easily visualize the tasks needed for performance optimization at different levels.

While the performance at application level can be done via APM, the underlying application framework also plays an important role. For instance, if the framework is in beta and the application is using some new feature of it, chances are that it might have a bug that can wreak havoc upon the application and create confusing problems. The application server also needs to be properly tuned and customized for clustering and performance scaling if needed. In other cases too, there is a need for system administration of these servers as they not only act as the host for the application, but also provide monitoring activities (which can further be used as performance measurement) and manage external resources needed by the application. JVM is an often ovelooked slice of the stack, which if used correctly, can cause precise Garbage Collection trips, memory management for the application as well as its dependencies. It never hurts to have knowledge of the JVM memory areas like stack, heap and perm spaces, which are the places where the application and its server reside and operate. The operating system and hardware are the obvious choices for performance optimization because these provide the underlying resource allocation, scalability, computation and other aspects that are needed for the software. From a developer's point of view, the operating system and hardware is not generally modifiable, but the overlying layers are. So it is better to have an understanding of these concepts. In a future post, i'll start with analyzing specific performance issues relating to a tier and post recepies and solutions that I came up with so far.