Shulou(Shulou.com)06/01 Report--

Technical brothers, we are tired from work, let's relax together! Lao Zhang, I am a fan of Jin Yong. In Jin Yong's novels, the eighteen hands of the dragon deserve its peak, and its power can be imagined. Today, Lao Zhang wants to introduce 18 tricks to optimize our MySQL database to make it run faster and more stable!

Students always asked me how to optimize our MySQL database, Mr. Zhang. This question is too general and not very specific! Because the optimization of the database needs to be considered from many angles, through different dimensional models to troubleshoot the problem. The teacher sorted out the next train of thought, probably from 18 angles, roughly four directions to give you some suggestions.

The first palm-Kang long has regrets

To ensure that the database can run efficiently and stably on the server, we must first ensure that there is sufficient memory. Only when the memory is large enough can we cache the hot data that we often access. Of course, some update statements can also be done in memory first. But we have to consider the use of the golden section of memory, due to the existence of different businesses, the demand for memory is of course different.

For example, for the hot data frequently accessed by users, the allocation of memory should reach about 70-80% of the database memory as far as possible. As we all know, we know that MySQL database memory is mainly composed of innodb_buffer_pool,redo log buffer,double write buffer,binlog cache and so on. If there is only one MySQL application running on the server, then probably innodb_buffer_pool can be allocated to about 50-80% of physical memory.

TIPS: we need to consider the allocation of database memory according to the actual physical memory size and the specific business type.

The second palm-Flying Dragon in the sky

To optimize the MySQL database, you must first be very familiar with the opponent. With the upgrade of the version, MySQL uses more CPU cores. Since MySQL 5.6, 64 cores can be used. The characteristic of MySQL connections is that each connection corresponds to one thread, and each sql/ query can only use one cpu core, so the more CPU is required, and the faster the CPU. Only in this way can we improve the performance of the database and improve the concurrency ability of our database!

TIPS: use multicore CPU.

The third palm-see the dragon in the field

As we all know, IO has always been a bottleneck for databases, and is likely to be for some time to come. So the demand for storage media is very high, for the IO system is relatively high, it is recommended that we use faster storage device SSD solid state disk can improve hundreds of times the data read and write performance or PCIE-SSD solid state disk can improve the data read and write ability thousands of times. For example, some e-commerce websites need to use this equipment to meet the influence requests of a large number of users when they are engaged in store celebrations or promotions.

TIPS: high speed hardware equipment, SSD or PCIE-SSD is recommended

Fourth hand-Hung-chien to Lu

Since web2.0 started, basically all the software we use has been independently developed based on the linux platform. We know that MySQL database is also running on the linux operating system. In the official recommendation, it is estimated that Solaris is the most recommended, but from the perspective of actual production, both CentOS and REHL are good choices. It is recommended to use CentOS. If you have to use REHL, the recommended version is later than 6. Running MySQL database under windows is not recommended here. Although with the upgrade of MySQL version, windows has been optimized, but for high concurrency and high load environment, it is still not recommended.

TIPS: CentOS or REHL operating system type is recommended

The fifth palm-do not use the hidden dragon

For optimization at the operating system level, we have to consider a problem that we may ignore, first of all, the problem of swappiness. The value size of swappiness is closely related to how swap partitions are used. There are two limits, one is 0 and the other is 100. check out the executable cat / proc/sys/vm/swappiness.

0 stands for: maximize the use of physical memory, followed by swap partitions, which may lead to a system memory overflow, resulting in mysql being accidentally kill dropped. This setting is not recommended.

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

TIPS: the default 60 is recommended here.

The sixth palm-Li wade across the river.

Corresponding to swappiness, another optimization at the operating system level, and another small detail is IO scheduling. There are cfq,noop and deadline, the system uses cfq by default, and here the teacher recommends using deadline. View method:

Cat / sys/block/sda/queue/scheduler/

TIPS:deadline can adjust the read and write time to avoid finishing a starvation scene that has not been read.

The seventh palm-suddenly

After Oracle 11g, an additional result_cache is added to cache the data result set. MySQL caches static result sets through a query cache in innodb_buffer_pool. We all want the hot data to be stored in memory, we can read the data quickly and easily, and the cache rate of the database is also very high! However, as soon as the data in the query cache in the database changes, the cache is meaningless and becomes a chicken rib. Moreover, if Query Cache is enabled, both updates and writes have to check the query cache, which increases the writing overhead.

TIPS: it is recommended that query cache be closed

The eighth palm-shocked a hundred miles

We are all too familiar with disk arrays, but how do we choose the cache strategy for array cards? First of all, for qps,tps, high-service systems, be sure to configure array cards, cache modules, and BBU modules (used to provide backup power).

There are two kinds of cache strategies, one is write through (WT), the other is write back

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

TIPS: it is strongly recommended that write back be used in the cache strategy for array cards.

The ninth palm-or leap in the abyss

As mentioned earlier, allocate as much space to the innodb_buffer_pool as possible, which is about 50-80% of the physical memory if the server only runs the database.

TIPS: it is recommended that the application and the database be deployed separately on the server to troubleshoot problems later.

The tenth palm-Ssangyong fetch water

There are some core parameters of MySQL database that we should keep in mind. For example, the meaning of double one directly affects the refresh mechanism of the log. Refresh mechanism that affects redo log buffer

Innodb_flush_log_at_trx_commit = 1 (safest)

Innodb_flush_log_at_trx_commit = 2 (average performance)

Innodb_flush_log_at_trx_commit = 0 (best performance).

Affects the refresh mechanism of binlog cache ~ sync_binlog=0. After a transaction is committed, MySQL does not do disk synchronization instructions such as fsync to refresh the information in binlog_cache to the disk, but let Filesystem decide when to synchronize, or synchronize to disk only when the cache is full. Sync_binlog=n, after every n transaction commits, MySQL will issue a disk synchronization instruction such as fsync to force the data in binlog_cache to disk. To ensure security, we can use sync_binlog=1. To get the best performance, we can use sync_binlog=0.

TIPS: for different business companies, the protection point is different, so we have to consider whether the business is the most important, or the data is the most important! Then set different parameters value respectively.

The eleventh palm-the fish leaps in the abyss

The main difference between MySQL database and other databases is plug-in storage engine, the most famous of which are myisam and innodb. They all have their own characteristics, here is strongly recommended to use innodb storage engine table, whether it is for transaction support, or online DDL statements fast operation, it is currently the best storage engine! Innodb is the default storage engine used after MySQL 5.5,

TIPS: in the production environment, if there are tables of such storage engine as myisam, it is recommended to do all the conversion of myisam-- > innodb storage engine! However, after MySQL 5.7, the system tables are all innodb!

The 12th palm-time multiplied by six dragons

Xfs is highly recommended for file systems, and things like ext3,ext4 are no longer used, because xfs is also the tree structure that B-tree structure is closest to a database.

The thirteenth palm-Miyun does not rain

In a production environment, operations such as delete or update for large tables often occur. With the emergence of data fragmentation, we often have to defragment the major business tables to make query retrieval faster. You can monitor the tables that interact most closely with disk through pt-ioprofile, and then defragment the tables by alter table or by importing and exporting data. Reclaim tablespaces as much as possible

Fourteenth palm-damage means there is a fu

Use lepus or zabbix to monitor the database. Monitoring items can be from the status of the server, the use of memory, the load of cpu. The information of adding, deleting, changing and checking every second in the database, latency and replication status information in the architecture are used as the core points of monitoring.

The 15th palm-the dragon battles in the wild

Cooperate with the developer to design the table structure reasonably, and choose the appropriate data type of the field according to the principle of simplicity, the better. For ipv4, the field of time type, we can access it through integer int! Through the function conversion on it!

Ip involves two functions: inet_aton and inet_ntoa

Two functions of time type: from_unixtime and unix_timestamp

Sixteenth palm-selection of transaction isolation level

There are four levels of transaction isolation in the mysql database. They are Read Uncommitted (RU), Read Committed (RC), Repeatable Read (RR) and Serializable (SR). For websites with a transaction type system, we recommend using the isolation level of RR for higher transaction requirements.

The 17th palm-the sheep touches the vassal

Change the file handle ulimit-n default 1024 is too small

The process limit ulimit-u is determined according to different versions

Ban NUMA numctl-interleave=all

Eighteenth palm-the dragon swings its tail

Students who have done the database can often encounter problems like too many connections, for such a problem, we must do a good job in configuring the concurrency within the database. The parameter innodb_thread_concurrency determines the concurrency of innodb. The default size is 0. In the mysql5.7 version, thread pool is added, and the existence of connection reuse can be taken as the default value of ok. However, prior to version 5.7, you need to consider the value. I personally recommend setting it to 36 in the mysql5.6 version. Before mysql5.6, it can be 8-32.

The eighteen palms of the dragon have been played. I hope it will be helpful for database enthusiasts and students engaged in database work. Let's learn a little every day, practice our internal skills more and more deeply, and play our own martial arts. Let our database fly!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.