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.
1. The philosophy of optimization
Note: Optimization is risky, so be cautious
A Possible problems with 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!
B optimization requirements?
- 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!
Who is involved in c 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.
2. Optimize your thinking
What is a optimized for?
There are two main aspects of database optimization: security and performance.
- Security -> Data sustainability;
- Performance -> High performance access to data.
B 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);
- The instance configuration
Note: Whether designing the system, locating the problem, or optimizing, you can follow this order.
C optimization 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 result: hardware < system configuration < database table structure
1. What are the optimization tools?
A 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)
B How to solve 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.
C 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.
D 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.
4. Basic optimization
A Optimization idea?
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.
B 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.
C 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.
D 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 deadline for permanent modifying vi/boot/grub/grub. Conf change to the following contents: the kernel /boot/vmlinuz-2.6.18-8.el5 ro root=LABEL=/ elevator=deadline RHGB quiet
E System parameter adjustment?
Linux kernel parameter optimization:
Vim/etc/sysctl. Confnet. Ipv4. Ip_local_port_range = 1024, 65535: Tcp_max_syn_backlog = 4096 net.ipv4.tcp_fin_timeout = 30 fs.file-max=65535: # Maximum file handle for the system, 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
F 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.
5. Database optimization
SQL optimization direction: execution plan, index, SQL rewrite.
Architecture optimization direction: high availability architecture, high performance architecture, sub-library and sub-table.
A Database parameter optimization?
Adjust the
Example Whole (Advanced Optimization, extension) :
Thread_concurrency: # Number of concurrent threads sort_buffer_size: # sort cache read_buffer_size: # Sequential read cache read_rnd_buffer_size: Thread_cache_size: # (1G — >8, 2G — >16, 3G — >32, >3G — >64)
Connection Layer (Base optimization)
Set reasonable connection customers and connection methods:
Max_connect_errors max_connect_errors max_connect_errors Connect_timeout # max_user_connections # Maximum number of user connections skip-name-resolve # Skip domain name resolution wait_timeout # back_log # Number of connections that can be 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.
B Storage engine layer (InnoDB basic optimization parameters)?
Default-storage-engineinnodb_buffer_pool_size # No fixed size, 50% test value, fine tuning to see what happens. 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 compromise binlog_syncInnodb_flush_method = (O_DIRECT, Fdatasync)innodb_log_buffer_size # 100M innodb_log_file_size # 100M innodb_log_files_in_group # Innodb_max_dirty_pages_pct # flush memory dirty pages to disk when up to 75% of the time. Max_binlog_size # Innodb_additional_mem_pool_size # Innodb_additional_mem_pool_size # For machines with less than 2 gb memory, 20M is recommended. 32 GB memory more than 100 MB