Notes on "MySQL Technology insider" (bottom)

Chapter Seventh Business

First, redo and undo

When a transaction starts, the log sequence number of the transaction is logged, and when this transaction executes, the log buffer pool is inserted into the data, and when the transaction commits, the data of the journal buffers is deposited to disk. (When innodb_flush_log_at_trx_commit=1)

Show engine InnoDB Status---Log---log sequence number 1759155 (current LSN) log flushed up to 1759155 (already flushed to redo log lsn) Last ch Eckpoint at 1759155 (LSN that has been flushed to disk)

Redo Log increment = log flushed up to-lastcheckpoint at

Undo when a transaction or statement fails for some reason, it is rolled back with the Undo segment transaction. He is not like a redo log with a log file exists, he exists in the form of a segment. Undo is a logical rollback, which is the opposite of the statement executed when rolling back (with delete in front of insert rollback). In a shared file, the undo segment is not recycled immediately after the transaction commits, and is slowly recycled by the master thread.

Second, transaction control
Begin Show Open transaction
Commit work means what to do after a transaction is submitted
Completion_type=0 don't do any work
Automatically open a new transaction when =1 is committed
=2 Commit Disconnect and server connection all parameters are gone.

TPS is an important parameter in MySQL performance monitoring

show global status like  '%commit% '; +---------------- +-------+| variable_name  | value |+----------------+-------+| com_commit     |1      | Show Submission    | |  handler_commit | 214  | Implicit commit +----------------+-------+ show global  status like  '%rollback% '; +----------------------------+-------+| variable_name               | value |+------------------ ----------+-------+| com_rollback                | 0     | |  Handler_rollback           | 0      |+----------------------------+-------+ 

tps= (com_commit+ handler_commit+ com_rollback+ handler_rollback)/time

Distributed native transactions participate in the global provisioning of their isolation levels using a serial modification process that cannot be read

Third, bad business habits

1.

CREATE PROCEDURE load () ...... While S<=count doinsert to T1 select Null,ccommit ... end

Instance one, forgot MySQL has auto-submit function, and wrote the Commmit, causing two flushes to redo log.

2. Transaction control statements are best done by the client because the program can easily crawl error messages. The stored procedure only completes the logical operation.

Eighth Chapter Backup

First, the backup classification

Backup method: Hot spare, cold, Win Bei
Win Bei has an impact on the server.

Cold: Need to share tablespace files, stand-alone tablespace files, frm files, redo log files

Advantages: Fast recovery and simple operation. Cons: Large file (including undo segment, insert buffer, etc.)
Backup content: Logical backup, bare file backup
Logical backup: Backup files are readable, such as data exported through the OutFile and Mysqldump methods (row data or SQL statements) but with a long recovery time
Database backup content: Full backup, incremental backup, log backup
Incremental Backup Page Principle: This page is backed up by a log point where the current log point is larger than a full-scale backup.
Log backups combined with full backup and log backups can restore a database to a point of data
Second, logical backup

1.mysqldump
you can only use the--lock-tables option if there are myism in the database that have InnoDB storage engine backups. --lock-tables and--single-transaction are two mutually exclusive, only one can be selected for backup. The--lock-tables option is a table-level lock that locks the schema sequentially while the backup is in sequence, but the table can read
Mysqldump If there is a binary, use the--HEX-BLOB option to display binary binary with 16, otherwise the binary character is not visible
Mysqldump can not export views

Single transaction consistency cannot isolate DDL statements, which are implicit commits (note: The statements that were not committed before the DDL statement are also submitted).

2. mysqlimport Multiple file Import
Mysqlimport can be poured into multiple tables use-thread can define the number of concurrent

/usr/local/mysql/bin/mysqlimporttest--use-threads=2/opt/a6.txt/opt/a9.txt

Concurrent import filenames/opt/a6.txt/opt/a9.txt to TEST.A6 and test. A9 table, the file name is the table name

Iii. about Binlog and redo log parameters

Innodb_flush_log_at_trx_commit is for redo logs.

= 1: Log buffer is flushed to the logfile hard drive for each commit

= 0: The log buffer data is flushed to the logfile drive per second

= 2: Each commit will flush data from log buffer into iOS memory, flushing data from iOS memory to disk per second

sync_binlog for Binlog.=0 MySQL does not control the refresh of Binlog=n flush to disk per commit n transactions

Innodb_support_xa=1 (Transactional two-segment commit) guarantees consistency between Binlog and redo logs. When the transaction commits, the data is flushed to the redo log file and labeled Prepare, and when the data is written to Binlog, the redo log changes the data prepare state to the commit state.

The specific introduction can refer to:

http://www.woqutech.com/?p=769

Http://www.linuxidc.com/Linux/2015-11/124942.htm

Iv. analysis of state parameters of replication

mysql> show slave status\g***************************1. row **********************                slave_io_state:  Waiting formaster to send event                   Master_Host: 192.168.188.211                   master_user:  replication                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: binlog.000487           read_master_log_pos: 841366505                relay_log_file: relaylog.000548                 relay_log_pos: 841366648        relay_ master_log_file: binlog.000487              slave_io_running: yes            slave_ sql_running: yes               replicate_do_db:voyage_intelligence,voyage_massive,voyage_static_zh_cn,voyage_static_zh_tw           Replicate_Ignore_DB:mysql,test,information_schema            Replicate_Do_Table:        replicate_ignore_table:      replicate_wild_do_table:  replicate_wild_ignore_table:                    last_ errno: 0                    Last_Error:                  skip_counter: 0          exec_ master_log_pos: 841366505               Relay_Log_Space: 841366837               until_condition: none

Read_master_log_pos represents the offset from the master to the read from the library (841366505 indicates that the binlog.000487 802M has been read synchronously)

Exec_master_log_pos indicates that the offset has been synchronized

SQL Thread delay =read_master_log_pos-exec_master_log_pos

(841366505-841366505=0, indicating no SQL latency)

Main Library

Mysql> Show Master Status\g

1. Row ***************************

file:binlog.000487

position:842561811

Binlog_do_db:voyage_massive,voyage_intelligence,voyage_static_zh_cn,voyage_static_zh_tw

binlog_ignore_db:

1 row in Set (0.00SEC)

IO Thread delay = Position-read_master_log_pos (you can see that the IO delay has a difference of 1 m)

Nineth Chapter Performance Tuning

I. Classification of databases
Databases are categorized according to application: OLTP (online transaction processing), OLAP (online transaction analysis)
Easy OLTP operation, small data capacity but large concurrency

Olap the opposite
CPU consumption: Statement complex comparison sort connection
Olap intensive in CPU OLTP intensive in IO

MySQL only one process, modify innodb_read_io_threads and innodb_write_io_threads to improve IO threads

Mysql> set Innodb_read_io_threads=1;

ERROR 1238 (HY000): Variable ' innodb_read_io_threads ' Isa read only Variable

You can see that the modification of this parameter requires restarting the server and then modifying the configuration table to modify the number of threads.

Second, the importance of memory

The increase in the buffer pool increases linearly the ability to process transactions per second, and TPS performance no longer increases when the buffer pool size is larger than the data file itself

Hit rate of the buffer pool

mysql> show global status like  '%innodb%read% ';   (View database health) +----------- ----------------------------+--------------+| variable_name                          |  value     |+---------------------------------------+--------------+       | |  Innodb_buffer_pool_read_ahead         | 101128    | (number of read-ahead) | innodb_buffer_pool_read_ahead_evicted | 805         | (pre-read not selected page) | innodb_buffer_pool_read_requests  | 761299912003 | (Number of pages read from cache) | innodb_buffer_pool_reads               | 645038 | (number of pages read from disk) | INNODB_DATA_READ      &Nbsp;              | 29097275392| (number of bytes read in total) | innodb_data_reads               | 79435  | (number of Read requests, multiple pages at a time) |+---------------------------------------+--------------+

Buffer pool Hit rate = innodb_buffer_pool_read_requests/(innodb_buffer_pool_read_requests+innodb_buffer_pool_read_ahead+ Innodb _buffer_pool_reads)

Buffer hit Rate is =761299912003/761300658169=99.99%

Buffer pool hit rate not less than 99% indicates that memory is adequate and there are no bottlenecks

Third, disk raid

RAID0 : The fastest reading, one data is divided into multiple segments, data written in parallel on each disk, but no redundancy, easy to lose
RAID1 most reliable, 22 mirror, slow reading speed
RAID5 (commonly used) also adds parity information when storing data, data and inspection are stored on each disk, one disk is broken, the disk can be recovered through the inspection information of other disks, and requires at least three disks.
RAID10(commonly used) first do RAID1 and then do RAID0. Two is a set of groups within a group of backups, inter-group parallelism. The difference with RAID0 is that data is striped and stored in separate backup groups

RAID6 There are two parity discs compared to the RAID5 to deal with the problem of two disks being bad at the same time.

Reference:http://xuegodlinux.blog.51cto.com/10844319/1709964

This article is from the "diligence and desolate" blog, please be sure to keep this source http://10574662.blog.51cto.com/10564662/1712033

Notes on "MySQL Technology insider" (bottom)