Row Level Locking in SAP Sybase IQ 16

[Former Member]

This month I spent some time testing the new row level versioning (RLV) feature of IQ and looking at the implications for real time loading of data. This blog has some notes about that and some high level figures from the testing I did.

Sybase IQ has, at least in recent versions, always been able to load data at an incredible rate – if you have not already seen it, take a look at this link, Sybase IQ breaks (smashes to pieces) another record loading data at an almost unbelievable 34TB per hour.

http://scn.sap.com/community/developer-center/analytic-server/blog/2013/05/30/sap-sybase-iq-16-sets-...

When loading, IQ likes things in big chunks – this is where it gets the best performance. When it comes to smaller “nibbles”, performance is modest. However, perhaps the biggest challenge historically, has been dealing with the Table-Level-Locking nature of IQ.

This is what the new RLV feature is about; improving the OLTP performance of IQ and providing Row-Level-Locking capability. This extends the range of applications IQ is able to cater for and in many cases will simplify the application design.

At this point it is worth remembering that while IQ 15.x already had pretty good OLTP performance for a column based database, and that 16.x improves on this by 4-5 times, it’s OLTP performance is still to be considered modest; this will go nowhere near the fantastic transaction rates of Sybase ASE (which now holds almost all of the SAP SD benchmarks: http://www.sap.com/solutions/benchmark/sd2tier.epx).

Setting up the RLV was easy enough. The RLV itself is transparent to the IQ user – a dbspace is created of type RLV STORE, and a new configuration parameter (rlvmem) controls the amount of memory available for RLV operations. The RLV data is held in-memory with persistence provided via a log on the RLV STORE dbspace (put this on a fast disk). Row-Level-Locked tables are actually just normal tables that exist in the standard IQ space (referred to in 16 as TLV) that are enabled to utilize space in the RLV – the choice of whether a transaction uses the TLV or RLV portion is done at run time by setting a new option; SNAPSHOT_VERSIONING is set to either table-level or row-level. In this way, users can still access an RLV enabled table in a non RLV way and get all the high performance characteristics of TLV tables and use the RLV features only when they need to. Tables can have data in both the TLV and RLV portions active at the same time. A merge process (either automatically or manually triggered) brings data from the RLV into the TLV portion.

So, what kind of performance did I get? In the test I set up insert, updates, and deletes were around 3-4 times faster on RLV tables than TLV tables – taking the modifications rates into the thousands per second (low thousands on the modest hardware I was using). I also noticed here that the transaction rates in 16.x are higher than in 15.x.

The second area I looked at was concurrency. I did this by loading a set of data via a single connection, and then doubling the number of connections and halving the number of rows inserted by each connection. Moving from a single connection through to two, four, eight, and sixteen connections loading into the same table showed a very pleasing constant loading rate.

So to me, based on these tests, it looks as though the RLV does exactly what was intended. It would be interesting to hook up Sybase Replication Server to IQ16. Maybe that is what I’ll do next (unless someone already has – spoiler alert ?).