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:
|
1 2 3 4 5 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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.
About the Author
Vadim TkachenkoVadim Tkachenko co-founded Percona in 2006 and serves as its Chief Technology Officer. Vadim leads Percona Labs, which focuses on technology research and performance evaluations of Percona’s and third-party products. Vadim’s expertise in LAMP performance and multi-threaded programming help optimize MySQL and InnoDB internals to take full advantage of modern hardware. He also co-authored the book High Performance MySQL: Optimization, Backups, and Replication 3rd Edition.
how do these compare in performance to a self join (on auto_increment_key = auto_increment_key – 1 for example?)
would love to see a refreshed version of “Star Schema Bechmark: InfoBright, InfiniDB and LucidDB”! They have also progressed a little
Hi Vadim,
Thanks for the nice write-up, the windowing functions are very powerful. There is a scheduled talk on “Windowing functions in MySQL with InfiniDB” at Percona Live on April 2nd. http://bit.ly/1mF1IVl
Cheers,
Jim T
Nice to see InfiniDB going completely open source for the core engine. I think this could be game changer for adoption of analytic storage engines for MySQL!
Ben your example, what if the key isn’t sequential?
When I catch some time to breather I’ll publish some of my experiments and benchmarks comparing InfiniDB and Infobright, including some of what I think are show stoppers. Probably after Percona Live though 🙁
Hi Vadim,
This really looks interesting, can be usefull in some cases 🙂
Thanks for sharing your information.
Cheers,
Ali Alwash
Indeed,it is interesting,for InfiniDB, but existing some years.See Oracle Analytic Functions.