澳门金沙vipinnodb中的REDO解析

Pages flushed up to 2122026419

seconds          —-过去55秒数据库的状态

 2: len 7; hex e00000013a0110; asc     :  ;;  

Spin rounds per wait: 2.40 mutex, 30.00 RW-shared, 0.00 RW-excl

0.00 hash searches/s, 0.00 non-hash searches/s

select * from t3 where a=2 for update  

 

*************************** 1. row
***************************
  Type: InnoDB
  Name: 

—————————-  


TRANSACTIONS

Trx id counter 2193C7F93
Purge done for trx’s n:o < 2193C7F82 undo n:o < 0
History list length 1640
LIST OF TRANSACTIONS FOR EACH SESSION:
—TRANSACTION 0, not started
MySQL thread id 998695, query id 2141628314 127.0.0.1 root

mysql> create table z(a int,primary key(a)) engine=innodb;  

缓冲池中缓存的数据类型有:索引页,数据页,UNDO页,插入缓冲,自适应哈希索引,InnoDB锁信息,数据字典等.

23272508 log i/o’s done, 0.00 log i/o’s/second

BUFFER POOL AND

*** (2) HOLDS THE LOCK(S):  

Buffer pool size 8192 中的大小即可算出缓冲区 8192*16(KB/page)/1024 =?

END OF INNODB MONITOR OUTPUT

Purge done for trx’s n:o < 11F35 undo n:o < 0  

insert 0, delete mark 0, delete 0

BACKGROUND THREAD

srv_master_thread loops: 372007 1_second, 372007 sleeps, 36255
10_second, 10646 background, 10646
flush     —-可反映数据库的压力情况

Buffer pool size   8192  

Purge done for trx’s n:o < 14771 undo n:o < 0 state: running but
idle

srv_master_thread log flush and writes: 373344

Total memory allocated 135987200; in additional pool allocated 0  

Sql代码  

LOG

Log sequence number 19152346341            –表示当前的LSN
Log flushed up to  
19152346341                –表示刷新到重做日志文件的LSN
Last checkpoint at  19152346341                –表示刷新到磁盘的LSN
0 pending log writes, 0 pending chkp writes

MySQL thread id 75, OS thread handle 0x33d61d40, query id 4077
192.168.0.69 root  


mysql> show engine innodb status\G

Trx id counter 11F45  


Spin rounds per wait: 5.09 mutex, 4.07 RW-shared, 30.47 RW-excl

BACKGROUND THREAD  

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

0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s

0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s  

init

FILE I/O

I/O thread 0 state: waiting for i/o request (insert buffer
thread)      —-可以看到10个IO线程,分别是insert buffer thread、
I/O thread 1 state: waiting for i/o request (log
thread)                    log thread、read thread、write 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
47691 OS file reads, 29911059 OS file writes, 23777623 OS fsyncs

Number of rows inserted 115750, updated 0, deleted 0, read 59717  

Log flushed up to 2122026419

show engine innodb status

 

MySQL thread id 1, OS thread handle 0x8ac, query id 15 localhost
127.0.0.1 root

ROW OPERATIONS

0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 9823, id 140584323032832, state: waiting for
server activity
Number of rows inserted 7012357, updated 9253488, deleted 2913883, read
214512661409

MySQL thread id 74, OS thread handle 0x33d61fc0, query id 4070
192.168.0.69 root  

Mysql InnoDB笔记内存

INSERT BUFFER AND ADAPTIVE HASH INDEX

Ibuf: size 1, free list len 4782, seg size 4784, 14474
merges              —-seg size
4784表示当前插入缓冲的大小为4784*16K=75MB      free list len
表示空闲列表的长度    
merged
operations:                                                                          size表示已经合并记录页的数量      
 insert 11381, delete mark 784750, delete
14238                            insert表示插入的记录数    
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 42499631, node heap has 20599 buffer(s)

Log flushed up表示刷新到重做日志文件的LSN,  

LRU len: 299, unzip_LRU len: 0

MEMORY                          —-这下面的信息可以看到InnoDB存储引擎缓冲池的使用情况

Total memory allocated 21978152960; in additional pool allocated 0
Dictionary memory allocated 532520
Buffer pool size  
1310719                             —-表示一共有1310719个缓冲帧,每个buffer
frame为16K,即:1310719*16K/1024=20G
Free buffers      
1070650                             —-表示剩余空间的缓冲帧
Database pages    
219470                            —-表示已经使用的缓冲帧
Old database pages 80995
Modified db pages 
0                                    —-表示脏页的数量
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 40256, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 48595, created 170875, written 10931522
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s
LRU len: 219470, unzip_LRU len: 0

—TRANSACTION 11F2B, not started  

Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0,
0] ,

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

  Type: InnoDB  

由缓冲池,重做日志缓冲池及额外的内存构成.

Status: 

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

0 pending log writes, 0 pending chkp writes

SEMAPHORES

OS WAIT ARRAY INFO: reservation count 9135562, signal count 21215755
Mutex spin waits 520177934, rounds 2648194228, OS waits 5360746
RW-shared spins 33278462, rounds 135599856, OS waits 1460221
RW-excl spins 1200397, rounds 36578622, OS waits 370830

LOG  

I/O thread 8 state: wait Windows aio (write thread)

I/O sum[0]:cur[0], unzip sum[0]:cur[0]

Main thread id 869673728, state: waiting for server activity  

Status:

131226 17:12:38 INNODB MONITOR OUTPUT

Per second averages calculated from the last 55

Per second averages calculated from the last 12 seconds  

Buffer pool size 8192

OS WAIT ARRAY INFO: reservation count 114, signal count 113  

Trx id counter 17154

*** (1) TRANSACTION:  

8 log i/o’s done, 0.00 log i/o’s/second

  


  

Sql代码  

———————-  

Main thread id 2708, state: sleeping

0.00 youngs/s, 0.00 non-youngs/s  

Pending reads 0

Spin rounds per wait: 30.00 mutex, 30.00 RW-shared, 390.00 RW-excl  

LIST OF TRANSACTIONS FOR EACH SESSION:

 0: len 4; hex 80000001; asc     ;;  


No buffer pool page gets since the last printout  

show engine innodb status\G;  

—TRANSACTION 0, not started  

Name:

Free buffers       4305  

Total memory allocated 135987200; in additional pool allocated 0

ERROR:   

BACKGROUND THREAD

mysql tables in use 1, locked 1  


TRANSACTION 11F41, ACTIVE 42 sec starting index read  

 

I/O thread 1 state: waiting for i/o request (log thread)  

Modified db pages 0

TRANSACTIONS  


0 queries inside InnoDB, 0 queries in queue  

312 OS file reads, 5 OS file writes, 5 OS fsyncs

merged operations:  


MySQL thread id 77, OS thread handle 0x33d61e80, query id 4118 localhost
root  

Per second averages calculated from the last 6 seconds

I/O thread 3 state: waiting for i/o request (read thread)  

 

—TRANSACTION 11F45, not started  


MySQL thread id 79, OS thread handle 0x33d62600, query id 4153 localhost
root statistics  

—TRANSACTION 0, not started

LIST OF TRANSACTIONS FOR EACH SESSION:  

TRANSACTIONS

 2: len 7; hex e00000013a011d; asc     :  ;;  

Number of rows inserted 0, updated 0, deleted 0, read 0

  

0 read views open inside InnoDB

=====================================  

SEMAPHORES

  


651 log i/o’s done, 0.00 log i/o’s/second  

No buffer pool page gets since the last printout

———————-  

============================

MySQL thread id 79, OS thread handle 0x33d62600, query id 4162 localhost
root  

I/O thread 0 state: wait Windows aio (insert buffer thread)

History list length 1433  

merged operations:

—  

0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s

Last checkpoint表示刷新到磁盘的LSN;  

I/O thread 3 state: wait Windows aio (read thread)

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

Pages made young 0, not young 0

INNODB存储引擎的预写日志方式来保证事务的完整性。这意味着磁盘上的存储的数据页和内存缓冲池中的页是不同步的,对于内粗缓冲中的页的修改,首先是写入重做日志文件,然后再写入磁盘。因此是一种异步的方式。可以通过show
 engine innodb status来观察当前的磁盘和日志的差距;

srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 17509
srv_idle

mysql tables in use 1, locked 1  

BUFFER POOL AND MEMORY

Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0,
0] ,  

show variables like ‘innodb_buffer_pool_size’\G;   134217728  

121130 14:17:47  

Mutex spin waits 5, rounds 12, OS waits 0

 insert 0, delete mark 0, delete 0  

Pending writes: LRU 0, flush list 0 single page 0

LIST OF TRANSACTIONS FOR EACH SESSION:  

insert 0, delete mark 0, delete 0

———-  

show variables like ‘innodb_additional_mem_pool_size’\G;    
8388608  

 insert 0, delete mark 0, delete 0  

AWE 开启后引擎自动禁用自适应哈希索引.

0.00 reads/s, 0.00 creates/s, 0.00 writes/s  


History list length 1433  


—  

    执行结果

 


I/O thread 1 state: waiting for i/o request (log thread)  

END OF INNODB MONITOR OUTPUT

———————-  


在innodb存储引擎中,事务日志通过重做(redo)日志文件和innodb存储引擎的日志缓冲(innodb
log buffer)来实现;当开始一个…

LOG

—TRANSACTION 11F36, not started  

INSERT BUFFER AND ADAPTIVE HASH INDEX   性能  插入缓冲与自适应哈希  
 插入缓冲不是缓冲池的一部分.它使用于索引是辅助索引,且索引不是唯一的.因为如主键自增长则插入这个聚集索引按顺序插入很快完成,但是如表另有不是唯一的非聚集索引,就需要对非聚集索引的叶子节点插入离散存储B+树.

Pending writes: LRU 0, flush list 0, single page 0  

InnoDB笔记内存 InnoDB 状态 Sql代码 show
engine innodb status\G; 执行结果 Type: InnoDB Name: Status: Per second
averages calculated from the last 6 seconds ——-…

  

Log sequence number 2122026419

RECORD LOCKS space id 0 page no 3132 n bits 80 index `PRIMARY` of
table `test`.`t3` trx id 11F40 lock_mode X locks rec but not gap
waiting  

ibuf aio reads: 0, log i/o’s: 0, sync i/o’s: 0

Total memory allocated 135987200; in additional pool allocated 0  

I/O thread 6 state: wait Windows aio (write thread)

0.00 hash searches/s, 0.00 non-hash searches/s  

srv_master_thread log flush and writes: 17509

  Type: InnoDB  

I/O thread 2 state: wait Windows aio (read thread)

—————–  

Pages read ahead 0.00/s, evicted without access 0.00/s, Random read
ahead 0.00/s

Database pages     3881  

Old database pages 0

BUFFER POOL AND MEMORY  

show variables like ‘innodb_log_buffer_size’\G;     8388608  

649 log i/o’s done, 0.00 log i/o’s/second  


*** WE ROLL BACK TRANSACTION (2)  


—TRANSACTION 0, not started  

InnoDB的内存

MySQL thread id 80, OS thread handle 0x33d62740, query id 4152 localhost
root statistics  

Last checkpoint at 2122026419

 2: len 7; hex e00000013a0110; asc     :  ;;  

0 queries inside InnoDB, 0 queries in queue

    -> commit;  

I/O thread 9 state: wait Windows aio (write thread)

    -> end;  

日志缓冲将重做日志信息先放入这个缓冲区,再按一定的频率将其刷新到重做日志文件.

TRANSACTION 11F41, ACTIVE 42 sec starting index read  

0.00 reads/s, 0.00 creates/s, 0.00 writes/s

  

I/O thread 4 state: wait Windows aio (read thread)

END OF INNODB MONITOR OUTPUT  

Pages read 299, created 0, written 1

  

0.00 youngs/s, 0.00 non-youngs/s

mysql> call load_test(10000);  

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

Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0,
0] ,  

History list length 588

    -> insert into z select i;  

RW-shared spins 2, rounds 60, OS waits 2

3 lock struct(s), heap size 320, 2 row lock(s)  

OS WAIT ARRAY INFO: signal count 2

Dictionary memory allocated 281610  

Free buffers 7892

Last checkpoint at  594246629  

Hash table size 553253, node heap has 1 buffer(s)

LATEST DETECTED DEADLOCK  

 

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:  

FILE I/O

RECORD LOCKS space id 0 page no 3132 n bits 80 index `PRIMARY` of
table `test`.`t3` trx id 11F41 lock_mode X locks rec but not gap
waiting  

Type: InnoDB

RW-excl spins 0, rounds 390, OS waits 13  

RW-excl spins 0, rounds 0, OS waits 0

=====================================  

Ibuf: size 1, free list len 0, seg size 2, 0 merges    
segSize:当前插入缓冲大小为2*16KB. free list 为空闲列表长度 size 为
已合并记录页为数量    insert 为插入记录数,merged
合并的页数量,merges合并次数

 1: len 6; hex 000000011f35; asc      5;;  

Dictionary memory allocated 32514

innodb中的REDO解析

I/O thread 5 state: wait Windows aio (read thread)

============================  

ROW OPERATIONS

No query specified  

I/O sum[0]:cur[0], unzip sum[0]:cur[0]

Pages read 633, created 3248, written 5875  

OS WAIT ARRAY INFO: reservation count 2

RW-excl spins 0, rounds 390, OS waits 13  


—TRANSACTION 11F2B, not started  

Database pages 299

Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format;
info bits 0  

0.00 hash searches/s, 0.00 non-hash searches/s

I/O thread 2 state: waiting for i/o request (read thread)  

I/O thread 7 state: wait Windows aio (write thread)

 0: len 4; hex 80000002; asc     ;;  

show engine innodb status

————————————-  

InnoDB 状态

mysql>