July 14, 2012 | abalogh
When Should You Use InfiniDB?
When should you use InfiniDB?
The short answer: InfiniDB is an excellent solution for any analytic query workload where analysis of millions or billions of rows is required to further the business goals. InfiniDB runs analytic queries 10X-50X faster than traditional databases, is far simpler to use than traditional systems, and can scale to grow with your business.
What about row-based DBMS (and NoSQL) technologies?
Most relational databases in use today are designed to handle online transactional processing (OLTP) workloads. Databases such as MySQL (MyISAM, InnoDB) and SQL Server rely heavily on row-based storage and indexes for performance. NoSQL products like Voldemort or MongoDB rely on specialized access paths to read or write individual groups of columns together. These technologies are all optimized for record access: inserting or reading back one or a small number of rows very quickly. However, these databases don’t typically hold up well for analytical workloads. Transactional databases are simply not designed for the performance, scalability, flexibility and extensibility that analytic workloads require.
For analytics, when do traditional databases typically fall short?
- When performance cripples analytic queries. Row-based storage is optimized for reading and writing individual records, but is very inefficient for analytic queries, reading 10X to 100X more bytes than needed.
- When the data size makes index maintenance a problem. For some use cases this could be with 50 or 100 GB of data.
- When the number of indexes required becomes large. More indexes are required to support investigative analysis, ad-hoc queries, or expanding requirements.
- When the load rate fails to keep up. Adding indexes invariably slows load speeds, crippling the ability to load new data.
- When requirements are change frequently or are not completely understood upfront. Row-based storage can require rebuilding tables as columns are added.
- When tuning becomes a problem. Traditional DBMS and NoSQL technologies can run well under specific access paths defined by the DBA or the application developer but can offer poor performance if the business needs any other access path.
- When running queries inefficiently results in expensive servers and database licenses.
- When disk space or performance while reading from disk becomes a problem. In addition to their inefficiencies when reading from disk, row-based DBMS technologies typically aren’t as efficient when compressing data on disk.
So, when to use InfiniDB?
- When performance is critical to the usability or the ROI of the project. The tremendous I/O efficiency of InfiniDB combined with the extreme parallelism of our Map-Reduce distribution of work delivers incredible performance (10X to 50X is typical).
- When the combination of the load rate, the size of the data, and the business requirements for analysis collaborate to make index creation and maintenance a problem. This illness can have different symptoms: the load rate slows because of too many indexes, index creation/rebuilds time-out because of too much data, or performance declines.
- When compression is important. InfiniDB’s columnar technology allows for tremendous compression ratios, achieving 9.3X compression in a recent test with telecommunications Call Detail Records.
- When the project time is critical. InfiniDB delivers out-of-the-box performance, reducing the amount of time required to implement BI or analytic projects.
- When requirements are not fully defined up front. With InfiniDB, you don’t pay any performance penalty for queries if you include another column “just-in-case” and you can also add a new column at any time without requiring a table rebuild.
- When the DBA says “you can’t run that report,” “you can’t load that data” or “you can’t include that column.”
In short: as data volumes grow and business requirements expand, costs expand. These are the situations that require InfiniDB’s next-generation technology.
When not to use InfiniDB?
When the workload is row-oriented—if the database is used primarily to load, modify or retrieve records one-at-a-time or a few at a time. Columnar DBMS technologies in general are not well suited to these uses. For analytic workloads: if data volumes are small and are not growing, business requirements are unchanged, and there is no demand to drive down costs, then traditional technologies can certainly meet your needs.
How do I try InfiniDB?
To experience the difference that InfiniDB can make for your analytic workloads, we invite you to try InfiniDB Enterprise Edition for free. See how ASI Group uses InfiniDB to scan databases exceeding a billion rows, allowing analysts to very quickly analyze multiple variables and make accurate decisions for the business.