Comment by NamTaf | Hacker News Reader

Comment by NamTaf | Hacker News Reader

I've been playing with Infobright community edition but also evaluated InfiniDB. I found InfiniDB was not compressing my data nearly at all whereas Infobright was utterly jamming it down - factors of over 300x for even small datasets of ~2m rows.

I don't know what the differences are to produce that, but when it comes to storing as much crap as I was looking at, I was willing to design around the limitations that Infobright CE has (ie: no insert/update queries) rathre than deal with the massive extra disk cost. I have currently got 223m rows sitting in Infobright and it's taking about 38MB.

I really hope that the OSS project takes off and that InfiniDB sees some better compression implemented, similar to Infobright. The extra features that InifiniDB has over Infobright CE (not only insert/update but also a multi-threaded infile loader, for example) would convert me if only the compression were better.

I'm all new to this though so if there's some good reason why they differ so greatly I'm all ears and would love to know. Maybe I screwed something up in the configuration? I'm not sure.

Either way, it's sad to see them go. Columnstore databases fill a really useful application that I can only see growing over time as more and more operational data is collected by industry.

223 million rows in 38MB would mean less than a fifth of a byte per row. Is your data really that extremely repetetive?

Phenomenal. Some is binary (sparse, too - 99% false sort of thing), one other is an integer from -1 to 8, some are floats (decimal degrees for GPS) and one is a datetime. 38MB is what is reported when I use the following query, which I found somewhere:

SELECT table_schema, sum (data_length + index_length) / 1024 / 1024 'Data Base Size in MB', TABLE_COMMENT FROM information_schema.TABLES GROUP BY table_schema

You're right though - the cost per row sounds really low, I might try to find it via other means tomorrow and report back.

Yup, just confirmed that the raw folder size of the database is 39.8MB and reports 39MB (not 38) in my query. It is also 222.78M rows. Note that you need to remove the space between the sum and the bracket if you want to run that query.

I can only assume that IB does some sort of differential compression on the data to get such a small filesize on that data and that it's an artifact of the machine data I'm using. But that's what I think the next big wave of data will be - stuff generated by data loggers on machines and equipment and analysed to scrape out incremental improvements on efficiency, reliability, etc. from previously relatively low-tech industrial corporations.

Somewhat unrelated: some filesystems also provide online compression (e.g. ZFS). I think that's a more appropriate layer to apply general compression.

Doesn't that mean you'll always be loading uncompressed data into memory?

If your database has specialised compression, or keeps most data in main memory compressed (I can think of at least one that does), then this approach won't work.

For almost everything else though, putting compression in the filesystem layer is better.

Reply on news.ycombinator.com