Deep optimization Lnmp MySQL

MySQL Database optimization Framework System

1. Hardware-Level optimization

2. Operating system level optimization

3.MySQL Database Level optimization

4.MySQL Security Optimization

5. Optimization on the site cluster architecture

6.MySQL process, System control optimization

1

Hardware-Level optimization

1, Database Physical machine procurement

CPU:64-bit CPU, 2-16 CPUs for a single machine. At least 2-4, L2 (cache) The bigger the better

Memory: 3-4 instances of 96-128g,mysql. 32-64g,1-2 instances

HDD: Machinery: Choose SAS, the more the better, the higher the speed, the better 15k

Performance: SSD (high concurrency) > SAS (On-line) >sata (offline)

SSD: Use SSD or PCIe SSD devices to increase your IOPS efficiency by up to thousand.

Random Io:sas Single-disk capability 300IOPS SSD random io: Up to 35000IOPS Flashcache HBA card with single-disk capability

RAID array: 4 fast disk: RAID0>RAID1 (recommended) >RAID5 (less) >raid1

Main Library Select RAID10, from library optional RAID5/RAID0/RAID10, from library configuration equal to or greater than main library

NIC: Using multiple NIC bond, and BUFFER,TCP optimization

Gigabit network adapters and gigabit, million gigabit switches

Tips:

The database is an IO-intensive service, and the hardware tries not to use virtualization.

Slave hardware to be equal to or greater than Master's performance

2, Enterprise case:

Baidu: A Department of IBM server for 48 core CPU, memory 96GB, a server running four or three instances:

Sina: Server is Dell R510 majority, CPU is E5210,48GB memory, HDD 12*300g SAS, do RAID10

3. Server Hardware configuration Adjustment

(1) Server BIOS tuning:

Improve CPU Efficiency Reference settings:

A. Open Perfirmance Per Watt optimeized (DAPC) mode to maximize CPU performance, and the database typically requires high computational capacity

B. Open the CIE and C states options to improve CPU efficiency

C. Memory Frequency (RAM frequency) Select Maximum performance (best performance)

D. In the Memory Settings menu, start node interleaving to avoid NUMA issues

(2) Array card adjustment:

A. Acquisition of the array card with both the cache and the Bbu module (mechanical disk)

B. Set the array write policy to Web, even Ofrce WB (High Data security requirements) (WB refers to the write policy of the raid card: write back)

C. Use of the WT policy is strictly prohibited, and the array pre-read policy is closed

2

OS level optimization

1. Operating system and MySQL instance selection

1. Be sure to choose x86_64 System, we recommend using CentOS6.8 Linux, turn off NUMA features

2. Separate the operating system and data, not only logically, but also physically

3. Avoid swapping partitions with swap

4. Avoid using software disk arrays

5. Avoid using LVM logical volumes

6. Remove unused installation packages and daemons on the server

2. File System Layer Optimization

(1) Adjust disk cache mode

1. Enable wce=1 (Write cache enable), Rcd=0 (Read cache Disable) mode

2. Command: Sdparm-s wce=1,rcd=0- s /dev/sdb

(2) using Linux I/O scheduler algorithm deadline
deadlineScheduling parameters
For CentOS Linux Recommendationsread_expire = 1/2 write_expire

1. echo > /sys/block/sdb/queue/iosched/Read_ Expire

2. echo > /sys/block/sdb/queue/iosched/ write_expire

Linux I/O scheduling method Linux deadline IO scheduling algorithm

(3) using XFS file system
Business volume is not very large and can be used ext4 , the volume of business is very large, recommended xfs : Tuning XFS File system 日志 and 缓冲 variables
XFS High Performance settings

(4) Mount Mount file system
Increase: async,noatime,nodiratime,nobarrier etc

noatime

When accessing files without updating the inode, the 时间戳 push line display applies this option in high concurrency environment to improve system I/O performance

async

Write data will be written to the 内存 buffer, only to the hard disk to write to the disk, so you can improve the write 效率 ! The risk is that if the server goes down or is unhealthy, data that is not written to the disk in the buffer is lost. Workaround: Server board 电池 or UPS uninterruptible power supply

nodiratime

Does not update the Inode timestamp on the system, directory high Concurrency environment, the recommended display of the application, can improve system I/O performance

nobarrier

Do not use raid the card battery

(5) Linux Kernel parameter optimization
1. swappiness set 0-10 the VM to
2. Set the VM to, set the dirty_background_ratio 5-10 VM to dirty_ratio its 两倍 left and right, to ensure that dirty data can be continuously flushed to 磁盘 avoid instant I/O write, resulting in severe等待

3. Optimizing the TCP protocol stack

2. #减少TIME_WAIT to improve TCP efficiency

4. NET. IPv4. Tcp_tw_recyle=1

6. NET. IPv4. Tcp_tw_reuse=1

8. #减少处于FIN-wait-2 The connection status so that the system can process more connection net. IPv4. Tcp_fin_timeout=2

10. #减少TCP keepalived Connection detection time, so that the system can handle more connections.

12. NET. IPv4. Tcp_keepalived_time=

14. #提高系统支持的最大SYN半连接数 (default 1024)

16. NET. IPv4. Tcp_max_syn_backlog = 16384

18. #减少系统SYN连接重试次数 (default 5)

20. NET,IPv4. Tcp_synack_retries = 1

22. NET. IPv4. Tcp_sync_retries = 1

24. #在内核放弃建立的连接之前发送SYN包的数量

26. NET. IPv4. Ip_local_prot_range = 4500 65535

28. #允许系统打开的端口范围

4. Network optimization
#优化系统套接字缓冲区

1. #Increase TCP Max buffer size

2. NET. Core. Rmem_max=16777216 #最大socket读buffer

3. NET. Core. Wmem_max=16777216 #最大socket写buffer

4. NET. Core. Wmem_default = 8388608 #该文件指定了接收套接字缓冲区大小的缺省

5. Value (in bytes)

6. NET. Core. Rmem_default = 8388608

#优化TCP接收/Send Buffers

1. # increase Linux autotuning TCP buffer limits

2. NET. IPv4. Tcp_rmem=4096 87380 16777216

3. NET. IPv4. Tcp_wmem=4096 65536 16777216

4. NET. IPv4. Tcp_mem = 94500000 915000000 927000000

#优化网络设备接收队列

1. NET. Core. Netdev_max_backlog=

5. Other Optimizations

1. NET. IPv4. Tcp_timestamps = 0

2. NET. IPv4. Tcp_max_orphans = 3276800

3. NET. IPv4. Tcp_max_tw_buckets = 360000

Tip: When interviewing, say the frame, then say one or two small optimization parameters can be
More cores can refer to "the third chapter of student movement with old boys" and our blog, which will be updated recently

3

MySQL Database level optimization

MY.CNF parameter optimization

This optimization is primarily for the InnoDB engine

2. If the MyISAM engine is used, the key_buffer_size need to be enlarged.

4. It is highly recommended to use the InnoDB engine,default-storage-engine=InnoDB

6. Adjust the innodb_buffer_pool_size size, consider setting the physical memory toabout %-

8. Set The value of Inno_flush_log_at_trx_commit, Sync_binlog according to the actual need .

10. If you need data that cannot be lost, then two is set to 1. If you allow the loss of larger data,

12. Can be set to 2 and 0 respectively, can be set to 0 on slave

14. Set innodb_file_per_table = 1, using a stand-alone table space

16. Set Innodb_data_file_path = ibdata1:1G:autoextend, do not use the default of ten

18. Set innodb_log_file_size=256M, set innodb_log_files_in_group=2, basic can be

20. Meet the above the scene;

22. Do not set the innodb_log_file_size parameter too large, which allows for faster and more disk space

24. Losing more logs is usually good, and you can reduce the number of events that restore the database after a database crash

26. Set long_query_time = 1 record those that perform slower SQLfor subsequent analysis and troubleshooting;

28. According to the actual needs of the business, adjust the max_connection(maximum number of connections max_connection_error

30. (Maximum number of errors, recommended to be set to more than ten, and open_files_limit,innodb_open_files,

32. Table_open_cache,table_definition_cache These parameters can be set to about ten times the

34. the size of the max_connection;) do not set too large, will make the database explode

36. Tmp_table_szie,max_heap_table_size,sort_buffer_size,

38. Join_buffer_size,read_buffer_size,read_rnd_buffer_size and so on are all

40. Each connection session is assigned, and therefore cannot be set too large

42. It is recommended to turn off the query cache feature or reduce the setting to not exceed 512M

More kernel parameters: MY-INNODB-HEAVY-4G.CNF configuration file Parameter introduction

MySQL tool mysqlreport We can use tools to analyze MySQL the performance
How to achieve high concurrent access to the site? Discussion on preventing DDoS attacks in the production environment
Interview may ask Doos attack protection

Design Specifications for Library tables

2. Recommended UTF-8 character Set, although some people talk about no latin1 fast

4. Fixed string columns as many as possible with fixed-length char, with less varchar

6. Storing variable-length strings uses VARCHAR instead of Cahr---Save space because the fixed

8. The length of CHAR, while the VARCHAR length is not fixed (UTF8 does not worry about this effect)

10. All InnoDB tables are designed for a non-business use of the auto-add-on key

12. As long as the field length satisfies the requirement, choose a small length

14. Field properties try to add not NULL constraints

16. For some text fields, such as "province" or "gender," we can define them as ENUM type as far as possible without using the text/BLOB type, if necessary, it is recommended to split into a child table, not with the main table

18. Put together to avoid reading performance when the SELECT * is poor.

20. When reading data, select only the columns you want, not select * Every time to avoid serious random reads

22. Problems, especially if you read some TEXT/BLOB types, it is recommended that you split them into child tables, and do not

24. Keep the main table together and avoid the poor read performance when SELECT *

26. When you create an index on a VARCHAR (N) column, you usually create a prefix with a length ofabout (or even less)

28. The index is sufficient to meet the requirements of the query, and it is not necessary to create a full-length index of the whole column.

30. Multi-use compliance index, less use of multiple independent indexes, especially some foundations (cardinality) are too small

32. (If the column has a total of less than 255 of unique values), do not create a stand-alone index.

4

Optimization of SQL statements

Index optimization
1) white list mechanism 100 degrees, Project 开发 Ah, DBA participate, reduce slow data on-line SQL
Catch out slow SQL , configuremy.cnf

1. Long_query_time = 2

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

3. Log_queries_not_using_indexs

4. Poll by day:slow-log. Log

2) Log analysis tool for slow queries-- mysqlsla or pt-query-digest (recommended)

1. PT-quey-diges,mysqldumpslow,mysqlsla,myprofi,MySQL- explain-slow-log, Mysqllogfileter

3) every night 0 o'clock timing Analysis slow query, sent to the 核心 development, DBA分析 and advanced operations, the CTO's mailbox
DBA analysis gives optimization recommendations--Core development Confirmation update-->DBA on-line Operations processing
4) Regular use pt-duplicate-key-checker of check and delete duplicate index
Regularly use pt-index-usage tools to check and remove low-frequency indexes
5) Use pt-online-schema-change to complete the online DDL requirements for large tables
6) Sometimes MySQL uses the wrong index, which is used USE INDEX for this case
7) using explain and set profile optimizing SQL statements

Large, complex SQL statement split into multiple small SQL statements
Sub-query, join table query, 40 million records in a table
The database is where the data is stored, but not where the data is calculated
For data calculation, application class processing, all to get the front-end application to solve. Prohibit processing on the database
Search function, like '%oldboy% ' generally do not use MySQL database
Use connection (join) instead of subquery (sub_queries)
Avoid using cout (*) on the entire table, it may lock the entire table
When you join a query with multiple tables, the associated field types are as consistent as possible and are indexed.
Using union in a WHERE clause instead of a subquery
When a multi-table connection is queried, the table with the result set is small (note that this refers to the filtered result set, not the same as the full table data volume) as the driver table

The process of retrieving data from crawlers

5

Optimization on the site cluster architecture

1. Server running multiple instances, 2-4 (specifically need to look at the server hardware information)

2. master-slave copy of a main five from, using mixed mode, try not to cross the computer room synchronization (process remote reader to write)

3. Regular use of pt-table-checksum, pt-table-sync to check and repair MySQL master-slave replication data differences

4. Business Split: Search function, like '%oldboy% ' generally do not use MySQL database

5. Business splitting: Some business applications use NoSQL persistent storage, for example: memcached, Redis, Ttserver

such as fan concerns, friend relationships, etc.

6. The database front-end must be added cache, for example: memcached, user login, commodity query

7. Dynamic database static, entire file static, page fragment static

8. Database cluster and read/write separation. A master multi-slave, through the program or dbproxy the separation of the cluster read and write

9. Single table more than 8 million, split the library to split the table. Manual demolition of a table (login, goods, order)

10. Baidu, Ali domestic first three companies, will choose to backup from the library, the database for the sub-list

6

MySQL process, System control optimization

Any update to the database record, you have to go through a process:

A. Human process: development--core development--operations or DBAs

B. Test Flow: Intranet Test-->IDC test and online execution

C. Client management, phpMyAdmin

MySQL Basic security

1. Startup program 700, owner and user group for MySQL

2. Set the password for MySQL superuser root

3. If you require strict deletion of the root user, create other administrative users, such as admin

4. Try not to expose the password at the command line when logging in, if there is a password in the backup script, give the setting 700,

The owner and password group are MySQL or root

5. Delete the test library that exists by default

6. Initial deletion of useless users, only reserved

| Root | 127.0.0.1 |

| Root | localhost |

7. Do not have a user to manage all the libraries, as far as possible special library

8. Clean up MySQL operation log file ~/.mysql_history (permission 600, can not be deleted)

9. Prohibit the development of the password to obtain a Web connection, prohibit the development of connection operations production of external libraries

10.phpmyadmin Security

11. The server disables the setting of the external network IP

12. Anti-SQL injection (web) php.ini or Web development plug-in monitoring, WAF control

Article turned from: https://www.abcdocker.com/abcdocker/952

Deep optimization Lnmp MySQL