MySQL Tutorial: I/O Memory Optimization

MySQL Tutorial: I/O Memory Optimization

Mysql Ver 14.14 Distrib 5.6.19, for Linux (i686) using EditLine wrapper

I. mysql directory files

Ibdata1: The system tablespace contains data dictionaries, rollback logs, and undolog.

(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

Ii. 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 request (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 thread)

I/O thread 8 state: waiting for I/o request (write thread)

I/O thread 9 state: waiting for I/o request (write thread)

Pending 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: 0

Pending flushes (fsync) log: 0; buffer pool: 0

393 OS file reads, 5 OS file writes, 5 OS fsyncs

0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s

All innodb background 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, and write thread.

After MySQL 5.6.10, the default thread processing model is run to connect each client to a thread statement. As more and more clients connect to the server and execute statements, the overall performance is reduced. The thread pool plug-in is provided to reduce overhead and improve the performance of other thread processing modes. This plug-in provides a thread pool to improve server performance by effectively managing a large number of client connections of statement execution threads.

The InnoDB Plugin version increases the number of default IO threads. The default read thread and write thread are increased to four, respectively, and the innodb_file_io_threads parameter is not used, but innodb_read_io_threads and innodb_write_io_threads parameters are used respectively.

Thread Pool solves several single-thread problems for each connection model

• Too plugin 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 implements threads executing in parallel, context switching overhead is 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 can handle and that is appropriate for the server host on which MySQL is executing.
• Too implements transactions executing in specified increases resource contention. In InnoDB, this increases the time spent holding central mutexes. The thread pool controls when transactions start to ensure that not too implements execute in parallel.

Iii. mysql file access process

Transaction from Network

Iii. Some parameters that affect IO/memory

1. Set innodb_flush_log_at_trx_commit to 2.

This parameter indicates how the transaction log (ib_logfile0, ib_logfile1) is written to the log buffer.

= 0 mysql crash will be lost, with the best performance

Buffer pool-> log buffer wirte OS cache per second & flush Disk

= 1 will not be lost, low efficiency

Buffer pool-> log buffer write OS cache & flush disk each time

= 2 even if mysql crashes, data will not be lost.

Buffer pool-> OS cache flush disk per second

Note: Due to a process scheduling policy issue, this "flush (flush to disk) operation per second" does not guarantee 100% "per second

2. sync_binlog

The frequency at which binary logs are synchronized to the disk. Each time binary logs are written to sync_binlog, they are flushed to the disk.

If autocommit is enabled, each statement writes binary log once; otherwise, each transaction is written once.

The default value is 0, and the file content is flushed to the disk from time to time without active synchronization.

It is the safest to set to 1. It synchronizes binary logs once after each statement or transaction. Even when a crash occurs, the log of one statement or transaction is lost at most, but it is also the slowest.

In most cases, data consistency is not strictly required, so sync_binlog is not configured as 1. To achieve high concurrency and improve performance, you can set it to 100 or directly use 0.

3. write/read thread

Asynchronous IO thread count

Innodb_write_io_threads = 16
Innodb_read_io_threads = 16

(This parameter must be added to the configuration file to restart the mysql instance to take effect.) Increase the number of threads written to dirty pages to improve the write performance.

4. innodb_max_dirty_pages_pct

Maximum dirty page percentage. When the percentage of dirty pages in the system exceeds this value, INNODB writes the updated data on the page to the disk file. The default value is 75, which is hard to achieve by the popular SSD hard drive. Adjustable between-80 based on actual conditions

5. innodb_io_capacity = 5000

The number of dirty pages refreshed at one time when the dirty pages are refreshed from the buffer zone. We recommend that you set the following settings based on the disk IOPS:

SAS 200
SSD 5000
PCI-E 10000-50000

6. innodb_flush_method = O_DIRECT (this parameter must be effective after the mysql instance is restarted)

Control the Enable and fl modes of innodb data files and redo logs. There are three values: fdatasync (default), O_DSYNC, and O_DIRECT.

• Fdatasync mode: When writing data, the write step does not need to be written to the disk to complete (it may be written to the buffer of the operating system and the result will be returned to be completed). The actual completion is the flush operation, buffer is handed over to the operating system for flush, and the metadata information of the file also needs to be updated to the disk.
• O_DSYNC mode: log writing is completed in the write step, and data file writing is completed in the flush step through fsync.
• O_DIRECT mode: data file write operations are directly from mysql innodb buffer to the disk, but not through the operating system buffer, and the real completion is also in the flush step, logs still need to be buffered by OS.

The figure shows that the advantage of O_DIRECT over fdatasync is that it avoids double buffering. the innodb buffer pool itself is a buffer and does not need to be written to the system buffer, however, the disadvantage is that it is directly written to the disk, so it is less efficient than sequential read/write of fdatasync.

In a large number of random write environments, O_DIRECT is more efficient than fdatasync. If there are more sequential writes, the default fdatasync is more efficient.

7. Set innodb_adaptive_flushing to ON to make refreshing dirty pages smarter)

Number of dirty pages refreshed per second

The rule is changed from "Refresh 100 dirty pages to disk when innodb_max_dirty_pages_pct is greater" to "judge the redo log generation speed through the buf_flush_get_desired_flush_reate function and determine the optimum number of dirty pages to be re ", A certain amount of dirty pages are refreshed even if the proportion of dirty pages is smaller than that of innodb_max_dirty_pages_pct.

8. Set innodb_adaptive_flushing_method to keep_average.

This variable affects checkpoint, calculates more evenly, adjusts the speed of dirty page refreshing, and flush necessary. (This variable is a variable under mysql-derived Percona Server, and does not exist in native mysql)

9. innodb_stats_on_metadata = OFF

Disable the index statistics generated by accessing the following table of the information_schema database.

After the mysql instance is restarted, mysql will randomly retrieve io data and traverse all the tables for sampling statistics. This is not used in actual use. We recommend that you disable it.

10. innodb_change_buffering = all

When the page corresponding to the updated/inserted non-clustered index data is not in memory (the update operation on Non-clustered index usually leads to random IO ), it will be placed in an insert buffer. When the page is read to the memory, the changes will be recorded in the merge page. When the server is idle, the background thread also performs merge operations.

Because we mainly use merge's advantages to reduce io, but do not modify fixed data multiple times in some scenarios, we do not need to enable change_buffering for update/insert operations, if it is enabled, it only occupies the buffer_pool space and processing capacity. This parameter must be configured based on the actual business environment.

11. innodb_old_blocks_time = 1000

So that the length of the Block in the old sublist is 1 s and will not be transferred to the new sublist. This avoids the contamination of the Buffer Pool BP and can be considered as a long chain table. It is divided into two parts: young and old. By default, old accounts for 37% of the size (configured by innodb_old_blocks_pct ). The Page near the top indicates that it was recently accessed. The Page near the end indicates that it has not been accessed for a long time. The intersection of the two parts becomes the midpoint. Whenever a new Page needs to be loaded to BP, the page will be inserted to the midpoint location and declared as old-page. When the old page is accessed, It is promoted to the top of the linked list and marked as young.

Because the operation of table scan is to load page first, and then immediately trigger an access. Therefore, when innodb_old_blocks_time = 0, the pages required by table scan are not read as young pages and added to the top of the linked list. Some pages that are not frequently used will be squeezed out by BP, resulting in disk IO generated by subsequent SQL statements, resulting in slow response.

At this time, although the pages accessed by mysqldump will be constantly loaded at the top of the LRU, the high-frequency hotspot data access will seize the page to the top of the LRU at a faster speed. As a result, the page loaded by mysqldump will be quickly flushed and immediately evict (obsolete ). Therefore, time = 0 or 1000 does not have a great impact on access in such a stressful environment, because the data of dump cannot compete for hotspot data. Not only dump, but also big data operations.

12. binlog_cache_size

Binary log buffer size: logs generated when a transaction is not committed (uncommitted) are recorded in the Cache. When a transaction is committed (committed, the logs are persistent to the disk.

If the value is too large, memory resources will be consumed (the Cache is essentially memory). Note that the binlog_cache is global and is allocated exclusively by SESSION, that is to say, when a thread starts a transaction, Mysql will allocate a binlog_cache for this SESSION.

How can we determine whether our current binlog_cache_size settings are correct?

Mysql> show status like 'binlog _ % ';

+ ----------------------- + ----------- +

| Variable_name | Value |

+ ----------------------- + ----------- +

| Binlog_cache_disk_use | 1425 |

| Binlog_cache_usage | 126945718 |

+ ----------------------- + ----------- +

2 rows in set (0.00 sec)

 

Mysql> select @ binlog_cache_size;

+ --------------------- +

| @ Binlog_cache_size |

+ --------------------- +

| 1, 1048576 |

+ --------------------- +

1 row in set (0.00 sec)

Binlog_cache_use indicates the number of times binlog_cache memory is used, and Binlog_cache_disk_use indicates the number of times binlog_cache temporary files are used.

13. innodb_file_per_table

Innodb_file_per_table = 1

Independent tablespace

Advantages:

• Each table's data and indexes are stored in its own tablespace.
• You can move a single table in different databases.
• Space can be recycled (except for the drop table Operation)
• After deleting a large amount of data, you can use: alter table TableName engine = innodb; to reduce unnecessary Space
• Using turncate table also causes space Shrinkage
• No matter how you delete a table that uses an independent tablespace, The tablespace fragments will not seriously affect the performance.

Disadvantage: A large increase in a single table, such as over 100 GB

Conclusion: Shared tablespace has few advantages in Insert operations. Other independent tablespaces do a good job. When the independent tablespace is enabled, properly adjust the following: innodb_open_files, InnoDB Hot Backup (cold Backup) tablespace cp will not face a lot of useless copy. In addition, innodb hot backup and tablespace management commands can be used to achieve single-current movement.

14. add local ports to deal with a large number of connections

Echo '1970 100'>/proc/sys/net/ipv4/ip_local_port_range

This parameter specifies the port allocation range, which is a restriction on outbound access. Mysql listens to port 3306 by default, even if there are multiple request links, it will not be affected. However, because mysql is a high-memory, high-cpu, and high-io application, we do not recommend that you use mysql on the same machine. Even if the business volume is small, it is better to reduce the configuration of a single machine and achieve coexistence of multiple machines.

15. Increase the number of queue connections

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

The larger the number of queues for establishing connections, the better. But from another perspective, it is more appropriate to use the connection pool in the actual environment to avoid performance consumption caused by repeated connection establishment. With the connection pool, the number of connections is more controllable at the application level.

16. Set the link timeout time

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

This parameter is used to reduce the resource duration occupied by TIME_WAIT. Especially for servers with http transient links or mysql that do not use a connection pool, the effect is obvious.