MySQL General optimization tips

Content outline

1. Features of MySQL;

2. hardware, system optimization;

3. MySQL configuration optimization;

4. Schema design optimization;

5. SQL optimization;

6. Other optimizations.

Features of MySQL

First of all, it needs to be clear. To do MySQL optimization, you need to know what MySQL features are:

In short, MySQL is generally used for data persistence in the Internet business, and is used to ensure consistency and reliability of data, not for:

• 复杂查询；

• 复杂运算；

• 大二进制存储。

and other wonderful uses.

Features of CPU utilization

Check out the different versions of MySQL support and utilization of CPU multicore:

Suggestions:

• Use the latest MySQL version to improve its CPU utilization;

• Each SQL is simple enough, not too complex;

• Each connection is fast enough to complete, not "tired".

Memory utilization Features

What are the characteristics of memory utilization and management?

Suggestions:

• Close query cache;

• Adopt InnoDB;

• Use PERCONA\MARIADB branch version;

• Simple kv data is stored with NoSQL and does not use MySQL.

Features of disk utilization

Finally look at the characteristics of disk I/O:

Suggestions:

• Improves overall I/O performance with multiple disks;

• Use high-speed I/O devices more;

• Maximize memory to mitigate I/O load.

MySQL optimization

Now that we've learned all the features of MySQL, we can start working on optimization.

Before we start, we need to make a few clear points:

1. Why optimize? Leader assignment \ User complaint \ Surveillance alert \ Nothing to do? Now run well, there is no need to toss God horse optimization nothing to find smoke, even if want to practiced hand, also need to slow down, prevent misoperation;

2. The goal of optimization is what, plainly, is to solve what bottleneck, avoid in the process deviate from beginner's mind;

3. Calculate the input-output ratio, for example, in order to improve performance by 1% and put 1 people months, basically is very not cost-effective, it is better to do something else;

4. Optimization before the site information collection, optimization after the collection to do the comparison, to confirm the optimization results (for credit Ah, let the boss see your results, the end of the pay-up and so on, at least can also exercise summary of the ability to summarize the document).

In general, our routines for MySQL optimization work like this:

Confirm the demand, first clear the current state of operation, whether it really needs to be optimized, do not have nothing to do;

Common bottlenecks:

• Most bottlenecks lie in I/O subsystems;

• If the CPU is very high, more than 90% is due to improper indexing;

• When swap occurs, the memory allocation may be too small or too large;

• When the iowait is too high, try to optimize from the index angle, as well as improve the I/O device performance, increase the memory, reduce the sorting, reduce the Select one-time read data volume.

Common optimization Strategies

1. Instantaneous concurrency is very high, using thread pool;

2. Frequent order By\group by, index start;

3. Adjust the memory appropriately, not too big or too small. General IBP set to 50% ~ 70% advisable;

4. Iowait high, add memory, improve IOPS, reduce data read and write.

When formulating the program, focus on solving the problem of high frequency (quantitative change is more likely to cause qualitative change); Review the feedback, collate the documents, review the summary, summarize the rules from the scattered data and prevent the risk from appearing again.

The following bottleneck analysis tools are commonly used:

In the vast majority of cases, experienced engineers rely on Sysstat tools to concentrate on enough, many problems at a glance will probably know the bottleneck.

What are the ways to identify bottlenecks at the MySQL level?

Hardware, System optimization

We continue the MySQL optimization tour. Let's take a look at the hardware and OS level, which can be optimized. The first is mainly the bios of the CPU and memory parameter adjustment, followed by RAID optimization.

Then take a look at a few reference configuration diagrams:

1, the CPU chooses the maximum performance mode, avoids the energy saving mode to cause the performance insufficiency.

2. Turn off NUMA and reduce the swap probability.

3, Adopt RAID-10, and choose force WB.

At the OS level, you can have several optimizations:

• Adjust IO Scheduler

• Using XFS

• Adjust other kernel options

Note:

1. Vm.swappiness, reduce the probability of the occurrence of swap;

2. Vm.dirty_background_ratio & Vm.dirty_ratio To avoid a sudden large number of I/O requests causing the system to die.

From this test results can be seen noop/deadline have obvious advantages.

This IO scheduler can also be modified online Oh, also waiting for God horse?

echo Deadline >/sys/block/sdc/queue/scheduler

When testing with PCIe SSD devices, XFS can run up to 4 times times the ioPS of EXT4 and perform very well.

Is there any reason not to use XFS?

XFS Mount Parameters:

/dev/sdc1/data XFS defaults,noatime,nodiratime,nobarrier 0 0

Formatting parameters are not specified, and are default.

MySQL Configuration optimization

As mentioned earlier, the memory allocated to MySQL is not too large or too small, then how much appropriate.

First, figure out which parts of MySQL's memory are made up of:

1. The SGA of global buffers and Oracle means that global one-time allocations are shared among multiple threads.

2. Thread buffers and the PGA of Oracle mean that each thread is assigned separately and the threads cannot be shared with each other, so do not allocate too large to avoid memory usage, and oom occurs.

principle: When adjusting to these options, do not tiger casually adjust, must first do in the heart to know, understand its specific role before hands.

Look at the performance of the Innodb_flush_log_at_trx_commit 0, 1, 2, respectively, for example:

If you enable the comparison after Binlog:

Finally, a comparison of the different settings for the Sync_binlog option:

Note: These 3 test result graphs are all from Percona.

Conclusions & Recommendations:

1. To ensure data security, set Trx_commit =1 & sync_binlog = 1

2. On slave or non-critical scenarios, you can change to 0

Schema Design Optimization

Next look at the MySQL Schema Design optimization essentials:

Points:

1. By default, use the InnoDB engine, do not myisam to yourself;

2. InnoDB must have the primary key of the self-increment (or similar self-increment) attribute;

3. Text/blob columns are not used or used sparingly;

4. Not NULL is mainly for optimizing index efficiency;

5. If there is no special need, can use the latin1 character set, otherwise use UTF8\UTF8MB4 and other large character sets to ensure universality.

Other Highlights:

SQL optimization

There are several points to the SQL optimization plane:

and the optimization points for the COUNT (*) and Oita pages:

Next, let's take a look at explain's results, what are the key messages to note. First look at the type column of the explain result, you can give us what information:

Let's look at what states the extra column has to give attention to:

MySQL's slow log can be parsed and managed using the following tools:

Pt-query-digest + Box anemometer case, slow log can be easily managed.

There are a few key points about join optimization:

Next look at the scenarios in which the indexes cannot be used effectively:

Take a look at several killer SQL cases and their optimization recommendations:

In peacetime, after we log in to the MySQL server, if you feel the problem, you can focus on the following some of the thread status:

Other optimizations

A brief introduction to the DBA's tool, commonly used percona-toolkit tools:

Attached: Introduction to MARIADB and Percona branch versions

Q&a

Q1: Multi-instance, process will not preempt? Each case is a stand-alone.

A: Except that the OS level resources will interact with each other, others will not. For example, if an instance consumes extra CPU resources, then other instances will also be affected, which is inevitable, unless the isolation is done in the form of virtualization.

Q2:SSD recommend Flixbox or RAID?

A: If you do not worry about losing data, the single-disk chant. If you're afraid of losing it, it's obviously not a single plate. If the random Io is very high, the RAID5 will not fit. But unless you use SSDs, you're not afraid to use RAID5. In fact, the RAID card will affect (reduce) the performance of the SSD, but for data reliability, no way, fortunately the impact is not particularly large.

Q3: Can you describe which business scenarios are suitable for what kind of RAID?

A:1, high random io, with raid10;2, need large capacity, with RAID5. Basically, in these two scenarios, in fact, because SSD IOPS performance is already very good, many enterprises will choose to build RAID5 directly with 3 disks. Without a doubt, on a PCIe SSD, you can avoid a lot of problems, or DBAs can do a lot less work, at least ease.

How should the q4:nnodb_buffer_pool_instances be set?

A:IBP instance generally not more than 8 advisable, more than 8 words, there may be a reaction, but the premise of multiple instance, the average to each instance of the IBP can not be less than 2G, otherwise there is no meaning.

Q5:no Text,or in compressed is it recommended to compress if I use text? What is Mr. Yip's experience in compressing data?

A: Yes, it is recommended that you do not store large amounts of text in InnoDB. If necessary, compress and deposit in advance. Do not need to retrieve the text, can be all compressed and stored in, not with InnoDB compression format Oh, is pre-external compression after storage, text content in the storage before the first compression, not with InnoDB compressed this row format, that will be pits miserable, performance loss of 9 layers, Only half of the compression ratio, it is better to use tokudb forget.

What are the pros and cons of Q6:mariadb and MySQL, and what does the great God think of Maria's tendency to replace MySQL?

A: Want to replace the early, not so easy, and there is no need to replace, as a supplement OK. Unless the official MySQL version is closed, or the support is poor.

Q7: The new business system, is it recommended to continue with MySQL5.5 or above, or with mariadb?

A: Recommended priority Percona 5.6, followed by MySQL 5.6, the last is mariadb.

Q8: Is your database backup done with Percona tools? Every Monday full, one increment per day? Backup with these tools, will not be able to recover the situation? Is there a way to verify that the backup is "normal"?

A: Tools to Xtrabackup-based, mysqldump supplemented, the number is not huge, every day a full prepared, most have slave do hot spare, so there is no regular increase. Mydumper also some not too cool, also relatively small is, the backup file must do the recovery test, do not only backup not to restore test, the key moment will be dead.

Q9: How does a restorative test have a process plan to guide you?

A: Simple: Data recovery, simple query verification number, key data, etc. complex: Take the test environment Bai.

Q10: Is there a more efficient tool or method for verifying backup effectiveness? Or do I have to restore the library to check it out?

A:mysqldump or Mydumper back-up files can be easily and quickly verified with grep, Xtrabackup, can only look at the file size, or do a full amount of recovery.

MySQL General optimization tips