MySQL Database multiple optimization scheme

MySQL Database multiple optimization scheme

1. Hardware optimization ( physical Machine )

A,CPU 8~16 CPU ,

b,mem 96g~128g, three-and- four examples. 32~64g Memory,2 instances

C,the more disk number the better , performance:SSD ( high concurrency ) > SAS ( normal ) > sata ( offline )

4 Disk For example:RAID0 > RAID10 > Raid5> RAID1

d, network card, multi-Nic bond,buffer and so on. Kernel tuning can handle

2. Software optimization

operating system:x64,

software : MySQL , compile and install, compile and optimize.

3,my.cf parameter Optimization ( the amplitude of the optimization is small, most of the schema and SQL statement optimization )

Reference:http://www.linuxyw.com/a/shujuku/20130506/216.html

Http://www.ha97.com/4110.html

thought:

monitoring: Production parameters are the parameters of the general situation

Command monitoring:show Global status\g can then see how much each parameter is used and how large each cache is. Adjust slowly through monitoring.

Tuning Tools :mysqlreport , There are other tools

3 . Optimization of SQL statements

A, the optimization of the index

1, catch the slow SQL. Let the DBA as early as possible participate in the development inside and develop joint discussions. White list mechanism

Long_query_time= #

log-slow-queries=/vra/lib/mysql/slow-log

2, slow query log Analysis tool --Mysqlsla,

Pt-query-digest.,mysqldumpslows,

3, every night 0:00 Timing Analysis Slow query, sent to the core development,DBA analysis, and senior operations,CTO mailbox.

the DBA analysis gives optimization recommendations -- Core development Confirmation Changes --->dba on -line operations.

b, large complex SQL statement split into multiple small SQL statements

Sub-query, JOIN Table Query. A table of data is too large, how to split all have problems. Need to split the table, open the library

C, the database is where the data is stored, but not where the data is calculated.

calculation, Application class processing, to get the front-end application to solve, prohibit processing on the database.

d, search function,like "%51cto%" , generally do not use MySQL database.

4, the optimization of the architecture

A) business split : search function,like "%51cto%" , generally do not use MySQL database.

b) Business splitting : Some business applications are separated using NoSQL persistence, for example : Memcachedb.redis,ttserver.

For example: fan concerns, friend relationships, and so on.

c) the database front-end must be added cache, such as:memcached, user login, commodity query and so on.

d) Dynamic Data static, complete file static, page fragment static

e) database cluster and read/write separation, a master multi-slave, through the program or dbproxy to separate the cluster read and write.

f) Single-table looked up million, the demolition of the table, different requests to different machines above, the front of the hash to take the mold.

5,process, system, security optimization.

any update that considers a database record should go through a process:

human processes: Develop-and- Core Development Confirmation --- operations or DBAs

the process of testing: Developer Intranet test- and IDC test - on-line execution.

client-side management, client PHPmyadmin. Client access control. Prevention

Above excerpt from old boy videos

This article is from the "Work hard" blog, please be sure to keep this source http://amyhehe.blog.51cto.com/9406021/1700262

MySQL Database multiple optimization scheme