MYSQL Design

Business Request Analysis

1. response time (RT)

2. data amount

3. request number per second

4. read / write ratio

5. important level

Request Transfer

1. TPS

2. IOPS

3. CPU

4. Memory

5. IO

Example

Business Requestments 

1. Response Time: ms level

2. Data amount: 1T/year

3. Request number per second: 1w requests

4. Read / Write ratio: 4:1

5. Important level: P1 (if crash, client will lost money)

6. Others: only need recent 15days, data record is 1KB

Step1: 1T/year --> 34KB/s

Step2: 1w requests per second and Read / Write ratio 4:1

Read per second = 8000/s

Write per second = 2000/s

data record is 1KB --> 1 second: 34 insert, 1966 update / delete

Step3: mysql page size 16KB 

Write 16KB * 2000/s = 32MB/s 

Read 16KB * 8000/s = 128MB/s

plus 30% Disk bandwidth write 50MB/s  read 150MB/s 

Step 4: only need recent 15 days' data

1T * 1024 / 365 * 15 = 42GB

Step 5: response time ms level --> 100% hit ratio --> 42GB Innodb buffer --> plus 30% Total Memory 45GB

HDD vs SSD --> PCI-E SSD

Machine Test

1. Performance test

2. Stability test

3. Power off protection test

4. Memory Exception test

5. IO broken disk rebuild test

Cost evalution

1. Machine Cost

2. Maintenance Cost

3. Power Cost

Mysql Character

1. Single data directory

2. Mixed read and write --> separate the log file and data file

3. Request random

Mysql Filesystem Design (According to Mysql Character)

1. System partition --> HDD

2. Data partition --> SSD

3. Log partition --> HDD

4. IO scheduler --> deadline

Mysql server system structure

/dev/sda1 /boot

/dev/sda2 /

/dev/sda3 /home

/dev/sda4 /tmp

/dev/sdb1 /data

/dev/sdc1 /log

Check the I/O Schedule: cat /sys/block/sdb/queue/scheduler

Change the I/O Schedule to deadline: echo deadline > /sys/block/sdb/queue/scheduler

Mysql database system structure

- binlog : similar to oracle archive log --> /log

- error log: mysql database error records --> /log

- slow log: related business logic --> /log 

- tmp directory: use system tmp folder --> /tmp

- Innodb log: similar to oracle online log --> /log

- DoubleWrite data --> /log