Shulou(Shulou.com)06/01 Report--

This article is mainly about how to optimize the MySQL database in the highly concurrent website cluster scenario. If you are interested, let's take a look at this article. I believe it is of some reference value to you after reading how to optimize the MySQL database in the highly concurrent website cluster scenario.

1. Hardware level optimization

1.1 Database physical machine

A.CPU:64 bit, server 2-16 CPU,2-4, L1 and L2, the bigger the better.

B.mem (memory): 48G-96G-128G-256G (48g 2-3 instances; 96g 3-4 instances)

C.disk (disk IO) database is an IO-intensive application

Mechanical disk: SAS (do not select SATA), 300G*12 block, the more the number of disks, the higher the IO, SAS 15K hard disk.

SSD: solid state disk

Test comparison: SAS single disk random IO,300IOPS,SSD single disk random IO reached tens of thousands.

D.raid array: select hardware RAID (0 > 10 > 5 > 1), select 10

E. Network card at least gigabit (bond), 10 Gigabit switch

f. Database servers should not be virtualized as much as possible

G.SLAVE server configuration is preferably greater than or equal to Master

Case study:

Baidu: IBM server with 96-128 GB CPU 48 cores and 3-4 instances

SINA: DELL R510, 48g memory, 300mm 12 disks, raid10

Multiple instances: a server with multiple databases, compared to multiple bedrooms in a house.

1.2 hardware tuning:

Bios tuning to improve CPU performance

1) turn on DAPC mode to play CPU performance.

2) start Node Interleaving to avoid NUMA problems (NUMA=0).

3) options such as key C1E and State.

Display cards:

1) configure CACHE and BBU modules (mechanical disk)

2) set the write policy to (wb)

3) disable the display and pre-reading policy without wt policy

2. Software level optimization.

2.1 Optimization at the operating system level

1) Select x86slave 64-bit system

2) separate the system disk from the data disk

3) try to avoid using swap

4) avoid using operating system soft raid

5) avoid using LVM

6) exclusive use of the dedicated library (do not run LNMP,TOMCAT)

7) adjust Cache mode

Start wce=1 (Write Cache Enable) RCD=0 (Read Cache Disable)

8) the scheduling algorithm defaults to cfq,noop,deadline. Parameters (kernel parameters) can be adjusted for deadline

9) Centos6.8 default ext4 can be used as a data file system, if the traffic is large, XFS is even better. Centos7 also selects XFS by default, adjusts XFS logs and buffers parameters.

10) mount parameters are very important, async,noatime,nodirname,nobarrier, etc.

2.2 File system layer optimization

2.3 Kernel level optimization

1) set vm.swappiness 0, or 0-5, so that the database does not use swap as much as possible.

2) the Vm.dirty_background_ration setting is 5-10 times the front of the vm.dirtyweights setting. Continue to brush the dirty data of the system to disk.

3) net.ipv4.tcp_tw_recyle=1,net.ipv4.tcp_reuse=1, net.ipv4.tcp_fin_timeout=2,net.ipv4.tcp_keepalived_time=600 reduce time_wait

4) Kernel optimization, refer to the Old Boy blog

3. MySQL level optimization.

3.1Optimization of my.conf parameters

1) if the myisam engine is used, the key_buffer_szie will be increased. Try to use innnodb.

2) after using innodb,5.5.5, the default is innodb engine.

3) innodb_buffer_pool_szie, adjusted to 50% of memory, single instance.

4) innodb_flush_log_at_trx_commit,sync_binlog, set to 1, data can be lost, set to 0, slave library set to 0.

5) use independent tablespaces. Innodb_file_per_table=1

6) innodb_log_file_size=256M

7) log_query_time=1. Statements longer than 1 second record LOG.

8) do not set some session parameters too large, a connection will occupy the size of the parameter settings. Parameters such as Sort_buffer_size,join_buffer_size are session-level parameters.

9) the query cache parameters should be set smaller: query_cache_size=64M, if you want to cache, add mc,redis at the front end.

3.2 Design optimization of libraries and tables

1) character set UTF-8

2) fixed the contents of the string. You can choose char.

3) the database should be given a self-increasing primary key, which has no business use.

4) the length of the field, under the premise of meeting the demand, use the shortest. Varchar (16)

5) Province, gender, this kind of content field can set ENUM type, mysql system table (char, ENUM)

6) avoid using text/blob as much as possible, and if so, put it in the child table.

7) for the field index, try to use the first N characters of the field instead of the whole field index.

8) use more federated indexes, prefix features, use less independent indexes, and don't build independent indexes for gender columns.

3.3 Optimization of SQL statements

A, index optimization

1) catch the slow query

Baidu: the whitelist method, participate in the design when designing the program, the program is online to connect to the database, there is something to control the database, please put it in my library in order to query, the database does not have or reduce slow query.

Give regular training to developers, with a higher DB level.

Now that the website is slow, show full processlist; slows down the query and executes it twice in a row. The interval is 1-2 seconds, if there is any, it is suspected that he is a slow query.

Daily: record slow query statements to log.

My.cnf

Long_query_time=2

Log_queryies_not_using_indexes

Log-slow-queries=/data/3306/slow.log

Generate slow.log every day, cut slow.log daily, and analyze it with analysis software (mysqlsla,-pt-query-digest) after cutting.

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

As far as operation and maintenance is concerned, the slow query SQL is sent to the developer.

Explain tests whether the statement walks through the index. Set profile takes an in-depth look at statement execution.

Check to delete duplicate indexes, pt-duplicate-key-checker, inefficient index check deletions, pt-index-usage

2) try not to use subqueries and use join instead

The database is the place where the data is stored, not where the data is calculated, and the calculation is put in front of the Web. Search function, like "% daf%", no database search.

3) remove in or from the statement as much as possible

4. Optimize the database on the website cluster architecture.

1) the server runs multiple instances 2-4

2) there are up to 9 master-slave replications, 1 master and 5 slaves, using mixed, and do not replicate across computer rooms (remote write, local read)

3) Business split: search function, like "% daf%", no database search.

Search software: Sphinx,Xapian,Solr

4) fans follow, good relationship, statistics this kind of application is relatively simple, do not need data, put to redis.

5) add caching to the front end of the database

6) dynamic internal conversion static (database data, converted to html file, put on storage) using CDN.

7) the database adopts read-write separation and MyCat,atlas,cobar,amoeba,MySQL-proxy

8) if a single table exceeds 8 million, dismantle the library and disassemble the table. Automatic expansion, automatic contraction.

9) choose to back up from the library, lock the table, the backup time is very long, affecting data access.

10) separate tables and databases are used when backing up.

5. Process, system, safety: 50% of failures are man-made.

1) Operation process: development-core development-operation and maintenance or DBA

2) testing process: office testing-IDC testing-IDC formal

Does the above details on how to optimize the MySQL database in the highly concurrent website cluster scenario be helpful to you? If you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.