mysql study_MySQL Study之--MySQL体系结构深入解析

MySQL体系架构

由连接池组件、管理服务和?工具组件、sql接口组件、查询分析器组件、优化器组件、缓冲组件、插件式存储引擎、物理?文件组成。mysql是独有的插件式体系结构,各个存储引擎有自己的特点。

MySQL内存结构：

Mysql 进程结构

Mysql不像oracle那样是通过多进程来完毕其功能的。默认情况下，InnoDB存储引擎的后台线程有7个：

4个IO thread，

1个master thread，

1个锁(lock)监控线程。

1个错误监控线程。

在InnoDB Plugin版本号開始添加了默认IO thread的数量，默认的read thread和write thread分别增大到了4个。而且不再使用innodb_file_ io_threads參数，而是分别使用innodb_read_io_threads和innodb_write_io_threads參数。

查看mysql thread：

mysql> show variables like ‘innodb_%version%‘;

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

| Variable_name | Value |

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

| innodb_version | 1.2.4 |

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

1 row in set (0.00 sec)

mysql> show engine innodb status;

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

150709 15:26:40 INNODB MONITOR OUTPUT

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

Per second averages calculated from thelast 12 seconds

-----------------

BACKGROUND THREAD

-----------------

srv_master_thread loops: 0 srv_active, 0srv_shutdown, 804 srv_idle

srv_master_thread log flush and writes: 804

----------

SEMAPHORES

----------

OS WAIT ARRAY INFO: reservation count 2

OS WAIT ARRAY INFO: signal count 2

Mutex spin waits 0, rounds 0, OS waits 0

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

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

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

------------

TRANSACTIONS

------------

Trx id counter 5377

Purge done for trx‘s n:o < 4872 undo n:o< 0

History list length 1

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 0, not started

MySQL thread id 3, OS thread handle0x7f1656c75700, query id 5 localhost root init

show engine innodb status

--------

FILE I/O

--------

I/O thread 0 state: waiting for completedaio requests (insert buffer thread)

I/O thread 1 state: waiting for completedaio requests (log thread)

I/O thread 2 state: waiting for completedaio requests (read thread)

I/O thread 3 state: waiting for completedaio requests (read thread)

I/O thread 4 state: waiting for completedaio requests (read thread)

I/O thread 5 state: waiting for completedaio requests (read thread)

I/O thread 6 state: waiting for completedaio requests (write thread)

I/O thread 7 state: waiting for completedaio requests (write thread)

I/O thread 8 state: waiting for completedaio requests (write thread)

I/O thread 9 state: waiting for completedaio requests (write thread)

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

ibufaio reads: 0, log i/o‘s: 0, sync i/o‘s: 0

Pending flushes (fsync) log: 0; bufferpool: 0

283 OS file reads, 5 OS file writes, 5 OSfsyncs

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

-------------------------------------

INSERT BUFFER AND ADAPTIVE HASH INDEX

-------------------------------------

Ibuf: size 1, free list len 0, seg size 2,0 merges

merged operations:

insert 0, delete mark 0, delete 0

discarded operations:

insert 0, delete mark 0, delete 0

Hash table size 276707, node heap has 0buffer(s)

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

---

LOG

---

Log sequence number 1611537

Log flushed up to 1611537

Pages flushed up to 1611537

Last checkpoint at 1611537

0 pending log writes, 0 pending chkp writes

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

----------------------

BUFFER POOL AND MEMORY

----------------------

Total memory allocated 137363456; inadditional pool allocated 0

Dictionary memory allocated 39010

Buffer pool si

MySQL Study之--MySQL体系结构深入解析

标签：active   flush   插件   byte   his   引擎   技术分享   add   内存

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：http://www.cnblogs.com/wzzkaifa/p/7078908.html