Before implementing MySQL optimizations, it is important to understand the MySQL query process. A lot of query optimizations actually follow some principles to make the MySQL optimizer work as expected.
Figure -MySQL query process
Note: Optimization is risky, so be cautious
1. Possible problems caused by optimization
-
Optimization is not always for a single environment, but is likely to be a complex system in production;
-
Optimization is inherently risky, but you just don’t have the ability to recognize and anticipate it;
-
Any technology can solve a problem, but inevitably there is a risk of creating a problem;
-
For optimization to solve the problem caused by the problem, control in an acceptable range is fruitful;
-
Status quo or worse is failure!
2. Optimization needs
-
Stability and business sustainability are often more important than performance;
-
Optimization inevitably involves change, and change is risky;
-
Optimization makes performance better, and maintenance and deterioration are equally likely events.
-
Remember optimization, should be all departments together, work together, any single department can not optimize the database!
So optimization is driven by business needs!
3. Who is involved in optimization
During database optimization, database administrators, service department representatives, application architects, application designers, application developers, hardware and system administrators, storage administrators, and other service related personnel participate in the optimization.
1. Optimize what
There are two main aspects of database optimization: security and performance.
-
Security -> Data sustainability;
-
Performance -> High performance access to data.
2. What is the scope of optimization
Storage, host, and operating system:
-
Host architecture stability;
-
I/O planning and configuration;
-
Swap Swap partition.
-
OS kernel parameters and network faults.
Application:
-
Application stability;
-
SQL statement performance;
-
Serial access to resources;
-
Poor performance session management;
-
Does this application work with MySQL?
Database optimization:
-
Memory;
-
Database structure (physical & logical);
-
Instance configuration;
Note: Whether designing the system, locating the problem, or optimizing, you can follow this order.
3. Optimize dimensions
There are four dimensions of database optimization:
Hardware, system configuration, database table structure, SQL and indexes.
Optimization selection:
Optimization cost: Hardware > System configuration > database table structure >SQL and index;
Optimization effect: hardware < system configuration < database table structure < SQL and index; < span>
1. Database level
Common tools for troubleshooting:
1) MySQL
2) MsyQladmin: MySQL client, which can perform management operations
3) mysqlshow: powerful view shell command
4) show [SESSION | GLOBAL] variables: view the database parameter information
5) SHOW [SESSION | GLOBAL] STATUS: view the STATUS of the database information
6) Information_schema: A method for obtaining metadata
7) SHOW ENGINE INNODB STATUS: SHOW ENGINE INNODB STATUS
8) SHOW PROCESSLIST: displays the current session status of all connections
9) explain: Get the execution plan of the query statement
10) show index: query the index of a table
11) slow-log: records slow query statements
12) mysqlDumpslow: Analyzes slowlog files
Uncommon but useful tools:
1) Zabbix: monitor hosts, systems and databases (deploy Zabbix monitoring platform)
2) Pt-query-digest: analyzes slow logs
MySQL SLAP: Analyze slow log
4) Sysbench: Stress test tool
5) MySQL profiling: a database profiling tool
6) Performance Schema: MySQL Performance status statistics
7) Workbench: Management, backup, monitoring, analysis, optimization tools (compare cost resources)
Zabbix Reference:
http://www.cnblogs.com/clsn/p/7885990.html
2. Solutions to problems at the database level
The general emergency tuning idea is as follows: In case of a sudden service processing delay, normal service processing cannot be performed immediately.
1) show processList;
2) explain select id,name from STu where name=’ CLSN ‘; # ALL id name age sex;
Select id,name from stu where id=2; Show index from table;
3) Through the execution plan to determine, index problems (whether there is, reasonable) or statement itself problems;
4) show status like ‘%lock%’; Query lock status
kill SESSION_ID; Kill the session in question.
General tuning idea: For periodic service congestion, for example, at 10-11 o ‘clock every day, the service is very slow, but can still be used, after this period of time is good.
1) Check the slowlog, analyze the slowlog, and find out the slowstatements;
2) Check all slow statements one by one according to a certain priority;
3) Analyze top SQL, debug explain, and check statement execution time;
4) Adjust the index or statement itself.
3. System level
Cpu:
Vmstat, SAR TOP, HTOP, nmon, mpstat;
Memory:
Free, ps – aux;
IO device (disk, network) :
Iostat, SS, Netstat, IPTRAF, IFTOP, lsOF;
The vmstat command is described as follows:
1) Procs: r shows how many processes are waiting on CPU time. B Displays the number of processes that are in uninterruptible sleep. Waiting for I/O.
2) Memory: SWPD displays the number of data blocks that have been swapped to disk. Unused data blocks, user-buffered data blocks, the number of data blocks used by the operating system.
3) Swap: the number of data blocks that the operating system swaps from disk to memory and from memory to disk per second. S1 and s0 better be 0.
Io: the number of data blocks written to device B0 that read B1 from the device per second. Indicates disk I/O.
5) System: shows the number of interrupts per second (in) and the number of context exchanges (CS).
6) Cpu: displays the Cpu time used to run user code, system code, idle, waiting FOR I/O.
Description of the iostat command:
Example: iostat -dk 1 5
Iostat -d -k -x 5 (View device usage (%util) and response time (await))
1) TPS: the number of transmissions per second of the device. “One transfer” means “one I/O request”. Multiple logical requests may be combined into a single I/O request.
2) IOPS: the maximum I/O count per second defined by the manufacturer before delivery
3) The size of the “one transfer” request is unknown.
4) kB_read/s: drive expressed data per second;
5) KB_wrtn/s: drive expressed data per second;
6) kB_read: the total amount of data read;
7) kB_wrtn: the total amount of data written; These are called Kilobytes.
4. Solutions to system-level problems
Do you think a high load or a low load is better? In actual production, it is generally considered that as long as the Cpu does not exceed 90%, there is no problem.
Of course, the following special cases are not excluded:
High Cpu load, low I/O load:
1) Insufficient memory;
2) Poor disk performance;
3) SQL problems –> go to the database layer, further check SQL problems;
4) IO problems (disk critical, bad RAID design, RAID degradation, locking, high TPS per unit time);
5) High TPS: a large number of SMALL data IO, a large number of full table scans.
High I/O load and low Cpu load:
1) Lots of small IO writes:
Autocommit, which generates a lot of small IO; IO/PS: specifies the maximum number of I/OS per second defined by the manufacturer when the hardware is delivered.
2) A large number of LARGE IO write operations: THE probability of SQL problems is relatively large
IO and CPU loads are high:
Insufficient hardware or SQL problems.
1. Optimize your thinking
Locate the problem point sucking: Hardware > System > Application > Database > Architecture (high availability, read/write separation, sub-library sub-table).
Processing direction: clear optimization objectives, performance and safety compromise, prevention.
2. Hardware optimization
Host:
Select the host CPU, memory capacity, and disk based on the database type.
1) Balance memory and disk resources;
2) Random and sequential I/O;
3) The Battery Backup Unit (BBU) of the RAID controller card on the host is disabled.
CPU selection:
Two key factors of CPU: core number, dominant frequency
Select according to different business types:
1) CPU intensive: more computation, more OLTP – high frequency CPU, core number
2) IO intensive: query comparison, OLAP – core number is more, the main frequency is not necessarily high
Memory selection:
OLAP type database, requires more memory, depending on the magnitude of the data fetch.
Oltp-type data typically has 2 to 4 times as much memory as Cpu cores, with no best practices.
Storage:
1) Select different storage devices based on the type of data to be stored.
2) An appropriate RAID level (RAID5, RAID10, and hot spare disk) is configured.
3) For the operating system, do not need too special choice, it is best to make redundant (RAID1) (SSD, SAS, SATA).
4) RAID card:
Host RAID card selection:
Redundant operating system disks (RAID1);
Balance memory and disk resources;
Random and sequential I/ OS;
The Battery Backup Unit (BBU) of the RAID controller card on the host must be disabled.
Network equipment:
Network devices (switches, routers, network cables, network adapters, and Hbas) that support higher traffic
Note: These plans should have been considered in the initial design of the system.
3. Server hardware optimization
1) Physical status lamp
2) Its own management equipment: remote control card (IPMI ILO IDarc), switching machine, hardware monitoring.
3) Third-party monitoring software and devices (SNMP and Agent) monitor physical facilities.
4) Storage device: built-in monitoring platform. EMC2 (acquired by HP), Hitachi (HDS), IBM low-end OEM HDS, high-end storage is their own technology, Huawei storage.
4. System optimization
Cpu:
Basically no tuning is required, just work on your hardware choices.
Memory:
Basically no tuning is required, just work on your hardware choices.
SWAP:
MySQL tries to avoid using swap.
The default swap value of aliyun server is 0.
IO:
Raid, NO LVM, EXT4 or XFS, SSD, and I/O scheduling policies.
Swap adjustment (without Swap partition)
Add vm.swappiness=0 to /etc/sysctl. conf (permanent)
This parameter determines whether Linux prefers swap or frees the file system cache. When memory is tight, lower values tend to free the file system cache.
Of course, this parameter only reduces the probability of using swap and does not prevent Linux from using swap.
Innodb_flush_ method = O_DIRECT
In this case, InnoDB’s buffer pool directly bypasses the file system cache to access disks, but the redo log still uses the file system cache.
Note that the Redo log is in overwrite mode and does not consume much of the file system cache.
I/O scheduling policies:
# echo deadline > / sys/block/sda/queue/scheduler temporary modification for the deadline
A permanent change
vi /boot/grub/grub.conf
Change to the following:
Kernel /boot/vmlinuz-2.6.18-8.el5 ro root=LABEL=/ elevator=deadline RHGB quiet
5. Adjust system parameters
Linux kernel parameter optimization:
vim/etc/sysctl.conf
Net.ipv4. ip_local_port_range = 1024 65535: # Range of user ports
net.ipv4.tcp_max_syn_backlog = 4096
net.ipv4.tcp_fin_timeout = 30
Fs. file-max=65535: # System maximum file handle, which controls the maximum number of files that can be opened
User limit parameter (MySQL can not set the following configuration) :
vim/etc/security/limits.conf
* soft nproc 65535
* hard nproc 65535
* soft nofile 65535
* hard nofile 65535
6. Application optimization
Independent of service applications and database applications;
Firewall: Iptables, Selinux and other useless services (disabled) :
chkconfig –level 23456 acpid off
chkconfig –level 23456 anacron off
chkconfig –level 23456 autofs off
chkconfig –level 23456 avahi-daemon off
chkconfig –level 23456 bluetooth off
chkconfig –level 23456 cups off
chkconfig –level 23456 firstboot off
chkconfig –level 23456 haldaemon off
chkconfig –level 23456 hplip off
chkconfig –level 23456 ip6tables off
chkconfig –level 23456 iptables off
chkconfig –level 23456 isdn off
chkconfig –level 23456 pcscd off
chkconfig –level 23456 sendmail off
chkconfig –level 23456 yum-updatesd off
Do not start GUI runlevel 3 on the server where GUI is installed.
Also, think about whether we really need MySQL for our business in the future, or use some other kind of database. The best way to use a database is to do without it.
SQL optimization direction: execution plan, index, SQL rewrite.
Architecture optimization direction: high availability architecture, high performance architecture, sub-library and sub-table.
1. Database parameter optimization
Adjust the
Example Whole (Advanced Optimization, extension) :
Thread_concurrency: # Number of concurrent threads
Sort_buffer_size: # sort cache
Read_buffer_size: # Read the cache sequentially
Read_rnd_buffer_size: # Read cache randomly
Key_buffer_size: # Index cache
Thread_cache_size: # (1 g – > 8, 16, 2 g – > – > 32, 3 g > 3 g – > 64).
Connection Layer (Base optimization)
Set reasonable connection customers and connection methods:
Max_connections # Maximum number of connections, depending on transaction count Settings
Max_connect_errors # Maximum number of connection errors
Connect_timeout # The connection times out
Max_user_connections # Maximum number of user connections
Skip -name-resolve # skip domain name resolution
Wait_timeout # Wait timeout
Back_log # Number of connections that can be made in the stack
SQL Layer (Basic Optimization)
Query_cache_size: query cache >>> OLAP type database. It is important to increase this memory cache, but usually does not exceed GB.
For frequently modified data, the cache is immediately invalidated.
We can use memory database (redis, memecache), replace his function.
2. Storage Engine layer (InnoDB basic optimization parameters)
default-storage-engine
Innodb_buffer_pool_size # no fixed size, 50% test value, fine tuning to see what happens. But try not to exceed 70% of physical memory
Innodb_file_per_table = (1, 0)
Innodb_flush_log_at_trx_commit =(0,1,2) # 1 is the safest, 0 is the highest performance,2 is the middle ground
binlog_sync
Innodb_flush_method=(O_DIRECT, fdatasync)
Innodb_log_buffer_size below 100M
Innodb_log_file_size # 100M or less
Innodb_log_files_in_group # 5 innodb_log_files_in_group # 5 innodb_log_files_in_group
Innodb_max_dirty_pages_pct # reaches 75 percent of the time when dirty pages in memory are flushed to disk.
log_bin
Max_binlog_cache_size # Optional
Max_binlog_size # may not be set
Innodb_additional_mem_pool_size # For machines with less than 2 gb memory, 20M is recommended. 32 GB memory more than 100 MB