I am currently trying to improve web application performance. The purpose of the application is to provide (real time) analytics. We have a database model that is similar to star schemaseveral fact tables and multidimensional tables. The database works with the engine Mysqland MyIsam.
The size of the fact table can easily go into the top millions, and some dimension tables can also reach millions.
Now the point is that query selection can become terribly slow if dimension tables are joined to fact tables and aggregations are also performed. The first thing that comes to mind when you hear this, why not predict the data? This is not possible because users are allowed to use several freely custom filters.
So, I need an all-in-one system that is suitable for all purposes;) Unfortunately, it has not yet been invented. Therefore, I came up with the idea of combining 2 existing systems. Mixing the database row orientedand column oriented(for example, infinidbor infobright). Saving the mysql MyIsam solution (for quick inserts and row-based queries) and adding a column-oriented database to them (for quick aggregation across multiple columns) and filling it periodically (nightly) through cronjob. The problem is when the current data is requested (they must be in real time), so I may need to get data from both databases, which can complicate the situation.
The first tests with infinidb showed really good performance when aggregating multiple columns, so I really think this can help me speed up the application.
So the question is, is a good idea? Perhaps someone has already done this? There may be better ways to do this.
I have no experience with column-oriented databases, and I'm also not sure what the schema should look like. The first tests showed good performance in the same structure star schema like, but also in the structure big table like.
Hope this question will be consistent with SO.
source
share