"MySQL" MySQL device selection and system planning-actual combat reference documentation-with accessories-available for download

More exciting content at www.leonarding.com

the Mysql Equipment selection and system planning

Introduction : The equipment selection of the project is a delicate and arduous task. Because it has one-time, non-change, compatibility, extensibility, functionality, performance, ease of use and other parameters of the threshold values to reference. For example, our project, first of all to understand how much this year's equipment procurement budget, how to allocate, the allocation of each project about how much, according to the budget criteria for grading. Second, because we are financial enterprise procurement scope is limited, there will be an alternative directory, listed above the brand, manufacturers, specifications and other information, can only be selected in these directories, if the device has more than the directory needs to write instructions to apply.

Once again the need to follow the requirements and their actual experience to select the appropriate equipment, I can enumerate a few reference parameters to help us choose the right equipment.

Equipment selection

CPU: Aggregation operation, degree of parallelism, hardware RAM structure, memory matching degree, is an important index to select CPU.

Memory: Result set response time, the degree of software load deployed, operating system, data interaction, memory bus, is an important indicator of memory selection.

Storage: Based on business data scenarios, throughput per unit of time, I/O efficiency, total data, read/write separation, sequential read/write, random read/write, data security level,HA, backup and recovery time, etc.

estimated data total: Generally we take a year as a unit of time, according to the interface specification and requirements, the length of all the fields in all the tables are added up, and calculate the total number of records, so that you can approximate the total amount of data, in accordance with the principle of super-distribution, enlarge Two Times to draw conclusions.

Network devices: Network throughput, maximum number of concurrent sessions, rate, whether data encryption, bandwidth, how many fiber ports and ports are supported, circuit protection, security mode, support for several disk channels (one hba+ a disk subsystem = a disk channel) an optical port or an electric port.

according to I/O efficiency can be divided into:pci-e card ->ssd->sas->scsi->sata

operating system: Paid version, free version, 32bit,64bit

System Planning : I think the system planning can be carried out together with the equipment selection, because they are a pair of twin brothers, the system of fine planning to help the selection of equipment parameters, the choice of equipment model to help guide the planning program. The following aspects are commonly used according to different scenarios.

Data area

System Area

Backup area

Temporary zone

Log area

Buffer

Business Assessment

Total data

STEP1:approximately 500G of data in 1 years

result : The amount of data produced per second is 500*1024 (MB) *1024 (KB)/(365*24*60*60) 31536000s=17kb/s

Requests per second & read/write ratio

STEP2:3w requests per second , read/write ratio 1:1

result : read requests per second 30000/2*1=15000 times / sec, write requests per second 30000/2*1=15000 times / sec

STEP3: Each data record length is approximately 1KB

results: Based onStep1the results, per secondInsertThe amount of write data is approximately17kb/s, you can find out every secondInsertthe Write Request17kb/1kb=17times. According toStep2the resulting write requests per second15000Times/seconds (InsertWrite Request+updateWrite Request+deletewrite a request),15000-17=14983Times/sto beUpdateand theDeletewrite the request. BecauseMysqlthe read and write operations of the database followPageprocessed for the unit, one page size is16KB(by default, you can also manually define8KB,32KB), assuming that each operation has a different page, the amount of write request data per second is16kb*15000Times=234m/s, the amount of Read request data per second is16kb*15000Times=234m/s.

STEP4: Processing data in the last 7 days

results: The thermal data volume was 500gb/365 days *7 days =10GB

Response time

STEP5: Operation Ms -level return

results : The result set requires milliseconds ms 100% calculation,

so The size of Innodb buffer(Mysql Storage engine buffer) is 10GB, while other components such as OS,application,Network approximately 4GB of memory is required, so the memory is over 14GB. According to the provisioning principle, the write bandwidth (wbps) is limited to 300mb/s, and the read bandwidth (RBPs) is limited to 300mb/s.

Degree of importance

based on the importance of business data, use the appropriate HA High Availability technology.

Mysql Equipment Selection system planning Cost Assessment Business Evaluation

Leonarding Liucheng

2014.06.26

Beijing &summer

Sharing Technology ~ Fulfilling Dreams

Blog:www.leonarding.com

This article is from the "Liucheng sharing technology ~ Achievement Dream" blog, please be sure to keep this source http://leonarding.blog.51cto.com/6045525/1431129