Feb
25
2014
--

Quick review of InfiniDB 4

I’ve mentioned InfiniDB before in a previous post titled, “Star Schema Bechmark: InfoBright, InfiniDB and LucidDB,” but it’s been 4 years since that was published. Recently I evaluated column-storage solutions for Percona Cloud Tools and took another look at InfiniDB 4. There was the release of version 4, which I think is worth attention.

What is interesting in InfiniDB 4:

  • Fully OpenSource, GPLv2. There is no reserved features for Enterprise version
  • Multiple CPUs are used even for single query execution
  • WINDOW functions in SQL queries

What is WINDOW functions? In short, in regular SQL for a given row you can’t access to previous and next rows (at least not without complex SELF JOINS) and WINDOW function allows that.

As a basic example:

SELECT start_ts,
       query_time,
       LAG(query_time) OVER (ORDER BY start_ts) prev_time
FROM   qc
WHERE  agent_id = 1;

So for given row (start_ts) we access not only current query_time, but also a previous query_time: LAG(query_time). So the result looks like:

start_ts       | query_time     | prev_sum
---------------------+----------------+----------
 2013-06-18 22:55:00 |       0.281992 |
 2013-06-18 23:00:00 |       0.292305 | 0.281992
 2013-06-18 23:05:00 |       0.215658 | 0.292305
 2013-06-18 23:10:00 |       0.243597 | 0.215658
 2013-06-18 23:15:00 |        0.35189 | 0.243597
 2013-06-18 23:20:00 |       0.543079 |  0.35189
 2013-06-18 23:25:00 |       0.495779 | 0.543079
 2013-06-18 23:30:00 |       0.589326 | 0.495779
 2013-06-18 23:35:00 |       0.210907 | 0.589326
 2013-06-18 23:40:00 |       0.167944 | 0.210907
 2013-06-18 23:45:00 |       0.202401 | 0.167944
 2013-06-18 23:50:00 |       0.171676 | 0.202401
 2013-06-18 23:55:00 |       0.234001 | 0.171676
 2013-06-19 00:00:00 |       0.214086 | 0.234001

It gives a lot of interesting possibilities, like calculation of a growth or decline for time periods.

I still continue to evaluate different solutions but I decided to share this about InfiniDB, as it looks quite interesting now.

The post Quick review of InfiniDB 4 appeared first on MySQL Performance Blog.

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com