MySQL optimization and MySQL Optimization

MySQL optimization and MySQL Optimization

Address: http://www.cnblogs.com/verrion/p/mysql_optimised.html

MySQLOptimization

 

MySQL is not introduced much. Today, I will talk about how to optimize and what aspects to start with. Many O & M practitioners are overwhelmed when optimization is started. When a parameter value reaches a certain threshold during operation, there will be various problems. Many O & M engineers are overwhelmed at this moment. First, they may have never handled similar situations. On the other hand, the business is tight and the system is abnormal. The first task is to solve the problem, there is no way to restart it. Let's not talk about whether the restart is feasible. For example, some applications can restart and solve the problem, but what should we do if the problem is not solved or a new problem occurs, such as a bloody collapse. Therefore, we should start with the problem, first check the log, and then analyze the specific problem.

 

The Fault Handling solution mentioned above is also suitable for optimization. Based on my study and experience, let's talk about my optimization ideas: First of all, we must have a sense of identity, when any system application can reach the bottleneck, why don't we first think about the bottleneck, rather than temporarily stick to it when the problem arises. Especially for O & M, everything should be controllable, and the plan and solution measures in most cases should be prepared in advance.

 

Why optimization:

The system has reached the maximum processing capacity limit in the existing environment. one more request or connection may affect the business.

 

How to optimize:

The key issue is that hardware resources (CPU, memory, disk, and network) are under load. In this case, we are not very clear. We can find out which part overload hardware resources, and then adopt the appropriate wording to adjust the optimization process until the system has been optimized enough, the problem still persists. Only horizontal scaling and hardware resources are added.

 

According to the above, I would like to sum up that mysql (others are the same) optimization should start from the following points and explain from the system architecture layer to layer:

1,Underlying hardware layer:The most basic and important is to solve the bottleneck of the following layers of optimization;

2,Operating system layer:Selection and optimization of operating systems, file systems, and network parameters;

3,Cluster architecture layer:The business development architecture also needs to develop;

4,Data Application Layer:Mysql installation and configuration file parameter settings;

5,SQLOptimization layer:SQL statement optimization. Good SQL statement success cases can improve performance by 70%;

6,Behavior mode layer:Security, process, and system optimization;

 

As I have a limited level and have errors, please criticize and correct me.

 

I. underlying hardware layer

1Hardware resource list

Hardware resources	Purchase or purchase ECs configuration reference
CPU	64-bit, 2-16 particles, the larger the L2, the better
Memory	96/128G runs 3/4 instances; 32/64G runs 1-2 instances.
Hard Disk	Mechanical hard drive (SAS), the more the number, the better, the higher the speed, the better Comparison of single disk capacity: SAS (300 IOPS)> SSD (35000 IOPS) Performance: SSD> SAS> SATA
Disk Array	Performance: RAID0> RAID10 (recommended)> RAID5 (rarely used)> RAID1 Note:Select raid10 as the master database, and select raid0/raid5/raid10 as the slave database. The slave database configuration is equal to or greater than the master database.
Nic	At least 1 Gbit/s Nic and 1 Gbit/s 10-ge Switch Multi-nic bond settings
Note:The database is an IO-intensive service, and the hardware should avoid virtualization. Slave hardware resources must be equal to or greater than the Master

 

2Hardware configuration list(Cloud Service skip this step)

Set options	Operations
BIOS System	CPUOptimization settings(Take the DELL series server as an example ): 1. Enable the Perfirmance Per Watt Optimeized (DAPC) mode to improve the CPU computing capability. 2. Enable CIE and C States to reduce cpu scheduling algorithm time and improve efficiency. Memory Optimization settings 1. Memory Frequency (Memory Frequency) Select Maximum Performance (optimal Performance) 2. In the memory settings menu, Start Node Interleaving to avoid NUMA problems.
Disk array card	1. the array card must have a CACHE and BBU Module 2. Set the write policy to write back (two write policies are available for Cache: write-through and write-back), and disable the array pre-read policy.

 

Ii. Operating System Layer

1File System Problems

Optimization level	Operations
Operating System	No doubt x86_64 architecture is selected, (RedHat> CentOS) is based on 6.8 stable version
Data Planning	Stores operating system files, mysql applications, and data files based on physical blocks.
Swap Partition	Do not use swap space, use shared memory/dev/shm as appropriate
Software disk array	Do not use
LVM logical volume	Do not use
Note:The following settings aim to improve I/O performance:
I/O Scheduling Algorithm	We recommend that you use the deadline scheduling parameter read_expire = 1/2 write_expire Echo 500>/sys/block/sdc/queue/iosched/read_expire Echo 1000>/sys/block/sdc/queue/iosched/write_expire
Xfs File System	If the business volume is not large, you can use ext4. If the business volume is large, you are recommended to use xfs: and adjust the XFS file system logs and buffer variables.
Data directory mounting	Set the following parameters for mounting: Sync: Write data into the memory buffer before writing data to the hard disk, and synchronize the data when the hard disk is idle, which greatly improves the efficiency. The disadvantage is that if the server is down or abnormal, data not written to the disk in the buffer zone will be lost. Solution: Use the motherboard battery or UPS for uninterrupted power supply; Noatime: The inode timestamp is not updated when the access file is used. In highly concurrent environments, the system I/O performance can be improved, which is especially important for select operations; Nodiratime: Do not update the directory inode timestamp on the system to improve system I/O performance Nobarrier: RAID card battery is not recommended.

 

2And network parameters

Kernel Parameters	Operations
Swappiness	1. cat/proc/sys/vm/swappiness: The default value is 60. 2. echo "vm. swappiness = 10">/etc/sysctl. conf (generally set to 0-10) 3. sysctl-p
Ratio	Vm. dirty_background_ratio is set to 5-10. Vm. dirty_ratio is set to about two times of it, Purpose:Make sure that dirty data can be continuously refreshed to the disk to avoid instant I/O writes and serious waits.
TCP Parameters	Note: Be familiarTCP11Principle of status conversion 1. ReduceTIME_WAIT(All applications must be configured) Net. ipv4.tcp _ tw_recyle = 1 Net. ipv4.tcp _ tw_reuse = 1 2. Reduce the FIN-WAIT-2 status time Net. ipv4.tcp _ fin_timeout = 10 3. Reduce the TCP KeepAlived connection detection time Net. ipv4.tcp _ keepalived_time = 600 4. Increase the maximum number of SYN semi-connections (1024 by default ). Net. ipv4.tcp _ max_syn_backlog = 16384 5. Reduce the number of system SYN connection retries (default value: 5) Net. ipv4.tcp _ synack_retries = 1 6. Discard the number of SYN packets sent before the connection established in the kernel Net. ipv4.tcp _ sync_retries = 1 7. Permitted port range Net. ipv4.ip _ local_prot_range = 4500 65535
Network Parameters	Note: familiar with network knowledge 1. Adjust the socket buffer Net. core. rmem_max = 16777216 # maximum socket read buffer Net. core. wmem_max = 16777216 # maximum socket write buffer Net. core. wmem_default = 8388608 Net. core. rmem_default = 8388608 2. Adjust the TCP receiving/sending Buffer Net. ipv4.tcp _ rmem = 4096 87380 16777216 Net. ipv4.tcp _ wmem = 4096 65536 16777216 Net. ipv4.tcp _ mem = 94500000 915000000 927000000 3. Adjust the network device receiving queue Net. core. netdev_max_backlog = 3000
Other Optimizations	Net. ipv4.tcp _ max_orphans = 3276800 Net. ipv4.tcp _ max_tw_buckets = 360000

 

Iii. cluster architecture Layer

Architecture Design	Notes
Instance	Generally, 2-4 servers are run according to server hardware resources and service requirements.
Solution	The Mysql architecture is diverse and highly scalable. I will not detail it here For example, the mixed mode is used for master-slave replication in a master-slave multi-slave architecture. Optimized architecture with a large volume of business data: 1. read/write Splitting: Master-Write-from-read through a program or dbproxy; 2. vertical database sharding and horizontal table sharding (it is generally recommended that a single table not exceed 40 million)
Data Scanning	Periodically Use pt-table-checksum and pt-table-sync to check and fix the data difference between master-slave replication.
Cache Mechanism	Adds a cache layer to the front end of the DB layer, such as memcached/redis, to store certain services such as session, token, and friend task ranking.
Static	Dynamic Database static: for example, static entire file and static page Fields
Avoidance options	For example, SQL has a large number of fuzzy queries, and mysql databases should be avoided whenever possible.
Note:Database architecture is a great learning, and the level is limited.

 

Iv. Data Application Layer

Note:The following parameter values are optimized mainlyInnodbIf you want to use the MyISAM engine, you need to adjust the key_buffer_size value.
Parameter	Settings reference
Innodb_buffer_pool_size	About 50-70% of physical memory
Inno_flush_log_at_trx_commit	Set no data loss to 1 based on business needs.
Sync_binlog	Set no data loss to 1 based on business needs.
Innodb_file_per_table	Set to 1: use independent tablespace
Innodb_data_file_path	Ibdata1: 1G: autoextend
Innodb_log_file_size	256 M. This parameter is used with the following parameter.
Innodb_log_files_in_group	2
Innodb_log_file_size	Do not set too large. First, ensure faster logging and second, ensure shorter incremental database recovery time.
Long_query_time	Set the time for slow query SQL
Max_connection	Maximum number of connections, which can be selected based on business scenarios
Max_connection_error	Maximum number of Link errors. More than 0.1 million are recommended.
Open_files_limit	10 times max_connection
Innodb_open_files	Same as above
Table_open_cache	Same as above
Table_definition_cache	Same as above
Tmp_table_szie	Set Session Allocation Based on business
Max_heap_table_size	Same as above
Sort_buffer_size	Same as above
Join_buffer_size	Same as above
Read_buffer_size	Same as above
Read_rnd_buffer_size	Same as above
Query cache	We recommend that you disable it. You must set it to a value not greater than 512 MB.
Tip:More kernel parameters are introduced in detail. Everything comes from the official documentation. I am only a porter of tens of millions of O & M jobs.

 

V:SQLOptimization Layer

1Some Ideas about database table design,DBARequired parameter Development

Design Concept	Operations
Character Set	We recommend that you use UTF-8 (affected by Chinese characters). Latin1 is used by default, because the latter is fast
String	1. fixed strings use fixed-length char to avoid varchar 2. variable-length string varchar. Do not use char (this effect is ignored due to UTF8) If the above two can predict the business storage length, the length can be short or short
Constraints	Add not and null to the field attribute, and use the auto-incrementing column irrelevant to the table structure design as the primary key.
Special Type	1. Set text fields (remarks, Blog content, etc.) to the enum type. 2. Try not to use the text/blob type, which has a great impact on select query performance.
Random I/O	It is strictly prohibited to use the select * Statement to query specific columns.
Index	1. Multi-Purpose composite indexes (except for special independent indexes), especially when cardinality was a child For example, if the total number of unique values in this column is less than 255, do not create an independent index. 2. For the varchar type, you can create a prefix index with a length of 50% or less to meet the query requirements of about 90%. If you do not need to create a full length, the performance will be reduced.

 

2Statement Optimization

Design Concept	Operations
Preparations	In the project development stage, DBAs must participate in the design of SQL statements and find slow SQL statements. Configure my. cnf Long_query_time = 2 Log-slow-queries =/data/mysql/slow-log.log Log_queries_not_using_indexs = true
Tool usage	Slow logSQLAnalysis ToolsMySQL SLA or pt-query-digest, and many other tools Index detection tool 1. regularly use pt-duplicate-key-checker to check and delete duplicate Indexes 2. regularly use pt-index-usage to check and delete low-frequency Indexes 3. Use pt-online-schema-change to implement online ddl requirements for large tables
SQL Design	1. search services, such as like queries. MySQL is not recommended. 2. Do not use count (*). Tables may be locked. 3. Multi-table join query. The associated fields are indexed and as consistent as possible. 4. Multi-table join query. A table with a small result set is used as the driving table. 5. Use union instead of subquery as much as possible in the where clause
SQL splitting	Large and complex SQL statements are split into various functional small SQL statements. For example, join table queries, subqueries, and more than 40 million records in a single table
Bottleneck	SlowSQLSolution:

 

Vi. behavior mode Layer

Standard Specification	Operations
Start the program	File Permission 700. Both the owner and user group are mysql
Superuser	Set a complex password for the MySQL Super User root, for example, killing the root user in my generation environment, creating other management users, and the name is not of the Public type.
Logon Behavior
O & M ideas	There is a convention for O & M: There is a kind of configuration called default, so many O & M personnel do not pay attention to the role and source of this parameter.
Liability issues	Prohibit a user from managing all databases and establishing a one-to-one service relationship
Permission problems
Environment Problems
Online behavior