- A managed vps server
- InnoDB storage for big tables
- 18gb ram
- 8 x 2.40 GHz CPU.
I have a pretty big MariaDB database table which is used for Business intelligence analytics data. The current table data fluctuates from 150-180 million rows depending on data that is being imported.
The database is filled through a java file that was custom written, and the data is being read through a custom PHP api, through a method that uses the table indexes. The data is date separated and currently the table holds around 2-3 years of data.
My problem is that whenever we see an error with the data we usually need to re-import large volumes of data going back a long time. Meaning tens of thousands of report files that each is being consumed by the java file and imported one by one.
I've done some speed optimisation to the import process, and a single report doesn't take long, unless the report is 300mb or so - then it takes around 10-15 minutes depending on the busyness of the database at that moment.
The daily report import usually has 200-400 files and it takes an hour in the early morning when no-ones in the office. It's all ran by cronjobs and custom report downloading scripts from various sources.
The daily imports and data fetches are pretty fast - however when one wants to make let's say a years data fetch where every day would be visible in the graph for one metric - it can take 10-15 minutes or longer if multiple people are trying to do the same, or if something big is being imported
The data that is being fetched is not a single query, I separate every day as a separate request - that sped up the fetches 10 times or more, depending on how long the time period is.
In a perfect world I'd like to optimize the structure or any settings I could so I could import the data faster and fetch longer timeframe data faster. Our current approach is to have a single database separated for analytics data, with a couple of helper tables.
What kind of optimisation could I do for this amount of data, knowing that we grow around 5-8 million rows per month (that could increase)?