MySQL Optimization--Bosenry

http://blog.itpub.net/28916011/viewspace-1758440/

Now let's talk about the two features of MySQL memory and I/O.

First, MySQL Memory Features:

1. There is also global memory and memory per session (each session is similar to Oracle's SGA and PGA), but for each session of memory, we do not assign it too large. If the memory allocated to each session is too large, it can cause the oom to occur. In high concurrency, increasing physical memory can reduce physical I/O. So MySQL's memory management is simpler than Oracle.

2. There is a library cache in Oracle to cache the execution plan. But MySQL does not cache the execution plan, MySQL has a similar Oracle-like Qcache,qcache is used to cache SQL statements, but in production is recommended to turn off this feature, because the function is very chicken, its function does not reflect that big role.

3, MySQL Query Analyzer is very efficient, MySQL does not cache the execution plan, but does not appear as the oracle of the kind of hard parsing problems.

4, for the memory allocation, if it is a single instance of MySQL, it is recommended to allocate 50% to 70% of physical memory to MySQL.

Second, MySQL I/O aspects of the characteristics

MySQL has binlog, undo log, and redo log three log files that are sequential write I/O. MySQL data files are composed of random write I/O and sequential write I/O.

Currently MySQL has InnoDB and MyISAM storage engines. One might say that MyISAM's storage engine is faster than the InnoDB storage engine. This is not the case, actually. Why do you say that, huh? InnoDB primarily retrieves data based on the primary key, because the primary key contains the data information of the row, and, for InnoDB, it is a doubly linked list structure, so he can find the data very quickly. In addition, InnoDB caches both the data file and the index file. But for MyISAM, MyISAM only caches index files. So, a very simple truth, you say is read from memory fast, or from disk read fast. The answer must be read from memory fast. So the conclusion is that InnoDB is faster than the MyISAM storage engine.

 

Optimization ideas:

1 , the process of discovering problems

When the database is slow, to understand what the problem caused the system is slow, is the problem of the system, or the development of the problem, or the database;

2 , after finding the problem, lock the problem bottleneck point;

The process of locking the bottleneck point is divided into two levels. One is the system level, one is the database level.

2.1 system Level

At the system level, we need to learn some Linux knowledge and use the knowledge of Linux to lock down the bottleneck of the problem. The top command can see which process consumes the highest CPU, view the load, and can see the memory.

Vmstat can view memory, CPU, I/O.

Sar–u viewing CPUs

sar-d View IO

Sar-r Viewing memory

2.2 Database Level

MySQL default storage is now caused by InnoDB.

1. Show Engine InnoDB status

See the situation of lock through show InnoDB status, see if data has no lock, there is no deadlock, there is no transaction status, look at some pages of the situation, see now there is no old page and new page move.

2. Slow log

Watch slow logs, make time, have a lot of tools to analyze slow logs (like Percona company), find TOP10 slowest SQL (the top 10 SQL statements with the highest frequency of general precedence)

You can also use graphical tools to find the slowest SQL, then find development communication, discuss whether to change it in business, or optimize it for this SQL. Our goal is to make SQL as simple as possible.

3. Show Global Status

Show global status looks at the overall performance of the database to see which points need to be adjusted to see how fragments are produced, how much fragmentation is produced, and how to clean up fragments. See how much TPS is generated per second.

4. Show Processlist

Show Processlist View a case of the entire MySQL link, note the Wait timeout and inactive timeout, which mainly reduce inactive connections, which can take back those consumed memory, thus preventing omq from happening.

5, Pt-ioprofile

Pt-ioprofile is a third-party tool to see which tables within InnoDB are most active, because most optimizations are at the SQL bottleneck point. When we lock into these tables, we can do some articles on these tables to see if the tables need to be indexed and defragment. So this tool is also very important.

3. Initial determination of optimization method

We are locked into the bottleneck of the problem, we need to develop an optimization plan.

4. make the optimization plan.

5 , test of optimization scheme

We cannot influence other businesses because of the optimization of one problem point. So the locking problem is from the polygon to the point, but the optimization test is from points to polygons. All we have to do is to solve the problem without affecting the whole. So the test must look for the test environment, the program can be really implemented in the production line.

6 , implementation of the programme

7 , the problem solved

Be sure to make a record of why such a problem occurs and avoid the next occurrence. This is also a good way to test a person's learning.

 

optimization at the hardware angle .

from the system BIOS level

From the system BIOS level, there is a parameter called DAPC, which means that each watt of electricity can produce the greatest effect, can let the CPU play its maximum effect.

At the BIOS level, there is also a parameter that is memory frequency and must be tuned to max performance.

There is also a CAE processor at the BIOS level, and when it is idle, we can disable the processor so that it is in the lowest state.

about TPS High, high business

With regard to the high TPS and high business conditions, it is important to configure the array cards, so be sure to configure the cache module and the Bbu module. The BBU module is used to provide my backup power, and when the machine is powered down, my BBU module can go as a power source to ensure that the data is not lost and the data is written to the cache. Now that the new server is the BBU of the capacitive mode, the performance will be better.

Questions about the array card policy

On the problem of array card strategy, be sure to choose WB (write bike), when writing data, be sure to write to the cache card, and then through the array card to brush the data to disk, so that can improve our iops. Be sure to disable Wthrow, because this direct write disk is very performance-intensive.

About turning off the read-ahead feature

About turning off the read-ahead function is to let the cache do write caching. Do not turn on pre-reading, do not open the data page, into my cache, this does not have any effect.

About array-level selection

With regard to array-level selection, RAID 1+0 is recommended for MySQL databases.

Some people say that using RAID5 is good, but RAID5 's write multiplication data is 4. Why is 4, because RAID5 has read data, read check bit, write data, write check bit. But RAID10, it has only double write, that is, the coefficient of RAID10 is 2. So raid10 write I/O must be higher than RAID5, but RAID5 read I/O than raid10 may be better, but for OLTP this system, recommended RAID10.

About policies on disk

For policies on shutting down disks, be sure to turn off the cache policy on the disk. Because we don't have to open it, this prevents data loss.

Questions about the disc selection

Recommended on SSDs or PCIE-SSD disks, which can increase iops or thousands of times.

about Swappiness adjustment of value size

The size of the swappiness value has a lot to do with how we use the swap partition. When Swappiness is set to 0, it is the full use of virtual memory. When the Swappines is set to 100, the swap partition is used, which means the swap partition can be used.

Before the Redhat 6 version, swappiness can be set to 0, but greater than REDHAT6 is best set to 10, to Redhat 7 above, be careful to set swappiness set to 0, because if set to 0, Redhat 7 may occur in OMQ.

about I/O selection of the scheduler

IO Scheduler, preferred deadline, followed by NoOp, do not use the default CFQ, the default is very bad.

About the file system selection

First XFS, followed by EXT4.

 

InnoDB is the default storage engine for MySQL. The most important parameter affecting the InnoDB engine is innodb_buffer_pool_size, which is equivalent to the buffer cache of Oracle, which is used to cache data.

In single-instance MySQL, it is best to set the physical memory size of innodb_buffer_pool_size to 50%~70%.

Innodb_data_file_path, this parameter is the size of the allocated shared table space. It is 10m by default. However, we recommend that you set it to 1G, which avoids late data spikes and consumes performance.

Innodb_log_file_size, this parameter should not be set too large, because this parameter is equivalent to the redo inside Oracle. If you set this parameter too large, the recovery data will be slow when the database is crash.

A transaction_isolation that represents the transaction isolation level. The level of transaction isolation required for MySQL is a step-by-step choice. For Oracle, the transaction isolation level is the default commit read. But for MySQL, there are default commit reads, repeatable reads, dirty reads, and string reads. It is recommended that you use MySQL's repeatable read (R mode), because this mode guarantees data consistency, avoids the occurrence of a transaction commit, and sees what he commits in another transaction. If a transaction commits, it can see what he commits in another transaction, which violates the case of transactional consistency.

Sort_buffer_size, Read_buffer_size, join_buffer_size three parameters, can be understood as the PGA of Oracle. These three parameters do not have to be set too large, about a few megabytes, a few 10 trillion on the line. Also, Oracle's PGA is not too large to set.

General_log,log_bin,sync_binlog,long_query_time,interactive_timeout,wait_timeout,max_connections

In production, do not open a full-time log (General_log), because the full-day log, it will record all the SQL statements, which affects MySQL performance.

For Binlog logs, be sure to turn this feature on, as this will enable replication and also enable the Binlog recovery function.

For Sync_binlog, the size of the parameter value is related to the database write Binlog situation.

Sync_binlog=0, said I brush every second binlog,sync_binlog=1 means I brush every second, to ensure that he will not lose; when sync_binlog=2, said to the operating system, the database regardless.

For Long_query_time, a scenario that indicates slow query time, you can set a slow query of 0.xxxx seconds. Optimize for slow SQL that appears to be high frequency.

You can see some interactive and non-interactive time waits through show processlist. For Interactive_timeout and wait_timeout These two parameters we do not set too large, generally these two values are set, in the case of no connection pool, set to 5 minutes on the line.

Max_connections, when the number of user connections exceeds this max_connections, an error will be encountered. But notice, when error, do not blindly increase max_connections this parameter. Because if the max_connections set too large, the database will be connected to the storm, the collapse of the situation, is very dangerous. So how do we adjust it? In fact, this situation will be linked to a lot of parameters, such as can reduce the value of the concurrency parameter to reduce the number of connections, or to observe the database SQL statements, whether it is business or database problems. Don't take everything to yourself and show the evidence that it's not my dba's problem.

Choose the storage Engine, which is for business. The default for OLTP is InnoDB. To MySQL 5.7 It is possible that the MyISAM will disappear.

The InnoDB storage engine supports transactions, supports row locks, and has a lower granularity of locks, so concurrency is good and can be recovered based on redo and undo when a failure occurs. InnoDB is cached for data and indexes, but MyISAM only caches indexes, and MYISAM data and indexes are separate.

Be sure to set the self-increment primary key. If you do not set the self-increment primary key, it does not matter, this time MySQL will give it a 6-byte primary key, but this will be very performance-intensive. Therefore, setting the self-increment primary key is a required option.

For time and date, the IPv4 type and data type can be in int

Avoid using large data types such as Text/blob. If you want to use a large data type, you can store the big data type separately on a single table.

When defining a field, try to define not NULL, because the index is not a null field.

Low-selectivity fields do not create indexes, fields such as men and women do not create indexes, and creating indexes is meaningless. Because MySQL optimizer is very intelligent, duplicate values appear a lot, MySQL may not go index, but go full table scan.

For sorting and grouping fields, be sure to create an index.

The index does not have too many, because update causes the page of the index to be flipped, with a significant decrease in performance.

A federated index is better than a single-column index, and a federated index can shorten a range of entire segment pool searches, which is better than a single-column index.

Like some character types, if it is possible to use only the first few characters, rather than the entire word Jianjian index, which is called the prefix index, we can create a prefix index, I only search those fields.

Index scan record more than 30%, will go full table scan

The double percent of the fuzzy matching query is not indexed. But removing the%,% on the left will probably use the index in the last one.

A federated index, where the first query condition is not the most indexed column, and the index is not used, which is the leftmost prefix principle.

Federated indexes, if the first index column uses a range query (>, =, <, >=, <=), then the index is part of the index, it is possible to use only the first index, and the subsequent index database is not available.

Two independent indexes, one to retrieve, one to sort, and possibly one to use. MySQL 5.6 has the ITC feature.

It is most taboo to use a function on an indexed field, so that it does not walk the index.

Q&a

The SGA is the memory global zone, and the PGA is the memory that is allocated separately to the database on a session connection of the user.

The most common cluster used by MySQL is MHA. Oracle's cluster is RAC.

Why are deadlocks generated? is because of a total rob a thing, you want my things, I have your things, thus causing the cycle of death, with show InnoDB status where the deadlock generated.

It is best to close the disk cache policy because it is written in the cache and is prone to data loss, so it is recommended to turn off the disk cache policy.

Have questions,

Ensure the stability of the business and ensure fast database.

Master-Slave separation has nothing to do with DBAs, only development is written in the code.

Now the MySQL architecture is using MHA.

Not that the volume of data is large, optimization, but the performance of the problem and then optimize. Look at the index is not built.

MySQL Optimization--Bosenry