"MySQL" Some optimizations on io/memory

Here is the MySQL Ver 14.14 distrib 5.6.19, for Linux (i686) using Editline Wrapper

One, MySQL directory file

IBDATA1: System table space contains data dictionary, rollback log/undolog, etc.

(Insert buffer segment/double write Segment/rollback segment/index segment/dictionary segment/undo segment)

Ib_logfile0/ib_logfile1: Transaction log/redolog

Mysql-relay-bin: Relay Log

Binarylog: Binary Log

General_log.log: General Log

Mysql_error.log: Error Log

Slow_query.log: Slow Log

. IBD: User tablespace-Data file (insert Buffer bitmap page/leaf page segment/none leaf page segment)

Innodb buffer Pool (memory): Undo Page/insert Buffer Page/adaptive Hash index/index page/lock Info/data dictionary

Second, MySQL thread

FILE IO

--------FILE I/o--------I/O thread 0 state:waiting for I/O request (insert buffer thread) I/O thread 1 state:waiting for I/O request (log thread) I/O thread 2 state:waiting for I/O request (read thread) I/O thread 3 state:waiting for I/O Reque  St (read thread) I/O thread 4 state:waiting for I/O request (read thread) I/O thread 5 state:waiting for I/O request (read thread) I/O thread 6 state:waiting for I/O request (write thread) I/O thread 7 state:waiting for I/O request (write Threa d) I/O thread 8 state:waiting for I/O request (write thread) I/O thread 9 state:waiting for I/O request (write thread) Pend ing normal AIO reads:0 [0, 0, 0, 0], Aio writes:0 [0, 0, 0, 0], ibuf aio reads:0, log I/O ' s:0, sync I/O ' s:0pending Flushes (Fsync) log:0; Buffer pool:0393 OS file reads, 5 OS file writes, 5 os fsyncs0.00 reads/s, 0 avg bytes/read, 0.00 WRITES/S, 0.00 FSYNCS/S

InnoDB Background All threads

| Thread/sql/main | BACKGROUND | YES | | Thread/innodb/io_handler_thread | BACKGROUND | YES | | Thread/innodb/io_handler_thread | BACKGROUND | YES | | Thread/innodb/io_handler_thread | BACKGROUND | YES | | Thread/innodb/io_handler_thread | BACKGROUND | YES | | Thread/innodb/io_handler_thread | BACKGROUND | YES | | Thread/innodb/io_handler_thread | BACKGROUND | YES | | Thread/innodb/io_handler_thread | BACKGROUND | YES | | Thread/innodb/io_handler_thread | BACKGROUND | YES | | Thread/innodb/io_handler_thread | BACKGROUND | YES | | Thread/innodb/io_handler_thread | BACKGROUND | YES | | Thread/innodb/srv_master_thread | BACKGROUND | YES | | Thread/innodb/srv_purge_thread | BACKGROUND | YES | | Thread/innodb/srv_monitor_thread | BACKGROUND | YES | | Thread/innodb/srv_Error_monitor_thread | BACKGROUND | YES | | Thread/innodb/srv_lock_timeout_thread | BACKGROUND | YES | | Thread/innodb/page_cleaner_thread | BACKGROUND | YES | | Thread/sql/signal_handler | BACKGROUND | YES | | Thread/sql/slave_sql | BACKGROUND | YES | | Thread/sql/slave_io | BACKGROUND |　 YES |

The IO threads are insert buffer thread, log thread, read thread, write thread, respectively.

After MySQL 5.6.10, the default threading model uses a thread statement that executes each client connection. As more and more clients connect to the server and execute statements, overall performance degrades. Thread pool plug-in provides the processing mode of other threads designed to reduce overhead and improve performance. The plug-in implements a thread pool that improves server performance by effectively managing a large number of client connections to the execution thread of the statement.
The InnoDB plugin version began to increase the number of default IO thread, with the default read thread and write thread growing to 4, and no longer using the Innodb_file_io_threads parameter, but using InnoDB respectively _read_io_threads and Innodb_write_io_threads parameters. Thread pooling solves several problems of single threaded solution for each connection model

• Too Many thread stacks make CPU caches almost useless in highly parallel execution workloads. The thread pool promotes thread stack reuse to minimize the CPU cache footprint.

• With too many threads executing in parallel, context switching overhead are high. This also presents a challenging task to the operating system scheduler. The thread pool controls the number of active threads to keep the parallelism within the MySQL server at a level that it C An handle and so is appropriate for the server host on which MySQL is executing.

• Too many transactions executing in parallel increases resource contention. In InnoDB, this increases the time spent holding central mutexes. The thread pool controls when transactions start to ensure, that is too many execute in parallel.

Third, MySQL access to the file flow

Transaction from the network

Iii. some parameters that affect io/memory 1、innodb_flush_log_at_trx_commit 设置为2

This parameter refers to the way the transaction log (IB_LOGFILE0, Ib_logfile1) is written to log buffer

=0 MySQL crash is lost, the best performance

Buffer pool, log buffer per second Wirte OS cache & Flush Disk

=1 not lost, low efficiency

Buffer pool, log buffer, every write OS cache & Flush Disk

=2 data is not lost even if MySQL crashes

Buffer pool, OS cache flush disk per second

Note: This "flush (brush to disk) operation per second" is not guaranteed to be 100% per second due to a process scheduling policy issue 2, Sync_binlogThe frequency at which binary logs (binary log) are synchronized to disk. Binary log per write sync_binlogTimes, Brush writes to disk.

If autocommit turned on, each statement is written once by binary log, otherwise each transaction is written once.

The default value is 0 not to actively synchronize, but relies on the operating system itself to flush the contents of the file to disk periodically

is set to be 1 the safest, synchronizing a binary log once per statement or transaction, even if it crashes, it loses at most one statement or transaction log, but is therefore also the slowest.

In most cases, the consistency of the data is not strictly required, so it will not be sync_binlog configured 1， to pursue high concurrency, improve performance, can be set to 100 or directly with0

3. Write/read Thread

Number of asynchronous IO threads

Innodb_write_io_threads=16
Innodb_read_io_threads=16

(该参数需要在配置文件中添加，重启mysql实例起效)Dirty page Write the number of threads, increase this parameter can improve write performance

4、innodb_max_dirty_pages_pct

最大脏页百分数，When the percentage of dirty pages in the system exceeds this value, InnoDB writes to write the updated data in the page to the disk file. By default 75, it is hard to reach this ratio for SSD drives that are generally popular today. Can be adjusted according to the actual situation between 75-80

5、innodb_io_capacity=5000

The number of dirty pages refreshed one time when dirty pages are flushed from the buffer. The following are generally recommended settings based on the capacity of disk IOPS:

SAS 200
SSD 5000
Pci-e 10000-50000

6、innodb_flush_method=O_DIRECT(该参数需要重启mysql实例起效)

Controls the open, brush-write mode of the InnoDB data file and redo log. There are three values: Fdatasync (Default), O_dsync,o_direct.

• Fdatasync mode: When writing data, write this step does not need to really write to the disk is completed (may write to the operating system buffer will return to completion), the real completion is the flush operation, buffer to the operating system to flush, Also, the metadata information for the file needs to be updated to disk.

• O_dsync mode: The Write log operation is done in write, and the data file is written in flush this step through fsync.

• O_direct mode: The data file write operation is directly from the MySQL InnoDB buffer to the disk, and does not pass through the operating system buffering, and the real completion is also in the flush this step, the log still goes through the OS buffering.

Through the figure can be seen o_direct compared to fdatasync advantage is to avoid double buffering, itself InnoDB buffer pool is a buffer, do not need to write to the system buffer, but there is a disadvantage is directly written to the disk, So the order of reading and writing is less efficient than fdatasync.

In a large number of random write environments, O_direct is more efficient than fdatasync, more sequential write, or the default Fdatasync more efficient.

7、innodb_adaptive_flushing 设置为 ON （使刷新脏页更智能）

Affects the number of dirty pages refreshed per second

The rule is changed from "100 Dirty pages to disk" when the original "is greater than innodb_max_dirty_pages_pct" to "through Buf_flush_get_desired_flush_ The reate function determines the rate at which the redo log is generated to determine the most appropriate number of dirty pages to be refreshed, even if the dirty page scale is less than innodb_max_dirty_pages_pct, a certain amount of dirty pages is refreshed.

8、innodb_adaptive_flushing_method 设置为 keep_average

Affects checkpoint, the more average calculation adjusts the speed of the brush dirty pages, making the necessary flush. (this variable is a variant of MySQL derived version Percona server, native MySQL does not exist)

9、innodb_stats_on_metadata=OFF

Turn off some index statistics that are generated by accessing the following table in the INFORMATION_SCHEMA library.

When the MySQL instance is restarted, MySQL randomly iterates through all the tables to sample the data, which is not used much in practice and is recommended to close.

10、innodb_change_buffering=all

When the page that corresponds to the updated/inserted nonclustered index is not in memory (an update operation on a nonclustered index usually brings random IO), it is placed into an insert buffer, and the changed records are then merge into the page when the page is subsequently read into memory. The background thread also does a merge operation when the server is relatively idle.

Because the main use of the advantages of the merge to reduce IO, but for some scenarios do not make fixed data changes many times, here do not need to update/insert operation to open change_buffering, if the opening is only unnecessary to occupy the buffer_pool space and processing power. This parameter is configured according to the actual business environment.

11、innodb_old_blocks_time=1000

Block in old sublist for a length of 1s, will not be transferred to the new sublist, avoid buffer pool contaminated BP can be considered a long linked list. is divided into young and old two parts, where old defaults to 37% of the size (configured by innodb_old_blocks_pct). A page near the top indicates a recent visit. A page near the end indicates that it has not been accessed for a long time. And the intersection of these two parts became midpoint. Whenever a new page needs to be loaded into BP, the page is inserted into the midpoint location and declared as Old-page. When the page of the old part is accessed, the page is promoted to the top of the list, identified as young.

Because the operation of the table scan is to load the page first, and then immediately triggers a visit. So when innodb_old_blocks_time = 0 o'clock, the page that is required for table scan is not read as the young page is added to the top of the list. Some less frequently used page will be squeezed out of BP, so that the subsequent SQL will generate disk IO, resulting in slower response times.

At this point, although the page accessed by mysqldump is constantly loading on the LRU top, the high-frequency hotspot data access will preempt the page to the LRU top again at a faster rate. The page that causes Mysqldump to load will be quickly brushed down and immediately be evict (obsolete). As a result, time=0 or 1000 does not have a significant impact on access to this stressful environment because the dump data simply grabs the hotspot data. Not only dump, but also when big data is being manipulated.

12, binlog_cache_size

binary Log buffer size : A transaction that, when not committed (uncommitted), logs the resulting log to the cache, and persists the log to disk until the transaction commit (committed) needs to be committed.

Set too big, will be compared to consume memory resources (cache essence is memory), more need to pay attention to is: Binlog_cache is not a global, is the session for the Unit exclusive distribution, that is, when a thread started a transaction, MySQL will assign a binlog_cache to this session.

How to judge our current binlog_cache_size setup?

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)

Run binlog_cache_use indicates how many times Binlog_cache memory has been used, binlog_cache_disk_use indicates how many times Binlog_cache temporary files have been used

13, innodb_file_per_table

Innodb_file_per_table=1

Stand-alone table space

Advantages:

• Data and indexes for each table will exist in the table space of your own

• You can implement a single table to move through different databases

• Space can be reclaimed (except for the drop table operation)

• Deleting large amounts of data can be done by: ALTER TABLE TableName ENGINE=INNODB; retract unused space

• Using Turncate table also shrinks the space

• For tables that use stand-alone table spaces, no matter how they are deleted, the fragmentation of the tablespace does not affect performance too severely

Disadvantage: single table increased too large, such as more than 100 g

conclusion: shared tablespaces have few advantages over insert operations. Others do not have a separate table space to perform well. When enabling stand-alone table spaces, make a reasonable adjustment: Innodb_open_files, InnoDB hot Backup (cold) tablespace CP will not face a lot of useless copy. And the InnoDB hot backup and table space management commands can be used to achieve single-move.

14、增加本地端口，以应对大量连接

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

This parameter specifies the allocation range for the port, which is the limit for out-of-access. MySQL default listener 3306 Port Even if there are multiple request links, it will not affect. However, since MySQL is a high-memory, high-CPU, high-io application, it is not recommended how much should be used for MySQL mashup on the same machine. Even if the volume of business is small, it can also be achieved by reducing the configuration of a single machine, multiple machines coexistence to achieve better.

15、增加队列的链接数

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

The larger the number of linked queues, the better, but from another point of view, it should be more appropriate to use a connection pool in the actual environment, avoiding the performance cost of repeatedly establishing links. With connection pooling, the number of links can be more manageable from the application level.

16、设置链接超时时间

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

This parameter is primarily intended to reduce the amount of resource time that time_wait occupies. Especially for HTTP short link service side or MySQL does not adopt connection pool effect is obvious.

Reference articles

Http://dev.cmcm.com/archives/107/comment-page-1

http://www.zhdba.com/mysqlops/2012/05/24/mysql-io/

http://blog.itpub.net/22664653/viewspace-1063134/

http://liyangliang.me/posts/2014/03/innodb_flush_log_at_trx_commit-and-sync_binlog/

Http://www.cnblogs.com/snifferhu/p/4736479.html

Http://www.cnblogs.com/xuanzhi201111/p/4040681.html

http://mysqllover.com/?p=636

"MySQL" Some optimizations on io/memory