I am trying to build an application, that uses MySQL at the database level. The main table in it right now is about 10 Mbytes. However, the client specified that this application should be able to store about a billion of records for this table per year. By extrapolating the current size it turns out that this table should be able to store 750 Gbytes per year.
The question is - how to tune the database level and the upper levels for this requirement? More specifically:
- We have a database level on MySQL.
- We have a glassfish server that runs the app.
- We have an application that uses JSP pages and JDBC connections for the database.
- The operations we need to provide is the paging list (watch a part of records), add new records, update them and remove.
So what can I do at each level in order to improve performance?