Home IT Linux Windows Database Network Programming Server Mobile  
  Home \ Database \ MySQL Tutorial: Some optimized for I / O memory areas     - Ubuntu program using the Sound Recorder (Linux)

- zBackup: A versatile tool to remove duplicate backup (Linux)

- VMware11 virtual machine Ubuntu14.10 system partition sda1 disk expansion (Linux)

- Approach the next Linux shared interrupts (Linux)

- Linux network monitoring tools ntopng installation (Linux)

- Set up MySQL master and slave servers under Ubuntu 14.04 (Server)

- How to protect the Apache website under Linux system (Linux)

- C # function (Programming)

- Binary tree to the next node (Programming)

- Linux dual physical network card set to a virtual NIC (Linux)

- About Linux iptables firewall interview questions and answers (Linux)

- 11 examples in Linux df command (Linux)

- Delete specific files using bash directory under Linux (Linux)

- Linux file compression and file system packaged with instructions (Linux)

- Using the Linux VNC service (Server)

- OpenCV cvFindCornerSubPix () to find sub-pixel Corner (Programming)

- Linux Command study manual - GPG command (Linux)

- Oracle to create an external table (Database)

- Piostat - Monitoring and Statistics Linux process (Linux)

- Docker + Nginx + Tomcat7 simple load balancing configuration (Server)

  MySQL Tutorial: Some optimized for I / O memory areas
  Add Date : 2018-11-21      
  Here is the mysql Ver 14.14 Distrib 5.6.19, for Linux (i686) using EditLine wrapper

One, mysql directory file

ibdata1: system tablespace contains the data dictionary, the rollback journal / 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 table space - data files (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

Two, mysql thread





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

innodb all threads 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.

After MySQL 5.6.10, the default threading model is performed using one thread per client connection statement. As more and more clients to connect to the server and execute the statement, the overall performance. Provided thread pool widget designed to reduce the cost and improve the performance of other threads processing mode. The plug-in implementation through effective management of a large number of statements executed thread client connections to improve the performance of the server thread pool.

InnoDB Plugin version began to increase the default number of IO thread, the default read thread and the write thread is increased to 4, respectively, and no longer use innodb_file_io_threads parameters, but were using innodb_read_io_threads and innodb_write_io_threads parameters.

Each connection thread pool model to solve several problems to solve single-threaded

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 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 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 not too many execute in parallel.
Three, mysql to access the document workflow

Transaction from the network

Third, the influence of some parameters IO / memory

1, innodb_flush_log_at_trx_commit set to 2

This parameter is a transaction log (ib_logfile0, ib_logfile1) in what way is written to the log buffer

= 0 mysql crash is lost, the best performance

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

= 1 is not lost, inefficiency

buffer pool -> log buffer every disk write os cache & flush

2 = mysql crash even if the data is not lost

buffer pool -> os cache flush disk per second

Note: Due to the process of scheduling problems, the "execution once per flush (brush to disk) operation" is not guaranteed to be 100% of the "per second

2, sync_binlog

Binary log (binary log) frequency synchronized to disk. After each write binary log sync_binlog times, brush written to disk.

If autocommit enabled, each statement write a binary log, otherwise a write each transaction.

The default value is 0, not active and synchronized, while relying on the operating system itself from time to time to flush the contents of the file to disk

Set 1 most secure, sync once binary log after each statement or transaction, even in the time of the crash also lost most one statement or transaction log, but therefore also the slowest.

In most cases, data consistency is not very strict requirements, so do not put sync_binlog configured to 1, in order to pursue high concurrency, improve performance, can be set to 100 or directly with 0

3, write / read thread

Asynchronous IO threads

innodb_write_io_threads = 16
innodb_read_io_threads = 16

(This parameter is required in the configuration file to add to restart mysql instance onset) dirty pages to write the number of threads, increase this parameter can 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 will be written to the page has been updated data is written to a disk file. 75 default, the general now popular SSD hard drive is difficult to achieve this ratio. It can be based on the actual situation adjustment between 75-80

5, innodb_io_capacity = 5000

Dirty pages from the buffer refresh, refresh the number of dirty pages. Depending on the capabilities of disk IOPS general recommendations set as follows:

SAS 200
SSD 5000
PCI-E 10000-50000

6, innodb_flush_method = O_DIRECT (This parameter need to restart mysql instance onset)

Control innodb data files and redo log open, flashing mode. There are three values: fdatasync (default), O_DSYNC, O_DIRECT.

fdatasync mode: When writing data, write this step really does not need to be written to disk is completed (may be written to the buffer in the operating system will return to complete), a real flush operation is completed, buffer to the operating system to flush, and metadata information files also need to be updated to disk.
O_DSYNC mode: write log write operation in this step is completed, and the data file is written in the flush completion of this step by fsync.
O_DIRECT mode: write data files directly from mysql innodb buffer to disk, the operating system does not use a buffer, but the real complete this step is in the flush, or to go through the OS log buffer.

It can be seen O_DIRECT advantages compared fdatasync is the avoidance of double buffering, innodb buffer pool itself is a buffer, it does not need to be written to the buffer system, but there is a drawback because it is written directly to disk, as compared sequential read and write efficiency fdatasync be lower.

In a large number of random write environment taller than fdatasync O_DIRECT efficiency, sequential write many things, or the default fdatasync more efficient.

7, innodb_adaptive_flushing set to ON (flush dirty pages make smarter)

Effect of the number of dirty pages per second refresh

Rules from the "greater than 100 innodb_max_dirty_pages_pct refresh dirty pages to disk" to "the most appropriate function to determine the number by buf_flush_get_desired_flush_reate redo log generation rate to determine the need to refresh the page dirty", even when the proportion is less than innodb_max_dirty_pages_pct dirty page will refresh certain the amount of dirty pages.

8, innodb_adaptive_flushing_method set keep_average

Influence checkpoint, more average calculation speed adjustment brush dirty pages, the necessary flush. (This variable is a derived variable mysql version Percona Server under native mysql does not exist)

9, innodb_stats_on_metadata = OFF

Turn off some access information_schema libraries under the table and index statistics produced.

When the instance restart mysql, mysql will randomly io fetch data through all the tables to sample to statistics, with the actual use of the small, the proposed closure.

10, innodb_change_buffering = all

When the update / insert a non-clustered index data corresponding to the page is not in memory when the (non-clustered index update operation usually bring random IO), which will put a insert buffer, and then when the page is read the memory, the recording will merge these changes to the page. When the server is relatively idle, the background thread will do the merge operation.

Since the main advantage merge used to reduce io, but for some scenes will not have a fixed data number of changes, here you do not need to update / insert open change_buffering, if turned on just taking up extra space and buffer_pool handling capacity. The parameters to be configured based on the actual business environment.

11, innodb_old_blocks_time = 1000

So stay in the old sublist Block in a long time is 1s, it will not be transferred to the new sublist, avoiding Buffer Pool contaminated BP can be considered a long list. Young and old are divided into two parts, which accounted for 37% of the old default size (by the innodb_old_blocks_pct configuration). Near the top of Page showing recently accessed. Near the end of the Page represents not visited for a long time. The interchange of these two parts become midpoint. Page whenever there are new needs to be loaded into the BP, this page will be inserted into the midpoint position and declared as old-page. When the old part of the page, when accessed, the page will be promoted to the top of the list, identified as young.

Since the table scan operation is to load page, then immediately trigger a visit. So when innodb_old_blocks_time = 0, it will lead to the desired page table scan does not read as a young page is added to the top of the list. The SQL infrequent use of some more page will be out of BP, so that later will produce disk IO, resulting in slower response.

In this case though mysqldump will continue to visit the page loaded in the top of LRU, but the high frequency of hot data access will be faster again seize the page to the top of LRU. Resulting mysqldump loaded the page will be quickly brush, and immediately evict (eliminated). Therefore, time = 0 or 1000 access to such pressure environment will not cause a great impact, because dump data but simply seize the hot data. Not only dump, the time when the big data operations as well.

12, binlog_cache_size

Binary log buffer size: a transaction in the absence of submission (uncommitted) when the logs generated record to the Cache; time until the transaction is committed (committed) to be submitted, put the log persisted to disk.

Set too large, it will be relatively consume memory resources (Cache is the essence of memory), more needs to be noted that: binlog_cache is not global, is based on SESSION exclusive distribution units, which means that a thread starts a transaction when the time , Mysql will be assigned for this SESSION a binlog_cache

How to judge our current binlog_cache_size no problem setting it?

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 memory operation mode is represented binlog_cache spend many times, Binlog_cache_disk_use represents binlog_cache temporary file mode is to spend many times

13, innodb_file_per_table

innodb_file_per_table = 1

A separate table space


Each table and index data will exist in their own table space
Single table can move in different databases
Space can be recovered (except drop table operation)
After deleting large amount of data can be: alter table TableName engine = innodb; retraction of unused space
Use turncate table space will also shrink
Use a separate table space for a table, no matter how removed, the table space debris will not be too severe performance impact
Cons: single table increase is too large, such as more than 100 G

Conclusion: The shared table space on the Insert operation few advantages. The other did not perform well a separate table space. When you enable a separate table space, a reasonable adjustment under: innodb_open_files, InnoDB Hot Backup (cold standby) table space cp will not face a lot of useless copy. And the use innodb hot backup and table space management commands can now implement a single move.

14, increasing the local ports to cope with large numbers of connections

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

This parameter specifies the distribution range of the port, the port is to restrict access to the outside. Mysql default port 3306 is listening even if there are multiple requests for links, it will not be affected. However, because mysql are high-memory, high cpu, high io application is not recommended to mix and match the number used in mysql on the same machine. Even if the volume of business is not to be reduced by a single machine configuration, multiple machines to achieve better coexistence.

15, increasing the number of links queues

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

The number of queue build links the bigger the better, but from another point of view to the actual environment, you should use the connection pool is more appropriate to avoid duplication to link performance caused by consumption. Use connection pooling, the number of links from the application level will be something more controllable.

16, set the timeout link

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

It is mainly to reduce the length of time occupied resources TIME_WAIT. In particular, short for http links or mysql server does not use connection pooling effect is obvious.
- Mac OS X 10.9 compiler OCI8 module (Programming)
- Github with .gitignore ignore specified file (Linux)
- PostgreSQL vacuum principle of a function and parameters (Database)
- ORA-38856: Unable instance UNNAMED_INSTANCE_2 (redo thread 2) marked enabled (Database)
- Android development, may cause a memory leak problem (Programming)
- 11 examples in Linux df command (Linux)
- What is the Docker (Linux)
- Zabbix installation and configuration process (Server)
- How to Install Cantata MPD 1.3.3 for Ubuntu and Derived Version Users (Linux)
- Ubuntu and Derivatives users install the latest KKEdit 0.0.31 (Linux)
- MySQL related statements (CRUD) (SQLyog software) (Database)
- RHEL6 install Python and other packages from source (Linux)
- Several SQL Server data migration / Export Import Practice (Database)
- Android first line of code study notes (Programming)
- Number JavaScript type system (Programming)
- Kubernetes Cluster Setup problems encountered and solutions (Server)
- Limit the use of the request being Nginx Flood attack (Linux)
- Linux Disk and File Management (Linux)
- Python basis: os module on the file / directory using methods commonly used functions (Programming)
- Install Jetty on CentOS / RHEL 6.X (Server)
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.