Mysql IO Memory Optimization _mysql

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

A, MySQL directory files

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: Regular 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 (RAM): Undo Page/insert Buffer Page/adaptive Hash index/index page/lock Info/data

Second, MySQL thread

FILE IO

--------
FILE I/o
--------
I/O thread 0 state:waiting for I/O request (insert buffer thread)
I/O Threa D 1 state:waiting for I/O request (log thread)
I/O thread 2 state:waiting for I/O request (read thread)
I/O thre Ad 3 state:waiting for I/O request (read thread)
I/O thread 4 state:waiting for I/O request (read thread)
I/o th Read 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
39 3 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

InnoDB all threads in the background

| 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 |

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

After the MySQL 5.6.10, the default threading model uses a thread statement that executes each client connection. Overall performance is reduced as more and more clients connect to the server and execute statements. The thread pool plug-in is provided to reduce overhead and improve performance for other threads in the processing mode. The plug-in implements a thread pool that improves server performance by effectively managing a large number of client connections to the statement execution thread.

InnoDB plugin version began to increase the number of default IO thread, the default read thread and write thread were increased to 4, and the innodb_file_io_threads parameters were no longer used, but they were used separately InnoDB _read_io_threads and Innodb_write_io_threads parameters.

Thread pooling solves several problems of single thread for each connection model

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

With the too many threads executing in parallel, the 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 in a level that it C An handle and this is appropriate for the server host in which the MySQL is executing.

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

Third, MySQL access to file flow

Transaction from the network

Iii. some parameters that affect io/memory

1, Innodb_flush_log_at_trx_commit set to 2

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

=0 MySQL crash is lost, best performance

Buffer pool-> Log buffer per second Wirte OS cache & Flush Disk

=1 not lost, inefficient

Buffer pool-> Log buffer per write OS cache & Flush Disk

=2 will not lose data even if MySQL crashes

Buffer pool-> OS cache flush disk per second

Note: This "perform flush (brush to disk) operation per second" is not guaranteed to be 100% per second due to process scheduling policy issues

2, Sync_binlog

The frequency at which binary logs (binary log) are synchronized to disk. Binary log writes to disk after each write Sync_binlog time.

If the autocommit is turned on, each statement is written once binary log, or each transaction is written once.

The default value is 0, not active synchronization, and relies on the operating system itself to periodically flush the contents of the file to disk

Set to 1 safest, synchronize the binary log once for each statement or transaction, even if the log of a statement or transaction is lost at most when it crashes, but so is the slowest.

In most cases, the consistency of the data is not very stringent requirements, so the Sync_binlog will not be configured to 1, in order to pursue high concurrency, improve performance, can be set to 100 or directly with 0

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, restart the MySQL instance to start) the number of threads written by dirty pages, which can be increased to improve write performance

4, innodb_max_dirty_pages_pct

The maximum percentage of dirty pages, 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 difficult to achieve this ratio in the popular SSD drive. Can be adjusted according to the actual situation between 75-80

5, innodb_io_capacity=5000

Refreshes the number of dirty pages at a time when a dirty page is refreshed from a buffer. The general recommendation for capacity based on disk IOPS is as follows:

SAS 200
SSD 5000
Pci-e 10000-50000

6, Innodb_flush_method=o_direct (this parameter needs to restart the MySQL instance to be effective)

Control InnoDB data files and redo log open, brush-write mode. has 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 to complete (may be written to the operating system buffer will return to complete), the real completion is flush operation, buffer to the operating system to flush, And the file's metadata information needs to be updated to disk as well.
O_dsync mode: The Write log operation is done in writing, and the write of the data file is done in flush this step through fsync.

O_direct mode: The data file write operation is directly from the MySQL InnoDB buffer to disk, and not through the operating system buffer, and the real completion is flush this step, the log or through the OS buffer.

The graph shows that the advantage of o_direct compared to Fdatasync is that it avoids double buffering, itself InnoDB buffer pool, which does not need to be written to the system buffer, but has a disadvantage because it is written directly to the disk. So it's less efficient to read and write sequentially than Fdatasync.

O_direct is more efficient than fdatasync in a large number of randomly written environments, with more sequential writes, or by default Fdatasync.

7, Innodb_adaptive_flushing set to ON (make refreshing dirty pages more intelligent)

Affects the number of dirty pages refreshed per second

Rules are refreshed from 100 dirty pages to disk when the original "is greater than innodb_max_dirty_pages_pct" into "through the Buf_flush_get_desired_flush_ reate function to determine redo log generation speed determines the most appropriate number of dirty pages to refresh, even if the dirty page ratio is less than innodb_max_dirty_pages_pct will refresh a certain amount of dirty pages.

8, Innodb_adaptive_flushing_method set to Keep_average

Affect checkpoint, more average calculation adjusts the speed of the brush dirty page, carry out the necessary flush. (this variable is a variant of the MySQL derivative 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 will randomly IO fetch data through all the tables to sample statistics, the actual use is not much, it is recommended to close.

10, Innodb_change_buffering=all

When the data for the updated/inserted nonclustered index is not in memory (update operations on nonclustered indexes usually bring random IO), will be placed in an insert buffer, and the changed records will be added to the page when the page is read in memory. The background thread also does the merge operation when the server is more idle.

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

11, innodb_old_blocks_time=1000

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

Because the action of table scan is to load page first, then an access is immediately triggered. So when innodb_old_blocks_time = 0 o'clock, the page that is required by the table scan is added to the top of the list as young page. And some of the less frequent page will be squeezed out of BP, so that the subsequent SQL will generate disk IO, resulting in slow response speed.

At this point, although mysqldump access page will continue to load at the top of the LRU, but the High-frequency hotspot data access will be faster to the page to preempt the LRU top. This results in the mysqldump loaded page being quickly brushed down and immediately evict (eliminated). As a result, time=0 or 1000 will not have a significant impact on the access to this pressure environment because the dump data simply preempt hotspot data. Not only dump, but also when large data operations are done.

12, Binlog_cache_size

Binary Log buffer size: A transaction that is generated when there is no commit (uncommitted), is recorded in the cache, and the log is persisted to disk when a transaction submission (committed) needs to be submitted.

setting is too big, will compare consumes memory resources (cache essence is memory), more need to note is: Binlog_cache is not the global, is by the session for the unit exclusive allocation, that is, when a thread to start a transaction, MySQL will assign a binlog_cache for 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 |
+---------------------+

The operation Binlog_cache_use indicates how many times the Binlog_cache memory mode was used, binlog_cache_disk_use how many times the Binlog_cache temp file was used

13, Innodb_file_per_table

Innodb_file_per_table=1

Standalone table space

Advantages:

Data and indexes for each table exist in their own table spaces

Can be implemented to move a single table in a different database

Space can be reclaimed (except drop table operations)

After you delete a large amount of data, you can pass: ALTER TABLE TableName ENGINE=INNODB, retract unused space

Using Turncate table also shrinks the space

For tables that use isolated table spaces, no matter how they are deleted, the fragmentation of the tablespace is not too serious to affect performance

Disadvantages:

Single table increased too large, such as more than 100 g

Conclusion: Shared tablespace has few advantages in insert operation. Others do not perform well in their own table space. When enabling standalone tablespace, adjust it appropriately: Innodb_open_files, InnoDB hot Backup (Lengbei) Table space CP does not face a lot of useless copy. And the use of InnoDB hot backup and table space management commands can realize the single move.

14, increase the local port to cope with a large number of connections

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

This parameter specifies the port's allocation range, which is the limit for outward access. MySQL's default 3306-port listener is not affected, even if there are multiple request links. However, because MySQL is a high memory, high CPU, high IO application, it is not recommended to apply the number of MySQL mixed on the same machine. Even if the volume of business is not large, you can reduce the configuration of a single machine, multiple machines coexist to achieve better.

15, increase the number of links to the queue

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

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

16, set the link timeout time

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

This parameter is primarily intended to reduce the length of resources that time_wait occupies. Especially for HTTP short link server or MySQL does not use the connection pool effect is more obvious.