CODEWITHSUNDEEP
mysqldatabase
Syed Shahzaib
Syed Shahzaib

Reputation: 23

How to handle Huge Mysql Database more than 8 TB's

I am working for huge database project i am developing application which will instantly filled with data in GB's i want to know points i need to take care during database design please suggest ASAP.

Thanks

Upvotes: 0

Views: 2399

Answers (1)

Meta_data
Meta_data

Reputation: 608

so 8Tb is not that huge for start , i manage 2Peta on Sql Servers and i can a sure you three things:

  1. you will make critical mistakes and lose data
  2. you will use any backup scheme at some point
  3. you will not be online for the next year 100%

after understanding those points, i hope you will find the following advice helpful:

  • hire a professional data base consultant - i recommend Percona. this is a better SQL Scheme and it will help you with sizing, Scehmes and you can even use their Active Active option to allow distribution of work loads with more then one server . other solutions for this are so complex and work poorly compare to their system.

  • Now 8TB have all kinds of usage, you didnt gave any explantion on how you are going to use this or for what, so i will assume that you are going to use this at on of the following ways:

    a. high write load , simple extract queries e.g : Select * form table where id =98 ; Id is indexed

    b. high write load , complex queries Select SUM(price) form table where id =98 and field2 IN (a,b,c);

    c. Medium work loads , lots of select queries

  • So you should use index fields , the more index you will have it will take more disk space and insert \ updates will take longer but select will be faster.

  • you must use Innodb if you plan on high query rate , MyIsam will lock the table and you will be stuck.

  • Consider on using Master/Slave Scheme to share the loads , so all selects and backup should run on the slave.

  • Run profiling on your queries and learn which fields to index , you can also look into partition to manage your table in a better way but watch out to partition on the wrong keys it will kill your server.

  • Make sure that your server have enough IO , MySql Love Io (Read / Write from disk) if you have SSD it should be better. try also to allocate as much Ram as possible , InnoDB can upload all the table to the RAM (Look into MyCNF configuration for innodb).

  • Install Percona instead of MySQL , This will be much better for : Replication, Backups , Scheme Changes , Name Changes for tables , Error Log , Table Lock Control and much More

  • Run Optimize tables and plan ahead your maintenance , Have a crisis plan for every Scanrio

  • Backup your tables

  • Practice Bakcup restore

  • Use file per table , This a critical configuration that will help you to restore your tables, other wise mysql create on big file from every database

  • Cancel ip resolving , it will save you CPU

  • Fun fact , top numer of unique keys at MySql is 17.. in case you need this

  • Delete data you don't need , create scripts that monitor which data is needed , don't be a dumb DBA there is a lot of unnecessary data . demand to know data Life cycle and use this to clean y

  • If the Write rate is too high plan to use ETL .. For example : Memcache that holds all the write and once every 5 minutes - create multi insert query and write this at one query, minimize the sessions.. this is of course if there is no need for this data to be in real time query.

  • if there is a write that needs to write data to SQL in order to get a unique ID .. well this is dangerous in case of high volumes. it depend on your hardware. in general its stupid. if you are not running a bank its really unnecessary.. try to create random unique number instead

  • Design your tables in way that its easy to understand what they are holding.. write comments it will help you one day

i hope this will help you, if you have any questions or if you can share any more data that will help me to better understand your needs feel free to comment..

Upvotes: 5

Related Questions