External IMDB Service Setup
We have been advised to set up the IMDB (in-memory database) service on a separate server for our multi-tenancy architecture. We're looking for detailed documentation or resources that describe how to achieve this effectively.
Can anyone point us to some guides or best practices for configuring and managing an IMDB service on a separate server? Any recommendations on the best in-memory databases for this purpose would also be greatly appreciated.
1 reply
-
There is a good guide on scaling Pyramid in the online help (search for "scaling guide"). It covers the entire platform and has some recommendations on the in-memory database service ("IMDB").
The guide provides some general pointers because each database scenario drives different requirements. However, it correctly points out that CPU's are the main resource driving performance. Outside of that, we see that the depth of our tables and user concurrency are the other key variables to consider.
In our organization, we have numerous small databases (5-20M rows). We've found that 16-24 cores are enough for moderate usage and sub-second query times. We have other databases that are 150M-250M rows, that get heavily used, and we have them sitting on 32 core and 64 core machines. In the end, we simply modulate the resources based on need and add new servers when we need to spread the load out.
From our testing Pyramid's IMDB was as performant as SAP HANA and MS Tabular; and better than a single-node Exasol instance. It's significantly more economical than the other 3 as well. SAP HANA has more features than IMDB (which were not necessary) but costs +20x the price. MS Tabular can theoretically handle deeper data sets (we never saw good performance though when tables exceeded 1B rows), but we noticed performance issues in load testing for concurrency. Exasol offers a multi-node scale out option, which is useful for very large datasets that cannot exist in a single machine's memory. But it's slower than the others. and quite expensive.
Last, and likely irrelevant, IMDB seems to handle 2x the data footprint and runs twice as fast as Tableau's in-memory Hyper database on the same equipment with the same data. While Qlik's database seems to have problems with data depth beyond 100M rows especially if the data sets are broad (lots of columns). We noticed that IMDB stores data in a proper normalized SQL structure, and is not affected by the number of tables or columns, other than consuming more memory as you'd expect.
While we weren't able to check all engines. we can confirm that IMDB, like SAP HANA has 2B row limit on any given table.