Today I have finally come to the conclusion that nntp//rss, as of v0.4, will no longer use hsqldb as its embedded database. It has been a bit of a rocky road over the past few months, with memory consumption issues, and problems of reliability, especially after system failure. I’ve spent some time evaluating alternatives, and have decided that Mckoi will may be (see update below) the replacement.
I’m currently in the process of finishing up a migration process to upgrade v0.3 users’ hsqldb databases to Mckoi, and will test to see how the whole thing behaves on my 100K+ item database.
Note that this is in addition to the MySQL support that has also been implemented. The demo server has now been running on MySQL for about a month, with good results. This will play a key role in the more group-oriented features to come in later releases of nntp//rss. Which, by that time, may deserve a rethink on the name…
Update: Stop the press… somebody referred me to Axion, a “small, fast, open source relational database system (RDBMS) supporting SQL and JDBC written in and for the Java programming language”. Will be checking this out as well.
Update 2: Looks like my 100K record test is proving an interesting challenge. The first the part of the challenge being getting the data out of hsqldb. As hsqldb – to the best of my knowledge – does not implement any disk scratch strategies for queries, it creates the entire result set within memory. I’m now performing iterative queries across the items table in my nntp//rss database to extract the content. This is turning out to be a long process. Anyway, once I’ve got the data out of hsqldb and into Mckoi, Axion et al, I’m focused initially on the following areas:
- Start up speed, given a non-trivial database
- Update/Insert speed
- Querying across a large recordset (on indexed and non-indexed columns)
- Shutdown speed
Watch this space for further updates!
Update 3: Managed to port 100K database to Mckoi, but getting some strange exceptions. Still investigating. Also looking at the potential of other persistence mechanisms, e.g. JDBM and SMYLE. Axion looks interesting, but has a more limited range of features (no multiple aggregate functions within a select, no GROUP BY).