MySQL share one: operational dimensions on MySQL database optimization

Change to: http://lizhenliang.blog.51cto.com/7876557/1657465

1. database table Design to avoid slow query, inefficient query statements, not properly indexed, database congestion (deadlock), etc.

2. Database deployment

Project initial visits are not very large, so a single deployment is sufficient to cope with the QPS at around 1500 (query rate per second). Considering the high availability, can use MySQL master-slave replication +keepalived do double-click hot, common cluster software has keepalived, Heartbeat.

Dual-Machine Hot Standby Blog: http://lizhenliang.blog.51cto.com/7876557/1362313

3, Database performance optimization

3.1 Database Configuration optimization

The parameters that primarily affect database performance are listed below.

Default values for public parameters:

• MAX_CONNECTIONS = 151 
• #同时处理最大连接数, recommended setting the maximum number of connections is about 80% of the maximum number of connections      
• SORT_BUFFER_SIZE = 2M 
• #查询排序时缓冲区大小, only for order by and group  by function, can increase this value to 16m 
• QUERY_CACHE_LIMIT = 1M   
• #查询缓存限制, only 1 m of the following query results will be cached, so as to avoid large result data to overwrite the cache pool  
• QUERY_CACHE_SIZE = 16M   
• #查看缓冲区大小, used to cache select query results, the next time there is the same select query will return results directly from the cache pool, which can be appropriately multiplied by this value  
• OPEN_FILES_LIMIT = 1024  
• #打开文件数限制, if show  global status like  Open_ Files ' view value is equal to or greater than Open_files_limit value, the program will not be able to connect to the database or Die

MyISAM parameter Default value:

1. key_buffer_size = 16m# Index buffer size, general setting of physical memory 30-40%
2. Read_buffer_size = 128K #读操作缓冲区大小, recommended setting 16M or 32M

InnoDB parameter Default value:

• Innodb_buffer_pool_size = 128M
• #索引和数据缓冲区大小, general setup of physical memory 60%-70%
• Innodb_buffer_pool_instances = 1
• #缓冲池实例个数, recommended setting of 4 or 8
• Innodb_flush_log_at_trx_commit = 1
• #关键参数, 0 represents about every second written to the log and synchronizes to disk, and database failures can lose about 1 seconds of transactional data. 1 writes to the log after each SQL execution and synchronizes to disk, I/O overhead, SQL to wait for the log to read and write, inefficient. 2 means that only the log is written to the system buffer, and then synchronized to disk per second, the efficiency is very high, if the server fails, the transaction data will be lost. The data security requirements are not very high recommended settings 2, high performance, modified after the effect is obvious.
• innodb_file_per_table = OFF
• #默认是共享表空间, shared tablespace idbdata files are increasing, affecting certain I/O performance. It is recommended to turn on the standalone tablespace mode, where each table's index and data exist in its own table space, enabling a single table to move in different databases.

3.2 System Configuration Optimization

Most MySQL is deployed on Linux systems, so some of the operating system's parameters also affect MySQL performance, and the following are appropriate optimizations for the Linux kernel.

1. Net.ipv4.tcp_fin_timeout = 30
2. #TIME_WAIT超时时间, the default is 60s
3. Net.ipv4.tcp_tw_reuse = 1
4. #1表示开启复用, allow time_wait sockets to be re-used for new TCP connections, 0 for shutdown
5. Net.ipv4.tcp_tw_recycle = 1
6. #1表示开启TIME_WAIT socket Fast Recovery, 0 to close
7. Net.ipv4.tcp_max_tw_buckets = 4096
8. #系统保持TIME_WAIT the maximum number of sockets, if this number is exceeded, the system will randomly clear some time_wait and print a warning message
9. Net.ipv4.tcp_max_syn_backlog = 4096
10. #进入SYN队列最大长度, increase the queue length to accommodate more waiting connections

In a Linux system, if a process opens a file handle that exceeds the system default of 1024, the "Too many files open" message is prompted, so you want to adjust the open file handle limit.

1. # vi/etc/security/limits.conf #加入以下配置, * on behalf of all users, can also specify the user, restart the system to take effect
2. * Soft Nofile 65535
3. * Hoft Nofile 65535
4. # Ulimit-shn 65535 #立刻生效

3.3 Hardware configuration

Increase physical memory, in order to improve file system performance, the Linux kernel allocates buffers (System cache and file cache) from memory to hold hot data, that is, the larger the physical memory, the larger the allocation buffer, the more cache data.

SSD drives instead of SAS hard drives, with RAID levels tuned to raid1+0, with better read and write performance (IOPS) compared to RAID1 and RAID5, after all, the pressure on the database comes mainly from disk I/O.

4. Database schema Extension

With the increasing volume of business, the performance of a single database server can not meet the business requirements, the consideration of adding machines, the cluster has to do ~ ~ ~. The main idea is to decompose a single database load, break through disk I/O performance, heat data storage cache, reduce disk I/O access frequency.

4.1 Master-slave copy and read/write separation

Because the production environment, the database is mostly read operations, so the deployment of a master multi-slave architecture, the main database is responsible for write operations, and do double-click Hot Standby, multiple from the database to do load balancing, read operations, the mainstream load balancer has LVS, HAProxy, Nginx. How to realize the separation of read and write? Most enterprises are at the code level to achieve read and write separation, high efficiency. Another way through the agent to achieve the separation of read and write, the enterprise less application, the common agent has MySQL proxy, amoeba. In such a database cluster architecture, greatly increase the database high concurrency capability, solve the single performance bottleneck problem. If you can handle the QPS from a database from a library, then 5 can handle 1w QPS, and the database scale-out is easy.

Master-slave Copy blog: http://lizhenliang.blog.51cto.com/7876557/1290431

Read-Write Separation blog: http://lizhenliang.blog.51cto.com/7876557/1305083

4.2 Increasing the cache

Increase the cache system for the database, improve read performance, cache implementation has local cache and distributed cache, local cache is to cache the data in the local server memory or file, fast. Distributed cache of large amounts of data, easy to expand, the mainstream distributed cache system has memcached, redis,memcached performance stability, data cache in memory, fast, QPS up to 8w, you can cache hot data into memory, If there is data to be requested in the memory cache, the results are no longer returned to the database.

4.3 min Library

The Sub-Library is based on the business different to the related tables into different databases, such as the Web, BBS, blog and other libraries. If the volume of business is large, you can also take the post-segmentation library from the schema to further avoid a single library pressure too much.

4.4 Minute Table

VCDs increase in data volume, a table in the database has millions of data, resulting in query and insertion time is too long, how can solve the single-table pressure? You should consider whether to split the table into smaller tables to relieve the pressure on a single table and improve processing efficiency, which is called a sub-table.

Sub-table technology is troublesome, to modify the code of the SQL statements, but also manually to create other tables, you can also use the merge storage engine to implement the table, relatively simple and much more. After the table, the program is to operate on a general table, the total table does not hold data, only some of the relationship between the table, and the way to update the data, the general table according to different queries, the pressure on different small tables, so improve concurrency and disk I/O performance.

The Sub-table is divided into vertical split and horizontal split:

Vertical split: Split the original table with a number of fields into multiple tables, solving the table width problem. You can put infrequently used fields in a single table, or you can put large fields in a single table, or put a closely related field in a table.

Horizontal split: Split the original table into multiple tables, the structure of each table is the same, to solve the problem of large amount of single-table data.

4.5 Partitioning

Partitioning is a table of data into a number of chunks, which can be on a disk, or on a different disk, partition, the surface is still a table, but the data is hashed in multiple locations, so that multiple hard disks simultaneously processing different requests, thereby improving disk I/O read and write performance, the implementation is relatively simple.

5. Database Maintenance

Database maintenance is the primary work of operations engineers or DBAs, including performance monitoring, performance analysis, performance tuning, database backup and recovery, and more.

5.1 Turn on Slow query log

MySQL turn on slow query log, analyze which SQL statement is slow, use Set set variable, restart service failure, you can add parameters in my.cnf permanent.

1. Mysql> Set global slow-query-log= on#开启慢查询功能
2. Mysql> Set global slow_query_log_file='/var/log/mysql/mysql-slow.log '; #指定慢查询日志文件位置
3. Mysql> Set global log_queries_not_using_indexes= on; #记录没有使用索引的查询
4. Mysql> set global long_query_time=1; #只记录处理时间1s以上的慢查询
   

Analyze the slow query log, you can use the MySQL Mysqldumpslow tool, the analysis of the log is relatively simple.

# mysqldumpslow-t 3/var/log/mysql/mysql-slow.log #查看最慢的前三个查询

can also use Percona Company's pt-query-digest tool, log analysis function is comprehensive, can analyze slow log, Binlog, general log.

Parsing slow query log: Pt-query-digest/var/log/mysql/mysql-slow.log

Analysis Binlog log: Mysqlbinlog mysql-bin.000001 >mysql-bin.000001.sql

Pt-query-digest--type=binlog Mysql-bin.000001.sql

Analyze normal log: Pt-query-digest--type=genlog localhost.log

5.2 Database Backup

Backup database is the most basic work, but also the most important, or the consequences are very serious, you know! But because the database is larger, hundreds of G, often backup is very time-consuming, so it is necessary to choose an efficient backup strategy, for the data volume of the database, generally use incremental backup. Common backup tools are mysqldump, Mysqlhotcopy, Xtrabackup, and so on, mysqldump comparison is suitable for small databases, because it is a logical backup, so backup and recovery time is longer. Mysqlhotcopy and Xtrabackup are physical backups, fast backup and recovery, no impact on database service, hot copy, recommended use of xtrabackup, support for incremental backups. Interested to refer to the previous blog: http://lizhenliang.blog.51cto.com/7876557/1612800

5.3 Database Repair

Sometimes the MySQL server suddenly loses power and shuts down abnormally, causing the table to become corrupted and unable to read the table data. At this point, you can use the MySQL comes with two tools to repair, Myisamchk and Mysqlcheck.

Myisamchk: can only repair MyISAM table, need to stop database

Common parameters:

-F--force forced repair, overwriting old temporary files, generally not used

-R--recover Recovery mode

-Q--quik Fast Recovery

-A--analyze analysis table

-o--safe-recover old recovery mode, if-r cannot be repaired, you can use this parameter to try

-F--fast Only check for tables that are not properly closed

Quick fix Weibo database:

# Cd/var/lib/mysql/weibo

# myisamchk-r-Q *. MYI

Mysqlcheck:myisam and InnoDB tables can be used, do not need to stop the database, such as repairing a single table, you can add table name after the database, separated by a space

Common parameters:

-A--all-databases Check all libraries

-R--repair Repair table

-C--check Check table, default options

-A--analyze analysis table

-O--optimize Optimization table

-Q--quik fastest check or repair table

-F--fast Only check for tables that are not properly closed

Quick fix Weibo database:

Mysqlcheck-r-q-uroot-p123 Weibo

MySQL share one: operational dimensions on MySQL database optimization