MySQL Database server overall planning (methodology)

We are building MySQL database Server at the beginning of the rational planning, you can avoid many future problems, greatly saving our time and energy, to a certain extent to reduce costs. Of course, this will involve many aspects. such as machine selection, business evaluation and system planning.

all of the design is related to the specific needs, the first thing we need to do is to conduct an overall assessment of the business. Let me share a specific example below.

First, Business Requirements

Requirements	Index
Response time	querying and manipulating requests Ms level return
Total data	1 The year is about 500GB The amount of data
Request Volume per second	each second has 3W Second Request
Read/write ratio	read/write ratio Yes 1 : 1
Degree of importance	core business, P1 level Failure
Other Notes	data is time-sensitive, with less access to historical data and general processing of recent 7 Data in days, the overall length of the data is approximately 1K

II. Operational Assessment

The amount of data for step1:1 is about 500GB.

Result: The amount of data produced per second is 500*1024*1024/(365*24*60*60) =17kb.

Step2:3W requests per second; Read/write ratio is 1:1

Result: Read Requests per second are 15000 times, and write requests per second are 15000 times.

Step3: The length of the record is approximately 1KB

results: According to the results of Step1, the Insert data write is approximately 17KB per second, and according to the results of STEP2, the request written per second is 15000. times, the 14083 is the update and delete operations. Because the MySQL write operation is processed by the page and the page size is 16KB, the number of write operations per second is 16KB*15000=234MB, assuming the pages are different for each operation . The number of read operations per second is 16KB*15000=234MB.

STEP4: Processing data for the last 7 days

Result: The amount of heat data is: (500/365) *7=21GB

STEP5: Operation MS level return

Result: The operation ms level is returned, and the basic balance of reading and writing. You need to load as much data into memory as you can. If the memory hit rate is close to 100%, then the Innodb_buffer takes approximately 21GB,while the rest of the memory needs to be 1~2GB, so the memory is over 32GB. The Write Bandwidth (wbps) is limited to 250mb/s, according to the hyper-provisioning principle. Read Bandwidth (RBPs) is limited to 250mb/s.

three, hard disk selection (HDD vs SSD)

HDD SSD hdd hard disk, but SSD the hard drive price is more expensive. Therefore, we can reduce the total cost of ownership through reasonable allocation.

for example, for some log files, these log files are mainly sequential IO, we can put these files on the HDD , you can consider using RAID5 level to improve the fault tolerance of the log system. For data files, we can consider putting on SSDs and using RAID10 to improve fault tolerance.

Four, the model test

Performance comparison test : The performance index of database performance under stress test for different hardware devices. Make an overall assessment of the hardware performance.

stability Test : There is no jitter phenomenon, can provide continuous and stable service.

power-down protection test : This link is very troublesome and requires the support of the system engineer.

Memory anomaly test : Test memory is prone to problems, can provide stable support for the business.

In addition there are IO devices and bad disk refactoring.

After a series of tests, we can select Two or three candidate models, we should try to avoid the application and equipment binding, to prevent a single model is out of stock, insufficient supply impact business.

V. Cost Assessment

through the previous series of preparatory work, we can select the candidate models, consider our cost of use. This includes:

Equipment Cost

operation and maintenance cost

Power cost

Special attention : Although we sometimes increase the cost of single use, but the overall cost of use has decreased. This is because we have improved the performance of individual machines and reduced the number of machines. Lower cost of operations and power, and even lower overall equipment costs.

Vi. Document System Planning

MySQL features of the database:

--Single Data Directory ( Single instance cannot specify multiple databases )

--Mixed read and write ( logs and data are read and written in different ways )

--Request Random

File system partitioning:

/dev/sda1/boot

/DEV/SDA2/

/dev/sda3/home

/dev/sda4/tmp

/dev/sdb1/data

/dev/sdc1/log

based on experience, it is recommended that the IO scheduling strategy be deadline :

#echo deadline >/sys/block/sd{b,c}/queue/scheduler

MySQL database log files are read-write in sequence and are recommended for normal SSD hard drive.

--binlog log files,error log files,slow log files can be stored in /log in the log directory

--tmp file specified as the system's directory / tmp

--all other directories specified as data directory /data

Note: Although log files are randomly read-write, it is possible to generate random read-write behavior by putting several log files into one partition.

Resources:

inexpensive SSDs for Database workloads :

http://www.percona.com/blog/2013/10/03/inexpensive-ssds-database-workloads/

SSD vs Hdd:which is the best? :

http://www.techradar.com/news/storage/computing-components/ssd-vs-hdd-which-is-best-936111

Solid State Drive vs Hard Disk drive price andperformance Study :

Http://www.dell.com/downloads/global/products/pvaul/en/ssd_vs_hdd_price_and_performance_study.pdf

SSD vs HDD :

Http://www.storagereview.com/ssd_vs_hdd

This article from "Zhang Zhiliang" blog, reproduced please contact the author!

MySQL Database server overall planning (methodology)