composite key sort consideration

May 9, 2013 at 1:34 AM
since DBreeze sorts keys, and when we use composite keys, like the NonUnique
Keys example from the pdf docu.

And I read the benchmark for random keys insert (Test 7).

Imagine there are already 100,000,000 records, and everyday there will be like 1MLN more to be added, will there be performance issue?
May 9, 2013 at 12:47 PM
Edited May 13, 2013 at 8:51 AM
Everything depends, may be yes may be not. From mathematical point of view - not, but real life can put its restrictions and slow downs. HDD writes are faster when it saves closer to the center of a disk and slower when it saves closer to its periferal etc...

Dbreeze is kind of a radix search trie of common usage.

The rest is up to your data and its distribution (btw. that's the reason why I personally don't like any real object databases - it's not possible to create performant universal tool, every data set needs its own approach - sure, if u take care the speed).

I read about new STSdb possibilities. They made some synthetic tests and achieved good performance, trying to insert 10MLN of random keys, using 2GB of RAM.

Probably the secret name of such test is MAGIC HASH (thou it's not published yet and we didn' t see real benchmarks in all necessary conditions).

Imagine that you know approximately your data distribution and you are ready to sacrifice with 2GB of RAM, then you create data blocks which can store up to 10000 keys and you can hold up to 10000 such blocks in memory, before you insert them (about update nobody talks and nobody talks about commit after every insert). Speed of such test will be amazing and residing space will be quite good. You can achieve the same results in DBreeze, which will store only links to such blocks. Simpliest example of dividing data onto blocks: when int from 0- 9999 is 1;from 10000-19999 is 2; from 20000 - 29999 is 3 etc. So, as key it's possible to save 1,2 or 3 and as value corresponding data ranges (from 10000-19999 under key 2 etc.). Range selects and batch inserts, for data located close to each other, will be fast - we read complete block and then return subvalues from the RAM. When you have 2GB of RAM you can hold lots of such blocks there and operate with them effectively in batch operations even random (when data distribution entropy is not really high). If we know the character of keys we can try to find the best hash for it.

The problem starts when you must make commits often - databases will grow up fast (if every update must be saved to the end of the file, or will work slow in case of updates of the same sectors), cause can work only by big data blocks. RAM usage will be big.
But who cares? Everything depends upon tasks.

The same about READs. If you load at once big data block (already sorted into RAM) selecting it will be a fast procedure, from the other side, random selects with a big spread and many threads will give huge RAM consumption and a speed loss.

There are some other factors.
Speed of HDD is slow: HDDs 7200 rpm = 120 rps -> only ~120 real physical updates of sectors per second are possible (even twice as less).
OS cache is also very important thing - there is such layer between your program and HDD - it influents on the speed of all operations.
If after tests we see that update of the same position in the file is much faster then 120 per second it means we save data into OS cache and really not flush it to the disk, power loss and your data can be lost. So 120 real physical updates per second.
Speed of SSD is much faster, especially for random sectors access. Practically all database engines will work much faster on newer SSDs.
May 10, 2013 at 7:46 AM
I have updated a bit previous answer
May 13, 2013 at 12:10 AM

Thanks for the detail reply. It's very useful and fundermental for data access.

Even there is a cache layer (either the one built-in DBreeze or the one from OS), it will finally spend sometime to flush to disk. So when data keeps writing, the total amount of load is same and but the physical limit of a disk/memory is fixed, so finally it will reach the threshold and cache will be useless.

For my case I do not have a lot of memory to utilize, so trading memory for performance may not be the best option.

However, modern HDD has read/write normally above 50MB/s, but SSD has over 500MB/s. If a NoSQL engine can achieve random insert like 1MLN within 30 minutes, it's still fine, since data read/write are normally spread within the whole day (though have peak/off peak hours).

Regarding RPM, it maybe a major issue for a single HDD, but could be eased with HDD RAID or even SSD RAID.

In my previous work(RDBMS), I need to handle over 2 billions records in a single table (and a lot more other tables over 1 BLN), with proper index there will not be big problem with read. But when it comes to write, the choice of index really matters, when it comes to a threashold, we need to upgrade hardware (memory, disk etc).

So far I find DBreeze is fine for the performance, but it's still for dev environment. We will see what will happen when we push it to demo or even prod environment.
May 13, 2013 at 8:26 AM
Good luck