Database optimization of the Dragon 18 palm

Technical old iron, work tired, we will come together to relax! Lao Zhang I is a fan of Jin Yong, in Jin Yong novels, the Dragon 18 palm of the peak drilling, its power can be imagined. Now son, Lao Zhang to give you introduce 18 moves, to optimize our MySQL database, let it run faster, more stable!

Before the old students asked me, Mr. Zhang how to optimize our MySQL database? This question is too general, not very specific! Because the optimization of the database is to be considered from many angles, the different dimension models are adopted to troubleshoot the problem. The teacher organized the next idea, probably from 18 angles, roughly four directions to give you some advice.

The first palm----Prince have regrets

To ensure that the database can be efficiently and stably run on the server, we must first ensure that there is sufficient memory, only the memory is large enough, we can cache those we frequently access the hot data, some of the operation of the UPDATE statement can also be in memory priority. But we have to consider the use of the golden rule of memory, because of the existence of different businesses, the need for memory is of course not the same.

For example, users often access the hot data, the allocation of memory will be as far as possible to reach the database memory 70-80% about. As we all know, MySQL database memory is mainly made up of Innodb_buffer_pool,redo log buffer,double write Buffer,binlog cache, and so on. If the server is running on only one application of MySQL, that probably innodb_buffer_pool can be allocated to the physical memory of the 50-80% around.

TIPS: we want to consider the allocation of database memory according to the actual size of physical memory and what kind of business it is.

The second palm----The Dragon in the sky

To optimize the MySQL database, the first to understand the opponent, as the version of the upgrade,MySQL to use more CPU cores, since MySQL 5.6 can be used to 64 cores. The MySQL connection feature is so that each connection corresponds to a thread, each sql/query can only be used to a CPU core, so the more CPU is needed, and the faster the CPU. This will help improve database performance and improve the concurrency of our database!

TIPS: use multi-core CPUs.

The third palm----See the Dragon in the field

As we all know, IO is always a bottleneck for the database, and it is possible for some time in the future. Therefore, the requirements for the storage medium is very high, for the high IO system, it is recommended that we use the faster storage SSD SSDs can improve the data read and write performance of hundreds of times or the PCIE-SSD SSD can improve the data read and write ability. A number of e-commerce websites, such as those currently on sale, need to use this device to meet a large number of user impact requests.

TIPS: recommended high RPM hardware devices, SSDs or PCIE-SSD

The four palms----the land of Hung-chien

Since the beginning of the web2.0 era, basically all of the software we use has been developed on a Linux platform. We know thatMySQL database is also running on the Linux operating system. In the official recommendation estimates are the most recommended Solaris, but from the actual production point of view CentOS and Rehl are good choices, personal recommendations to use CentOS, if you do not want to use Rehl, the proposed version of 6, it is not recommended to use in Windows run M Ysql database, although with the MySQL version of the upgrade, Windows has been related to the optimization, but for high concurrency, high-load environment, is still not recommended for use.

TIPS: It is recommended to use CentOS, or Rehl OS type

Five Palms----the Dragon

Operating system level optimization, we have to consider a possible people will be more overlooked problems, the first is the swappiness problem. The value size of the swappiness is closely related to how the swap partition is used. There are two limit values, one is 0, the other is 100, and the executable cat/proc/sys/vm/swappiness is viewed.

0 Rep: Maximize the use of physical memory, then the swap partition, which can cause system memory overflow, resulting in the accidental killing of MySQL. It is not recommended to set this.

100 is: actively use the swap partition, and the memory above the data in time to move to the swap partition.

TIPS: It is recommended to use the default 60.

The palm of the hand----the DA Chuan

With Swappiness, another operating system level optimization, there is also a small detail point is IO scheduling. There are cfq,noop and deadline, the system uses CFQ by default, and the teacher recommends using deadline. To view the method:

cat/sys/block/sda/queue/scheduler/

TIPS: deadline can adjust read and write time to avoid the starvation scene without being read.

The seventh palm----suddenly

Oracle 11g has one more result_cache to cache the data result set. Inside MySQL , there is a query cache inside the Innodb_buffer_pool that caches the static result set. We all want the heat data to be stored in the memory, we read the data quickly and easily, the database cache rate is also very high! But when the data in the database query cache changes, this buffer is meaningless and will become chicken. And if you turn on query cache, both the update and write are going to check the query cache instead of increasing the write overhead.

TIPS: It is recommended to close query cache

The eighth palm----shocked thyme

We are familiar with the disk array, but how do we choose the cache strategy for the array card? First, for Qps,tps, a high-service system, be sure to configure the array card, with the cache module, and the BBU module (to provide backup power).

There are two types of cache policies: Write through (WT), and the other: write back;

It is strongly recommended to use write back (WB). WT meaning, data directly written to disk, WB meaning: The data is written on the array card cache, and then written to disk by the cache, which improves the performance of the write. And it's good for accelerating redo log, binlog, and data file.

TIPS: It is strongly recommended that the cache policy of the array card use write back.

The nineth palm----or leap in the abyss

The front also involves, as large as possible to allocate space for Innodb_buffer_pool, in the server only run the database one application premise is probably the physical memory 50-80%.

TIPS: It is recommended that the application be deployed separately from the database on the server to troubleshoot the problem later.

Tenth palm----Double Dragon fetch water

MySQL Database Some of the core parameters, we have to remember in mind. For example, the meaning of the two, directly affect the log refresh mechanism. Refresh mechanism that affects redo log buffer

innodb_flush_log_at_trx_commit = 1(most secure)

Innodb_flush_log_at_trx_commit = 2 (general performance)

innodb_flush_log_at_trx_commit  = 0 (Best performance).

sync_binlog=0 , when the transaction commits, MySQL does not do fsync such as disk synchronization instructions to refresh Binlog_cache information to disk, and let Filesystem decide when to synchronize, or The cache is full before synchronizing to disk. sync_binlog=n , after each n transaction commit, MySQL will perform a disk synchronization instruction such as Fsync to force the data in the Binlog_cache to disk. To ensure security, we can sync _binlog=1. For best performance we can Will sync_binlog=0.

TIPS: for different business companies, the point of protection is not the same, all of us to consider good, is the most important business, or data is the most important! Then set different parameters to the value

11th Palm----Diving Yu Yu

MySQL database is different from other databases the most important is the plug-in storage engine, the most famous is MyISAM and InnoDB. They all have their own characteristics, it is strongly recommended to use the InnoDB storage engine table, whether for transactional support, or the online DDL statement fast operation, it is the best storage engine today! The storage engine used by default after MySQL 5.5 is InnoDB

TIPS: in a production environment, if there are tables MyISAM this storage engine, it is recommended to do all MYISAM-->INNODB storage engine conversions! But after MySQL 5.7, the system tables are INNODB!

12th Palm----by six dragons

The file system strongly recommends using XFS, no longer using EXT3,EXT4, because XFS is also the b-tree structure that is closest to the database tree structure.

13th Palm----Milin

In a production environment, it is often possible to delete large tables or update such operations. As data fragments are generated, we often defragment the main business tables to make query retrieval faster. You can monitor the tables that interact most closely with the disk by Pt-ioprofile, and then defragment the tables by ALTER TABLE or by importing the exported data. Reclaim table space whenever possible

The 14th Palm----The loss of a Fu

The use of the Day Rabbit (Lepus) or Zabbix to do a good job of monitoring the database. Monitoring matters can be from the state of the server, the memory usage, the CPU load. Database in the second of the deletion and modification of information, the structure of the delay and replication status information to the core of the monitoring.

15th Palm----Dragon War in the wild

In conjunction with the developer to design the table structure reasonably, the simpler the better the principle, to choose the appropriate field data type. For IPv4, the time type of the field, we can be fully accessible by integer int! You can do it with a function transformation!

IP involves two functions: Inet_aton and Inet_ntoa

Two functions of a time type: From_unixtime and Unix_timestamp

16th Palm----Selection of transaction isolation levels

There are four types of transaction isolation levels in the MySQL database. They are READ UNCOMMITTED(RU),read Committed(RC),Repeatable Read (RR),Serializable (SR) . For the network of the trading type systemfor high transaction requirements, we recommend using theRR this isolation level.

17th Palm----Shing

Change file handle ulimit–n default 1024 is too small

The number of process limits ulimit–u depends on the different versions.

Disable NUMA Numctl–interleave=all

18th Palm----Dragon Tail

The students who have done the database, can often encounter too many connections such problems, for such a problem, we must do the configuration database internal concurrency. Innodb_thread_concurrency This parameter to determine the concurrency of the InnoDB. The default size is 0. In the mysql5.7 version, add the thread pool, the existence of the connection reuse, you can take the default value OK. However, prior to the 5.7 version, you need to consider the value, personal recommendations mysql5.6 version set to 36. mysql5.6 can be 8-32 before.

Down Dragon 18 Palm has been finished, hope for database enthusiasts, engaged in the database work of students to help. Let us learn a little every day, the strength of their own training more and more deep, play their own martial Arts. Let's get our database up and flying!

This article is from "Zhang Mengsu blog" blog, please be sure to keep this source http://sumongodb.blog.51cto.com/4979448/1949024

Database optimization of the Dragon 18 palm