I recently found out that, the databases does not have standard architecture(data model), some of them are,
- No relationship between tables(entities).
- No indexes defined.
- Only integer and varchar data types are used all over the database.
- Same data fields are stored on multiple tables (entities).
mobile_number int(100) geo_location_id varchar(200) Same user profile is stored in multiple locations.
There are many other issues, but as of now I am only concerned about the storage, production server have enough storage space and memory to deal with the growing data size, but in our backup storage, we are running out of memory, some specifications about our server.
Server OS: Cent OS. MySQL Server Version: 5.5. Engine Type: InnoDB. Number of Databases: 46. Number of Tables: Average 20 tables per database.
Backup: From replication server.
Backup Storage Type: NAS. Backup Size: 2 Tb (2 months). Data growth rate: 6%. Requirement: Need to store for 13 months.
Apart from performance issue, I need to know whether the above mentioned issues contribute to the increase of data size.
The server is at present used by many applications, if the above mentioned issues effect the storage space, is there any other possible solutions other than reverse engineering and fix it from scratch. Also, if possible, kindly mention the
- Consequences of the above mentioned issues?
- What are the things which effect the storage space?
This questions is linked to MySQL Backup Strategy have a look at it.
Thanks in advance.