0

I recently found out that, the databases does not have standard architecture(data model), some of them are,

  1. No relationship between tables(entities).
  2. No indexes defined.
  3. Only integer and varchar data types are used all over the database.
  4. Same data fields are stored on multiple tables (entities).

Example:

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.

Production:

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

  1. Consequences of the above mentioned issues?
  2. 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.

  • 1. No relationship between tables(entities). 2. No indexes defined. 3. Only integer and varchar data types are used all over the database. 4. Same data fields are stored on multiple tables (entities). Welcome to the real world! Many database "systems" have been designed by people who wouldn't be able to tell the difference between a schema and a turnip! :-) – Vérace Mar 12 '16 at 17:12
  • Based on the horrible appraisal of the schema, the only option is to embark on a rewrite of the schema and, hence, the applications. In doing so, you will probably get answers to your questions. – Rick James Mar 14 '16 at 1:10
  • As per our current requirement, rewriting the entire application is not a best solution, also it is not our primary concern at present, I just need to know the factors affecting the disk space and mysql backup storage space. – rathishDBA Mar 14 '16 at 6:10
  • In my opinion, it is not worth to optimize schema for occupy less storage space because the time of people is more expensive that storage space. It is worth spending time to optimize schema for other objectives (better maintenance, better performance, ...) it will repay on the long term. If your data are growing it is possible there is a chance to archive stale data (i.e. older receipt, older orders) to an offline database and mantain into online databases only the data for day to day work. – Giovanni Mar 16 '16 at 12:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.