Large Web site MySQL depth optimization Revelation 2

Life-long learning, the old boy education to achieve your starting point!

Believe that you believe in the old boy!!!

old boy -51cto- Open Class - Large web site MySQL Deep Optimization Secrets Part of the arrangement has not followed the sorry

?

Catalog

Large web site MySQL Deep Optimization Secrets ???? 2

Section 1 Chapters optimization of ideas and lines ???? 2

1.1 The idea of website optimization ???? 2

1.2 MySQL optimization, Nginx how can such things be optimized? ???? 2

Section 2 Chapters Hardware-level optimization ???? 3

2.1 Database Physical Machine ???? 3

2.1.1 CPU???? 3

2.1.2 Memory???? 3

2.1.3 Disk IO???? 3

2.1.4 RAID Array???? 3

2.1.5 Network card???? 3

2.1.6 Case???? 4

2.2 Hardware Tuning ???? 4

2.2.1 BIOS tuning improves CPU performance???? 4

2.2.2 Array card Adjustment???? 4

Section 3 Chapters Software-level optimization ???? 4

3.1 Operating System ???? 4

3.2 File system Layer Optimization ???? 5

3.3 Kernel level optimization ???? 5

Section 4 Chapters MySQL level optimization ???? 5

4.1 my.cnf Optimization of parameters ???? 6

4.2 design optimization for libraries and tables ???? 6

4.3 SQL Optimization of statements ???? 7

4.3.1 caught out slow query???? 7

4.3.2 generates Slow.log every day???? 7

4.3.3 as far as possible without subqueries, with join instead of???? 8

Section 5 Chapters site cluster architecture optimizes database ???? 8

5.1 Server Run multiple instances 2-4 a ???? 8

5.2 Business Split :???? 8

5.3 database front end plus cache ???? 8

5.4 Backup Policy ???? 9

Section 6 Chapters safety ???? of process system 9

?

?

Large Web site MySQL depth optimization secret

1. Optimization of ideas and lines
2. The idea of website optimization

Answer: From the user to open the browser, to return to the Web page content this process to optimize every detail

1. MySQL optimization, nginx such things how to optimize?

Recommendation: According to the OSI 7 layer model, from bottom to top ... Take this as the line

1. Hardware-Level optimization
2. Database Physical Machine
   1. Cpu

64-bit CPU, Server 2-16,CPU general 2-4 l1,l2 the bigger the better.

1. Memory

48g-96g-128g-256g

48G of 2-3 instances

96G of 3-4 instances

1. Disk (diskette IO)

Database is IO intensive application

1. Mechanical Disc

SAS (do not choose SATA), 300g*12 blocks, more disks, higher IO, SAS 15k HDD

1. Ssds????

Test comparison: SAS single disk Random io,3000iops, SSD single disk random io up to tens of thousands

1. RAID array

Select Hardware RAID (0>10>5>1)

1. Card

At least gigabit (bond), million gigabit switch

The database server is not virtualized as much as possible

Slave server configuration is preferably greater than or equal to master

From the library will take over the main library, the configuration from the library is too low, resulting in delay

1. Case

Baidu: IBM server, Memory 96g-128g, CPU48 core

Sina:dell r510 memory 48G, disk 300*12 block, RAID10

1. Hardware tuning
   1. BIOS tuning improves CPU performance

Turn on (DAPC) mode to play CPU performance

Start node interleaving to avoid NUMA issues

Close c1e and state, etc.

1. Array card Adjustment

Configuring the Cache and Bbu modules (mechanical disks)

Write policy (always write back)

Do not use the (WT) strategy

Turn off array pre-read policy

1. Software-level optimization
2. Operating system

Select the x86_64 bit system

system disk and data disk separate

In extreme cases, do not divide the swap partition

Avoid using the operating system's soft raid

Avoid using LVM

Dedicated library do not run (LNMP,TOMCAT)

1. File System Layer Optimization

Adjust the cache mode

Start wce=1 (Write Cache Enable)

Rcd=0 (Read Cache Disable)

System scheduling algorithm default CFQ (more moderate), database selection noop,deadline. Parameters can be adjusted for deadline (kernel parameters)

Centos 6.8 Default EXT4 can be used as the file system of the database, the size of the house, XFS is better

CENTOS7 also selects XFS by default, adjusting XFS logs and buffering parameters.

Mount parameters are important –o async,noatime,nodirname,nobarrier, etc.

1. Kernel level optimization

Reference Link: http://oldboy.blog.51cto.com/2561410/1336488

Vm.swappiness is set to 0, or 0-5, so the database does not use swap as much as possible

Vm.dirty_background_ratio set the 5-10,vm.dirty_ratio setting to twice times the front. The system data is continuously brushed to disk.

Reference Link: http://blog.sina.com.cn/s/blog_448574810101k1va.html

Reduce time_wait

Net.ipv4.tcp_tw_recyle=1,net.ipv4.tcp_tw_reuse=1,

net.ipv4.tcp_fin_timeout=2,net.ipv4.tcp_keepalived_time=600

1. MySQL level optimization

Reference Link: http://oldboy.blog.51cto.com/2561410/1726517

1. Optimization of MY.CNF parameters

If we adopt the MyISAM engine, the key_buffer_size increases. Adopt InnoDB

It is recommended to use InnoDB, 5.5.5 is InnoDB engine by default

Innodb_buffer_pool_size, adjusted to 50% of memory, single instance. Multiple Instances each 25%

Innodb_flush_log_at_trx_commit, Sync_binlog, set to 1, data can be lost (not important), can be set to 0, from the library is set to 0, the log of things, how long to brush into the hard disk

Use a separate table space. Innodb_file_per_table=1. Default Shared table file is inefficient

Innodb_log_file_size=256m don't give too much

log_query_time=1, log query, more than 1 seconds of SQL statements, recorded in the log, looking back to this log, to optimize.

Some session parameters, do not set too large, a connection will occupy the size of the parameter settings. Don't give too much

Sort_buffer_size, join_buffer_size,read_buffer_size,tmp_table_size,max_heap_table_size Such parameters are the session, The level parameter. 2m6m8m on it.

Query cache parameters to be set smaller: Query_cache_size = 64M, want to cache, front-end plus Mc,redis

1. Design optimization for libraries and tables

Character Set UTF-8

The contents of a fixed string, you can select Char

The database has to give a self-increasing homepage, no use.

The length of the field, under the premise of meeting the requirements, the shortest. Varchar (16)

Province, sex, so the content field can be set ENUM type, MySQL system table (char,enum)

As much as possible without text/blob a larger field type (blog post), if used, can be placed in the sub-table

Generally for the field index, try to use the first N character index of the field, not the whole field index, inefficient

Multi-use federated indexes, with prefix attributes, fewer independent indexes, sex columns, and not indexed. Poor effect

1. Optimization of SQL statements

Index optimization (most commonly used for operations)

1. Catch it up slow query

Baidu: White list of methods, design procedures involved in design, program online connection database, there is a control Chaku things, ask to put my library, to query, database does not or reduce slow query

Frequent development training with higher DB levels

Now the website is slow, show full processlist; Catch slow query, continuous execution two, interval 1-2 seconds, if there is, suspect he is slow query.

Daily: Log slow query statements into log

My.cof

long_query_time=2 query for more than two seconds

Log_queryies_not_using_indexes no more than two seconds without walking index

Log_slow_queries=/data/3306/slow.log

1. Generate Slow.log Daily

Cutting Slow.log by day, post-cutting analysis software analysis (mysqlsla,-pt-query-digest)

Mysqldumpslow,myprofi. Optimized statements, not necessarily a single time-consuming, frequency, a single not long, but the total time is very long, these may also be the focus of optimization.

For operations, slow query SQL is sent to development.

Have the ability and development together to engage in

Use explain to test whether the statement goes through the index, and set profile depth to view the execution of the statement.

Check Delete duplicate index, tool pt-duplicate-key-checker

Inefficient index, check Delete, Pt-index-usage tool

1. Try to use join instead of subquery

The database is where the data resides, not where the data is calculated, and the calculation is placed on the web

Search function, like "%daf%", without database search

Remove in or <> characters as much as possible in the statement

1. Site Cluster Architecture optimizes database

Reference Link: http://oldboy.blog.51cto.com/2561410/775056

1. Server run multi-instance of 2-4

   Master-slave replication of up to 9, recommended 1 main 5 from the main library using mixed mode, do not replicate across the room (if it is, remote write, local read

2. Business split:

   Search function, like "%daf%", without database search

   Search Software: SPHINX,XAPIAN,SOLR

   Fan concerns, good relations, statistics this kind of application is relatively simple, does not have the database, puts to the Redis (wants to persist haunted)

3. Database front end plus cache

   Dynamic content to static (database data, into HTML files, put on storage), the advantage is that the CDN cache can be used

   The database adopts read/write separation, reads from the library, writes the main library.

   Related software: Mycat,atlas,cobar,amoeba,mysql-proxy

   Single table more than 800W, the demolition of the table, automatic expansion, automatic contraction.

4. Backup policy

   Choose from library Backup, lock table, long backup time, impact data access

   The use of sub-tables in the backup is very laborious.

5. Process system security

   50% of all faults are caused by man-made.

   Operating Procedures: open-and-core development--operations or DBAs

   Test flow: Office Test-->IDC Test Environment test--production environment

Large Web site MySQL depth optimization Revelation 2