Author: Li Zhenliang

Original: http://blog.51cto.com/lizhenliang/2095526

Note: migrant brother made some modifications

A mature database architecture is not designed with high availability, high scalability and other characteristics, it is with the increase of the number of users, the infrastructure gradually improved. This article mainly talks about the problems and optimization solutions faced by MySQL database in the development cycle. Aside from front-end application, it can be roughly divided into the following five stages:

Stage 1: Database table design

After the project is approved, the development department develops the project according to the demand of the product department.

The development engineer will design the table structure at the beginning of the development project. For the database, the table structure design is very important, if the design is improper, will directly affect the user to visit the website speed, user experience is not good! There are many reasons for this, such as slow queries (inefficient query statements), improper indexing, database congestion (locks), and so on. Of course, there are teams in the testing department that do product testing and find bugs.

As the development engineers pay attention to different points, the initial stage does not consider too much database design is reasonable, but as soon as possible to complete the function implementation and delivery. Such as the project online after a certain amount of traffic, hidden problems will be exposed, then to modify it is not so easy!

Phase 2: Database deployment

It’s time for the operations engineers to show up and bring the project online.

At the initial stage of the project, there were generally few visits. At this stage, the single deployment of Web+ database was enough to cope with the QPS (query rate per second) of around 1000. Considering the single point of failure, high availability should be achieved. MySQL master-slave replication +Keepalived can be used to achieve dual-system hot backup. Mainstream HA software include Keepalived (recommended), Heartbeat.

Phase 3: Database performance optimization

If MySQL is deployed on a common X86 server, the theoretical value of MySQL can normally handle around 1500 QPS without any optimization. After optimization, it may be increased to around 2000 QPS. Otherwise, when the traffic reaches about 1500 concurrent connections, the database processing performance may respond slowly, and the hardware resources are relatively rich, then it is time to consider the performance optimization problem. So how do you get the most out of your database? Mainly from the hardware configuration, database configuration, architecture, specifically divided into the following:

3.1 Hardware Configuration

If possible, replace SAS mechanical hard disks with SSDS and set the RAID level to RAID1+0. Compared with RAID1 and RAID5, SSDS provide better read/write performance. After all, the pressure on databases is mainly from disk I/O.

The Linux kernel has a feature that divides the cache (system cache and data cache) from the physical memory to store hot data. The data is written to the disk through the file system delay mechanism until conditions (such as the cache size reaches a certain percentage or the sync command is executed) are met. That is, the larger the physical memory, the larger the cache allocated, the more data cached. Of course, some cached data can be lost due to server failures. It is recommended that the physical memory be at least 50% richer.

3.2 Database Configuration Optimization

MySQL is most widely used in two storage engines: MyISAM, which does not support transaction processing, fast read performance, and table-level locking. The other is InnoDB, which supports transaction processing (ACID properties) and is designed for big data processing with row-level locking.

Table locks: low overhead, large lock granularity, high deadlock probability, and low relative concurrency. Row lock: high overhead, small lock granularity, low probability of deadlock, and high relative concurrency.

Why table and row locks? The main purpose is to ensure data integrity. For example, if a user is working on a table and other users want to work on the table, they must wait until the first user has finished working on the table. Table and row locks are used for this purpose. Otherwise, data conflicts or exceptions may occur when multiple users operate a table simultaneously.

For these reasons, using the InnoDB storage engine is the best choice and is the default storage engine for MySQL5.5+. Each storage engine has many running parameters. The following table lists the parameters that may affect database performance.

Default values for public parameters:

Sort_buffer_size = 2M sort_buffer_size = 2M order by and group by 16Mopen_files_limit = 1024 # Show global status like 'open_files' The program may fail to connect to the database or freezeCopy the code

MyISAM Parameter Default value:

Read_buffer_size = 128K # Buffer size for read operations Query_cache_limit = 1M query_cache_limit = 1M query_cache_limit = 1M Query_cache_size = 16M # Query the size of the buffer used to cache the results of the SELECT query. The next time the same SELECT query returns the results directly from the cache, multiply this value appropriatelyCopy the code

InnoDB parameter default value:

Innodb_buffer_pool_instances = 1 # Number of buffer pool instances, Innodb_flush_log_at_trx_commit = 4 or 8 innodb_flush_log_at_trx_commit = 1 # 1 indicates that each SQL entry is written to the log and synchronized to the disk. As a result, THE I/O overhead is high. After the SQL is executed, the log must be read and written, resulting in low efficiency. 2 indicates that logs are only written to the system cache and then synchronized to the disk every second, which is very efficient. Transaction data will only be lost if the server fails. Recommended setting that does not have high requirements on data security 2, which has high performance and obvious effect after modification. Innodb_file_per_table = OFF innodb_file_per_table = OFF innodb_file_per_table = OFF innodb_file_per_table = OFF innodb_file_per_table = OFF innodb_file_per_table = OFF You are advised to enable the independent tablespace mode. The index and data of each table are stored in its own independent tablespace to move a single table among different databases. Innodb_log_buffer_size = 8M # Log buffer size. Since logs are refreshed at most once per second, it is generally not required to exceed 16MCopy the code

3.3 Optimization of system kernel parameters

Most oF MySQL is deployed on Linux. Therefore, some operating system parameters may affect MySQL performance. The following describes how to optimize Linux kernel parameters

Net.ipv4.tcp_fin_timeout = 30# TIME_WAIT timeout period. The default value is 60snet.ipv4.tcp_tw_reuse = 1 # 1 0: Net.ipv4. tcp_TW_recycle = 1 # 1: enable TIME_WAIT socket fast recovery. 0 indicates that net.ipv4. tcp_max_TW_BUCKETS = 4096 # The system maintains the maximum number of TIME_WAIT sockets. Net.ipv4. tcp_max_syn_backlog = 4096# Maximum length of SYN queues. Increase queue length to accommodate more waiting connections on Linux. If the number of open file handles exceeds the default value of 1024, the message "Too many Files Open" is displayed. Therefore, adjust the limit of open file handles. Restart the permanent: # vi/etc/security/limits the conf * soft nofiles 65535 * hard nofiles 65535 shall enter into force for the current user: # ulimit SHn - 65535Copy the code

Phase 4: Database schema expansion

With the increasing volume of services, the performance of a single database server can no longer meet the business requirements, so it is time to consider adding a server extension architecture. The main idea is to break down the load of a single database, break through the disk I/O performance, store hot data in the cache, and reduce the disk I/O access frequency.

4.1 Adding a Cache

Add a cache system to the database to cache hot data in memory. If there is requested data in the cache, it will not request MySQL to reduce the database load. Caching implementations include local caching and distributed caching. Local caching is the caching of data into local server memory or files. Distributed cache can cache massive data and has good scalability. The mainstream distributed cache systems: memcached and Redis, memcached has stable performance and data is cached in memory with fast speed. QPS theory can reach 8W. If you want to persist data, use Redis, with performance no lower than memcached.

Working process:

4.2 Primary/Secondary Replication and Read/write Separation

In the production environment, the service system usually has more reads and less writes. You can deploy an active and multi-slave architecture in which the active database performs write operations and performs dual-system hot backup. Multiple secondary databases perform load balancing and perform read operations. Mainstream load balancers: LVS, HAProxy, Nginx.

How do you implement read/write separation? Most enterprises implement read/write separation at the code level with high efficiency. Another way to achieve read and write separation through proxy programs, less application in the enterprise, will increase the consumption of middleware. The main middleware proxy systems include MyCat, Atlas and so on.

In this kind of MySQL master-slave replication topology, the single load is distributed, which greatly improves the concurrency capability of the database. If one slave server can handle 1500 QPS, then three can handle 4500 QPS and scale easily.

Sometimes, the single write performance cannot meet service requirements for applications with a large number of write operations. Can do the bidirectional replication (double), but there is a problem to pay attention to: the two main server if provides read and write operations, is likely to encounter data inconsistency phenomenon, produces the reason is that program have a chance at the same time operating two database, at the same time, the update operation will cause two database data conflicts or inconsistencies.

You can set auto_INCREment_increment and auto_INCREment_offset for each table ID field, or write algorithms to generate random unique values. Also consider the MGR (Multi-master Replication) cluster officially launched in the past two years.

4.3 depots

Branch database is based on the business of the related tables in the database to separate into different databases, such as Web, BBS, blog and other libraries. If the business volume is large, the separated database can also be master slave replication architecture, to further avoid excessive pressure of single library.

Table 4.4

As the data volume increases dramatically, a certain table in the database has millions of data, leading to query and insert time is too long, how can solve the single table pressure? You should consider breaking up the table into smaller ones to reduce the stress of a single table and improve processing efficiency. This method is called splitting tables.

The merge storage engine can also be used to implement the merge storage engine, which is relatively simple. After the sub-table, the program is to a total table operation, the total table does not store data, only some sub-table relations, and the way to update data, the total table will be based on different queries, the pressure to different small table, so improve the concurrency and disk I/O performance.

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

• Vertical split: split the original table with many fields into multiple tables, to solve the table width problem. You can put infrequently used fields in a separate table, large fields in a separate table, or closely related fields in a single 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 data in a single table.

4.5 partition

Partition is to make a list of data according to the table structure in the field (such as the range, the list, the hash, etc.) into multiple blocks, the blocks can be on a disk, can also be on a different disk, partition, a list or on the surface, but the data hash in multiple locations, so that more disk processing different requests at the same time, This improves disk I/O read and write performance.

Note: Adding caches, libraries, tables, and partitions is primarily done by programmers or DBAs.

Phase 5: Database maintenance

Database maintenance is the work of database engineers or operation and maintenance engineers, including system monitoring, performance analysis, performance tuning, and database backup and recovery.

5.1 Key Performance Indicators

Technical terms: Queries Per Second (QPS) and Transactions Per Second (TPS) Run the show status command to check the running status. More than 300 records of the status information are displayed.

Questions: Number of queries sent to the database Com_select: number of queries actually performed Com_insert: number of inserts Com_delete: number of deletes Com_update: Update count Com_commit: specifies the transaction count. Com_rollback: specifies the rollback countCopy the code

Then, here comes the calculation method: Calculate QPS based on Questions

mysql> show global status like 'Questions'; mysql> show global status like 'Uptime'; QPS = Questions / UptimeCopy the code

Calculate TPS based on Com_commit and Com_rollback:

mysql> show global status like 'Com_commit'; mysql> show global status like 'Com_rollback'; mysql> show global status like 'Uptime'; TPS = (Com_commit + Com_rollback) / UptimeCopy the code

Another calculation method:

QPS is calculated based on Com_select, Com_insert, Com_delete, Com_update:  mysql> show global status where Variable_name in('com_select','com_insert','com_delete','com_update'); Wait 1 second to execute, get the interval difference, the second time each variable value minus the corresponding variable value of the first time, is QPS.Copy the code

TPS calculation method:

mysql> show global status where Variable_name in('com_insert','com_delete','com_update'); Calculate TPS, not query operation, calculate insert, delete, update four values.Copy the code

According to the netizens’ test of these two calculation methods, it is more accurate to calculate with Questions when there are many myISAM tables in the database. When there are many innoDB tables in the database, Com_* is more accurate.

5.2 Enabling Slow Log Query

MySQL slowly enables log query to find out which SQL statement is slow. Dynamic enable is supported:

Mysql > set global slow_query_log_file='/var/log/mysql/mysql-slow.log'; Mysql > set global log_queries_not_using_indexes=on; Mysql > set global long_query_time=1; Analyze slow query logs. You can use MySQL's mysqlDumpslow tool to analyze slow query logs. Mysqldumpslow -t 3 /var/log/mysql/mysql-slow.log Analyze slow log, binlog, and general log. Analyzing slow query logs: pt-query-digest /var/log/mysql/mysql-slow.log Analyzing binlog logs: Mysqlbinlog mysql-bin.000001 >mysql-bin.000001. SQL pt-query-digest --type=binlog mysql-bin.000001. pt-query-digest --type=genlog localhost.logCopy the code

5.3 Database Backup

Backup database is the most basic work, but also the most important, otherwise the consequences will be serious, you know! For high frequency backup strategies, it is important to choose a stable and fast tool. If the database size is less than 2 GB, you are advised to use the official logical backup tool mysqldump. Above 2G, it is recommended to use Percona’s physical backup tool Xtrabackup, otherwise it will slow like a snail. Both tools support hot standby under InnoDB storage engine without affecting business read and write operations.

5.4 Database Restoration

Sometimes, the MySQL server is powered off or shut down unexpectedly. As a result, the table is damaged and the table data cannot be read. You can use the two tools that come with MySQL to fix it, myisamchk and mysqlCheck. The former can only repair MyISAM tables and stop the database, while the latter can be repaired online by both MyISAM and InnoDB.

Note: It is best to back up your database before repairing it.

MyISamchk -r --recover recovery mode -q --quik Quick recovery -a --analyze table -o --safe-recover Old recovery mode. If -r cannot be restored, You can use this parameter to try -f --fast to check only tables that are not closed properlyCopy the code

For example, myisamchk -r -q *.myi

Mysqlcheck; -a --all-databases Check all databases -r --repair table -c --check table, Default options -a -- ANALYZE table -o --optimize table -q --quik Fastest check or repair table -f --fast Check only tables that are not closed properly for example: mysqlcheck -r -q -uroot -p123456 weiboCopy the code

5.5 MySQL Server Performance Analysis

Focus on:

• Id: CPU usage percentage, less than 60% on average Normal, but busy.

• Wa: indicates the time for the CPU to wait for the DISK I/O response. If the value is greater than 5, the disk is heavily read and written.

KB_read/s and KB_wrtn/s The amount of read/ write data per second. The value is estimated based on the highest read/ write speed of the disk per second.

R /s, W /s: indicates the number of read and write requests per second. IOPS is a major indicator of disk performance. Await: Average I/O response time. If the value is greater than 5, the disk response is slow and exceeds its own performance. Util: the percentage of disk utilization, less than 60% on average. Normal but busy.

summary

Because of the original design limitation of relational database, it will be inadequate in big data processing. Therefore, NoSQL (non-relational database) fire up, inherently inspiring, with distributed, high performance, high reliability and other characteristics, make up for the relational database in some aspects of the congenital deficiencies, very suitable for storing unstructured data. Mainstream NoSQL databases include MongoDB, HBase, and Cassandra.

Simple database level optimization effect is not much obvious, mainly according to the business scenario to choose the right database!

Long press concern public number