Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what are the parameters that need to be concerned about the performance of MySQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. Innodb_flush_log_at_trx_commit is set to 2

This parameter refers to how the transaction log is written from log buffer to the log file (ib_logfile0, ib_logfile1)

= 0 mysql crash is lost, and the performance is the best

Buffer pool-> log buffer per second wirte os cache & flush disk

= 1 will not be lost and inefficient

Every time commit,buffer pool-> log buffer- > write os cache & flush disk

= 2 even if mysql crashes, it will not lose data.

Every time commit, buffer pool-> os cache and then flush disk per second

Note: due to the problem of process scheduling policy, this "flush (flush to disk) operation once per second" does not guarantee 100% per second.

You can set this parameter according to the security of the business and the performance requirements, as shown in the following sync_binlog

2 、 sync_binlog

How often binary logs (binary log) are synchronized to disk. Every time binary log is written into sync_binlog, it is written to disk.

If autocommit is on, binary log is written once for each statement, otherwise it is written once for each transaction.

The default value is 0, which does not synchronize actively, but relies on the operating system itself to flush the file contents to disk irregularly.

Set to 1 is the safest, synchronizing binary log once after each statement or transaction, losing at most one statement or transaction log even in the event of a crash, but therefore the slowest.

Sync_binlog = N: the control is to refresh the binlog from binlog buffer to the underlying binlog file (that is, to the underlying disk)

N > 0 every time N SQL or N transactions are written to the binary log file, the data of the binary log file is flushed to disk

Number0 does not actively refresh the data of binary log files to disk, but is determined by the operating system.

In most cases, there are no strict requirements for data consistency, so sync_binlog will not be configured as 1. In order to pursue high concurrency and improve performance, you can set it to 100 or directly use 0.

Note that after Group Commit was introduced in MySQL 5.6,

The meaning of sync_binlog changes. Suppose it is set to 1000, which means that instead of doing fsync after 1000 transactions, it means 1000 transaction groups. In other words, when the setting sync_binlog=1,binlog is not down, the system crash will lose the corresponding last transaction group; if there are 10 transactions in this transaction group, then all 10 transactions will be lost.

How to check whether it belongs to a transaction group

Through mysqlbinlog, you can view the last_ committed value in the binlog log. If the value is the same, it indicates that it is in the same transaction group.

# INSERT INTO `wukong_ test`.`wukong`

# SET

# @ 1room3 / * INT meta=0 nullable=1 is_null=0 * /

# @ 2 VARSTRING cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc

# at 496468

# 170527 4:17:35 server id 12001 end_log_pos 496499 CRC32 0xd6e7f69f Xid = 5556

Com _ MIT _ blank /

# at 496499

# 170527 4:17:35 server id 12001 end_log_pos 496564 CRC32 0x28816d5c GTIDlast_committed=1845sequence_number=1846

SET @ @ SESSION.GTID_NEXT= '0a646c88-36e2-11e7-937dMerfa163ed7a7b1purl 3624mm /

# at 496564

# 170527 4:17:35 server id 12001 end_log_pos 496632 CRC32 0x03150d48 Query thread_id=1852 exec_time=0 error_code=0

SET timestamp 1495873055

BEGIN

3 、 write/read thread

Number of asynchronous IO threads

Innodb_write_io_threads=16

Innodb_read_io_threads=16

(this parameter needs to be added in the configuration file. Restarting the mysql instance takes effect.) the number of threads written by dirty pages. Increasing this parameter can improve write performance.

4 、 innodb_max_dirty_pages_pct

Maximum percentage of dirty pages, when the percentage of dirty pages in the system exceeds this value, INNODB will write to write the updated data in the page to the disk file. The default is 75, and it is difficult for popular SSD hard drives to reach this ratio. It can be adjusted between 75 and 80 according to the actual situation.

This parameter is too large, which will take a long time for instance recovery. If it is too small, it will refresh frequently and increase the burden on page_cleaner_thread, innodb_write_io_threads and cpu. Generally, the default value is used.

5 、 innodb_io_capacity=5000

The number of dirty pages at a time when dirty pages are flushed from the buffer. According to the capabilities of disk IOPS, it is generally recommended to set as follows:

SAS 200

SSD 5000

PCI-E 10000-50000

6. Innodb_flush_method=O_DIRECT (this parameter requires restarting the mysql instance to take effect)

Controls the opening and writing mode of innodb data files and redo log. There are three values: fdatasync (the default), Olympus DSYNCpender direct.

Fdatasync mode: when writing data, the write step does not need to be actually written to disk (it may be written to the operating system buffer and the completion will be returned). The real completion is the flush operation, and the buffer is handed over to the operating system to flush, and the metadata information of the file needs to be updated to disk.

O_DSYNC mode: log writing is done at write step (not through os buffer), while data file writing is done through fsync at flush step.

O_DIRECT mode: the data file is written directly from mysql innodb buffer to disk, and does not need to be buffered by the operating system, and the real completion is in flush, and the log is still buffered by OS.

From the figure, we can see that the advantage of O_DIRECT over fdatasync is to avoid double buffering. Innodb buffer pool itself is a buffer and does not need to be written to the buffer of the system, but there is a disadvantage that it is written directly to disk, so it is less efficient to read and write sequentially than fdatasync. So if the disk io pressure is not high, and if the system uses swap space, you can consider innodb_flush_method=O_DIRECT

In an environment with a large number of random writes, O_DIRECT is more efficient than fdatasync. If you write more sequentially, the default fdatasync is more efficient, because we now use the use of insert buffer cache (converted to sequential writing). Personally, I think the default value is better.

7. Set innodb_adaptive_flushing to ON (make it smarter to refresh dirty pages)

Affects the number of dirty pages refreshed per second

The rule changed from "refreshing 100dirty pages to disk when it is greater than innodb_max_dirty_pages_pct" to "determine the most appropriate number of dirty pages that need to be refreshed through the buf_flush_get_desired_flush_reate function to determine the speed at which redo logs are generated". Even if the proportion of dirty pages is less than innodb_max_dirty_pages_pct, a certain amount of dirty pages will be refreshed.

8.innodb_page_cleaners

MySQL 5.7enables concurrent refresh threads, and innodb_page_cleaners controls the number of refresh threads

Mysql > show variables like'i% cleaners'

+-+ +

| | Variable_name | Value |

+-+ +

| | innodb_page_cleaners | 1 | |

+-+ +

1 row in set (0.05sec)

Add innodb_page_cleaners= Num value to the configuration file my.cnf

The default is 1; the maximum can be 64, that is, 64 page cleaner threads will work concurrently to clean up dirty pages

9. Parameters for refreshing adjacent pages with innocent database flush _ neighbors

When a dirty page is refreshed, the Innodb storage engine detects all pages in the extent of the page, and if it is a dirty page, refresh it together. The benefit of this is obvious: multiple IO writes can be merged into a single IO operation through AIO, so this working mechanism has a significant advantage over traditional mechanical disks. As for the solid state disk, because of its ultra-high IOPS performance, it is recommended to set this parameter to 0, that is, to turn off this feature. Personally, if io is not a performance bottleneck, it is not recommended to turn on this function, because it may refresh the not-so-dirty page, and then it will soon become a dirty page.

10. Innodb_adaptive_flushing_method is set to keep_average

Affect checkpoint, more averagely calculate and adjust the speed of scrubbing dirty pages, and make the necessary flush. (this variable is a variable under mysql derivative version Percona Server, and native mysql does not exist.)

11 、 innodb_stats_on_metadata=OFF

Turn off some index statistics generated by accessing the following table of the information_ schema library.

When the mysql instance is restarted, mysql will randomly io the data to traverse all tables to sample for statistical data. This is not often used in practical use, so it is recommended to close it.

11 、 innodb_change_buffering=all

Change buffer can be thought of as an upgrade of insert buffer. You can buffer dml operations-insert, delete, and update. They are: insert buffer, delete buffer, and purge buffer.

This parameter is used to enable various buffer options. Available values are: inserts, delete, purges, changes, all, none;, where changes means inserts and delete,all are enabled, all are enabled, none means none is enabled, default is all

When the page corresponding to the data of the updated / inserted nonclustered non-unique index is not in memory (updating the nonclustered non-unique index usually results in a random IO), it is put into an insert buffer, and when the page is subsequently read into memory, the records of these changes are merge into the page. Background threads also do merge operations when the server is idle.

Since the advantage of merge is mainly used to reduce io, but for some scenarios, fixed data will not be modified many times, so it is not necessary to enable change_buffering for update / insert operation. If enabled, it will only take up the space and processing power of buffer_pool. This parameter should be configured according to the actual business environment.

12 、 innodb_change_buffer_max_size

Starting with Innodb version 1.2.x, the maximum amount of memory used by change buffer can be controlled by the parameter innodb_change_buffer_max_size; the default value is 25, which means a maximum of 25% of the buffer pool memory space is used, and the maximum valid value of this parameter is 50%.

If you use too much, then when MySQL server crash, it will take a long time to recover (merger operation)

13. Innodb_old_blocks_pct initialization defaults to 37

(page 27 of innodb Architecture) Note that because the new page is placed in the position of the trailer 3 page 8 (that is, belongs to sublist of old blocks), the sublist of new blocks can only come from the movement of sublist of old blocks.

The innodb cache pool has two areas, one is sublist of old blocks to store data that is not often accessed, and the other is sublist of new blocks to store data that is often accessed

The innodb_old_blocks_pct parameter controls the number of entries to the sublist of old blocks area. Initialization defaults to 37. 0.

When accessing the data in the sublist of old blocks, the innodb_old_blocks_time parameter controls the number of microseconds after which the data is not immediately transferred to the sublist of new blocks area, but how many microseconds it will actually enter into the new area. This also prevents the data in the new area from being kicked out immediately.

So there are two situations:

1. If you do a lot of full table scans in your business, you can reduce the innodb_old_blocks_pct setting to the innodb_old_blocks_time time, prevent these useless query data from entering the old area, and try your best to keep the useful data cached in the new area from being wiped out immediately. (this is also a temporary method. A large number of full table scans need to optimize the sql and table index structure.)

2. If you don't do a lot of full table scans in your business, you can increase innodb_old_blocks_pct, reduce innodb_old_blocks_time time, cache useful query data in innodb buffer pool as much as possible, reduce disk io, and improve performance.

21 、 binlog_cache_size

Binary log buffer size: a transaction, when not committed (uncommitted), the resulting log is recorded in Cache; when the transaction commit (committed) needs to be committed, the log is persisted to disk.

If the setting is too large, it will consume memory resources (Cache is memory in essence). More attention should be paid to whether binlog_cache is global and is allocated exclusively by SESSION, that is to say, when a thread starts a transaction, Mysql will assign a binlog_cache to the SESSION.

How to tell if our current binlog_cache_size setting is all right?

Mysql > show status like 'binlog_%'

+-+-+ |

Variable_name | Value |

Binlog_cache_disk_use | 1425 |

| | Binlog_cache_use | 126945718 | |

2 rows in set (0.00 sec)

Mysql > select @ @ binlog_cache_size

+-+-+ |

@ @ binlog_cache_size

1048576

1 row in set (0.00 sec)

Binlog_cache_use indicates how many times the binlog_cache memory mode has been used, and Binlog_cache_disk_use indicates how many times the binlog_cache temporary file method has been used. When the corresponding Binlog_cache_disk_use value is relatively large, we can consider raising the corresponding binlog_cache_size value appropriately.

Two values for 22.innodb_file_per_table:

1: open independent tablespaces

0: if it is not enabled, shared tablespaces will be used

Advantages:

1) the data and indexes of each table will be stored in its own tablespace

2) it is possible to move a single table in different databases

3) Space can be reclaimed (except for drop table operation)

4) after deleting a large amount of data, you can use alter table TableName engine=innodb; to retract the unused space.

Using turncate table also shrinks the space.

5) for tables that use independent tablespaces, no matter how they are deleted, the fragmentation of the tablespaces will not seriously affect performance.

Disadvantages:

1) the increase of a single table is too large, such as more than 100 gigabytes, it may be better to use shared tablespaces!

Conclusion: shared tablespaces have few advantages in Insert operations. Nothing else performs as well as independent tablespaces. When independent tablespaces are enabled, please make a reasonable adjustment: innodb_open_files, because each table corresponds to one file, and more files need to be opened than shared tablespaces, so you need to appropriately increase the parameter of innodb_open_files and the limit of linux kernel parameter open files 1.

There are two ways to convert a shared tablespace to a separate tablespace:

1. First, make a logical backup, then modify the parameter innodb_file_per_table in the configuration file my.cnf to 1, and then import the logical backup after restarting the service.

two。 Modify the parameter innodb_file_per_table in the configuration file my.cnf to 1. After restarting the service, all innodb tables that need to be modified will be executed once: alter table table_name engine=innodb

After modification in the second way, the data in the tables in the original library will continue to be stored in ibdata1, and the newly added data will use independent tablespaces.

23.sync_relay_log:

Sync_relay_log: the default is 10000, which means that every 10000 sync_relay_log events are flushed to disk. 0 means no refresh, which is controlled by the cache of OS. If N means n sync_relay_log events will be flushed to disk.

If the value of this variable is greater than 0, the MySQL server synchronizes its relay log to disk (using fdatasync ()) after every sync_relay_log events are written to the relay log. Setting this variable takes effect for all replication channels immediately, including running channels

When set to 1, every time the slave's I / O thread receives the binlog log sent by master, it will be written to the system buffer and then brushed into the relay log relay log, which is the safest, because in the event of a crash, you will lose at most one transaction, but it will cause a large number of I and O on the disk.

When set to 0, it is not immediately brushed into the relay log, but it is up to the operating system to decide when to write, although the security is reduced, but a large number of disk Ihop O operations are reduced. This value defaults to 10000 and can be modified dynamically.

24. Then introduce the parameter sync_binlog:

Sync_binlog = N: the control is to refresh the binlog from binlog buffer to the underlying binlog file (that is, to the underlying disk)

N > 0 for every N SQL or N transactions written to the binary log file (when the group commits, it is actually n group transactions), the data from the binary log file is flushed to disk

Number0 does not actively refresh the data of binary log files to disk, but is determined by the operating system.

25. Increase local ports to cope with a large number of connections

one

Echo '1024 65000' > / proc/sys/net/ipv4/ip_local_port_range

This parameter specifies the allocation range of the port, which is a restriction on outward access. Port 3306, which mysql listens on by default, will not affect even if there are multiple request links. However, because mysql is a high-memory, high-cpu, high-io application, it is not recommended to mix multiple mysql applications on the same machine. Even if the business volume is small, it can be better achieved by reducing the configuration of a single machine and the coexistence of multiple machines.

twenty-six。 Increase the number of links in the queue

one

Echo '1048576' > / proc/sys/net/ipv4/tcp_max_syn_backlog

The larger the number of queues that establish links, the better, but from another point of view, connection pooling should be more appropriate in the real world to avoid the performance consumption caused by repeatedly establishing links. Using connection pooling, the number of links is more controllable at the application level.

twenty-seven。 Set the link timeout

one

Echo'10'> / proc/sys/net/ipv4/tcp_fin_timeout

The main purpose of this parameter is to reduce the duration of resources consumed by TIME_WAIT. Especially for the server of http short links or mysql without connection pool, the effect is more obvious.

28.linux kernel semaphore

The default setting of the linux kernel semaphore is too small, causing a lot of waiting.

The default values are as follows:

# cat / proc/sys/kernel/sem

250 32000 32 128

Description:

The first column represents the maximum number of semaphores in each signal set.

The second column represents the total number of system-wide maximum semaphores.

The third column represents the maximum number of system operations when each signal occurs.

The fourth column represents the system-wide maximum total number of signal sets.

Adjust the third column a little larger to refer to the data on the network

Echo "kernel.sem=250 32000 100128" > > / etc/sysctl.conf

Then sysctl-p

Restart mysql

If the setting is too small, when a large number of concurrency occurs, an error will be reported in the error log: InnoDB: Warning: a long semaphore waitworthy errors!

This is the end of the content of "what are the parameters that need to be concerned about the performance of MySQL". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.