PostgreSQL和MySQL的存儲層深度解析

PostgreSQL

和

MySQL存儲層深度解析存儲與計算

,

引領(lǐng)數(shù)據(jù)處理的發(fā)展PostgReSQL

pk

MySQL

：存儲層對比12PostgReSQL

和

MySQL

存儲層的優(yōu)化3存儲層，我們看重什么？看的見的文件僅僅是文件嗎？看不見的管理者：表空間？2.12.22.331什存儲與計算

,

引領(lǐng)數(shù)據(jù)處理的發(fā)展

？

什么引領(lǐng)著

數(shù)么在引領(lǐng)著數(shù)據(jù)處理技術(shù)的發(fā)展4文件系統(tǒng)

:

只存儲無計算數(shù)據(jù)庫

:

既存儲又計算兩個時代，五個階段存儲的目的是為了計算

分布式數(shù)據(jù)庫

:

分布式實時計算

第一代

Haddop

生態(tài)園

:

分布式非實時計算

武新分布式文件系統(tǒng)

:

對大數(shù)據(jù)存儲單機時代多機協(xié)作時代階段1階段2階段3階段4階段551存儲與計算

,

引領(lǐng)數(shù)據(jù)處理的發(fā)展

存儲與計算的需求

引領(lǐng)什么在引領(lǐng)著數(shù)據(jù)處理技術(shù)的發(fā)展

發(fā)展62PostgReSQL

pk

MySQL

：

存儲層對比存儲層，我們看重什么？看的見的文件僅僅是文件嗎？看不見的管理者：表空間？2.12.22.372.1

存儲層，我們看重什么？存儲層，三大能力1

IO

能力2

穩(wěn)定性3

存儲能力力8比較項PostgreSQLMySQL數(shù)據(jù)管理單位頁（默認8k）區(qū)（每區(qū)默認1M，一次可分配1--4個）物理讀寫單位頁頁使用方式

讀數(shù)據(jù)到緩存區(qū)讀數(shù)據(jù)到緩存區(qū)

預(yù)讀(Read-Ahead)

多范圍讀(Multi-RangeRead)2.1存儲層，我們看重什么？存儲層，三大能力

IO

能力92.1

存儲層，我們看重什么？存儲層，三大能力

穩(wěn)定能力PostgreSQL

與MySQL都能長期穩(wěn)定運行，包括存儲系統(tǒng)長期的穩(wěn)定運行MySQL:Facebook/Google/

淘寶

...PostgreSQL:

騰訊

/Fujitsu/Cisco/NTT

Data..../about/users/

1064-bitsPostgreSQLMySQL數(shù)據(jù)庫的個數(shù)無限制無限制表的個數(shù)無限制無限制(InnoDB限制40億張表)單表的容量無限制理論上無限制(受限于OS)2.1存儲層，我們看重什么？存儲層，三大能力

存儲能力112.1

存儲層，我們看重什么？文件大小受操作系統(tǒng)限制12比較項PostgreSQLMySQL每個表的數(shù)據(jù)文件個數(shù)無數(shù)個1個單個文件最大尺寸默認1G受OS限制單個文件初始大小自動擴展到單個文件最大自動擴展到最大文件在外存的形式filenode.1,filenode.2,etc表名.ibd2.1存儲層，我們看重什么？單表比較132.1存儲層，我們看重什么？PostgreSQL

單表容量示例：單表

2GB*(1024*100

個文件

)

=

200TB/

表空間（

64-bits

機器，實際上限為

2

的

64

次方

)

次方）MySQL

單表容量示例：受操作系統(tǒng)影響

,

單表容量就是外存文件大小MySQL

依賴脫機配置參數(shù)

配置

系統(tǒng)表空間的容量

,

使用不方便且

IO

低效：innodb_data_file_path

=

/ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend

14

實例X

易的相冊庫：150

億記錄，分布在

8

個實例節(jié)點上，單表接近

20

億的記錄，單表

350G

的存儲量X

浪

60

億單表的記錄，單表容量超過

1T2.2看的見的文件僅僅是文件嗎？cReate

tablespace

ts_a

(...)cReate

table

t_a

(…)

…

tablespace

ts_a...

數(shù)據(jù)庫

用戶

SQL

外存數(shù)據(jù)的存儲路徑目錄

+

文件名PostgReSQL/MySQL/...,

神秘的盒子

?152.2看的見的文件僅僅是文件嗎？

--PostgReSQL1

存儲結(jié)構(gòu)2

運行信息162.2看的見的文件僅僅是文件嗎？

--PostgReSQL

存儲結(jié)構(gòu)包括了元

信息和用戶的數(shù)據(jù)

1

存儲結(jié)構(gòu)

2

運行信息

運行信息與數(shù)據(jù)

庫的功能相對應(yīng)172.2看的見的文件僅僅是文件嗎？

--PostgReSQL

用戶

SQL

系統(tǒng)表cReate

tablespace

ts_a

(…)cReate

table

t_a

(…)

…

tablespace

ts_a...select

…

fRom

t_a...

數(shù)據(jù)緩存區(qū)

外存，文件系統(tǒng)：

pg_tablesapce

pg_class

pg_attRibutes

Reltablespace

+

Relfilenode

（

+

ReltoastRelid

）

存儲介質(zhì)目錄

-

表空間

-+

文件名

filenoed1/filenode2

18深度挖掘2.2看的見的文件僅僅是文件嗎？

--PostgReSQL

文件中存放系統(tǒng)運行信息、元

數(shù)據(jù)、用戶數(shù)據(jù)

沒有管理

IO

依賴操作系統(tǒng)

表空間級的存儲能

力沒有限制

單個文件大小有限但表的數(shù)據(jù)量沒限制192.2看的見的文件僅僅是文件嗎？

--MySQL202.2看的見的文件僅僅是文件嗎？

--MySQL

1

系統(tǒng)數(shù)據(jù)

2

用戶數(shù)據(jù)212.2看的見的文件僅僅是文件嗎？

--MySQL

參數(shù)控制表空間外存，文件系統(tǒng)：t_a.fRm

t_a.ibd.

InnoDBIf

(…){...}FoR

(int

i=1,

i<100,i++){…}

存儲介質(zhì)

用戶

SQL

innodb_file_peR_tablecReate

table

t_a

(…)

…select

…

fRom

t_a...

數(shù)據(jù)緩存區(qū)22stoRage\innobase\include\fil0fil.h:內(nèi)部存儲時，頁的類型，部分定義：系統(tǒng)運行信息與數(shù)據(jù)存放在一起2.2看的見的文件僅僅是文件嗎？

--MySQL#define

FIL_PAGE_INDEX

17855

/*!<

B-tree

node

*/#define

FIL_PAGE_UNDO_LOG

2

/*!<

Undo

log

page

*/#define

FIL_PAGE_INODE

3

/*!<

Index

node

*/#define

FIL_PAGE_IBUF_FREE_LIST

4

/*!<

Insert

buffer

free

list

*/#define

FIL_PAGE_TYPE_ALLOCATED

0

/*!<

Freshly

allocated

page

*/#define

FIL_PAGE_IBUF_BITMAP

5

/*!<

Insert

buffer

bitmap

*/#define

FIL_PAGE_TYPE_SYS

6

/*!<

System

page

*/#define

FIL_PAGE_TYPE_TRX_SYS

7

/*!<

Transaction

system

data

*/#define

FIL_PAGE_TYPE_FSP_HDR

8

/*!<

File

space

header

*/#define

FIL_PAGE_TYPE_XDES

9

/*!<

Extent

descriptor

page

*/#define

FIL_PAGE_TYPE_BLOB

10

/*!<

Uncompressed

BLOB

page

*/#define

FIL_PAGE_TYPE_ZBLOB

11

/*!<

First

compressed

BLOB

page

*/

23深度挖掘2.2看的見的文件僅僅是文件嗎？

--MySQL

文件中存放元數(shù)據(jù)、用戶數(shù)據(jù)

對

IO

進行了一

定程度的管理表空間級的存儲能

力受

OS

限制

單個文件大小

依賴

OS24比較項PostgreSQLMySQL系統(tǒng)運行的信息使用獨立的外存文件存放沒有獨立文件存放系統(tǒng)的元數(shù)據(jù)外存文件（global）information_schema用戶的表定義使用系統(tǒng)表,由系統(tǒng)統(tǒng)一存儲表定義直接存放到外存用戶的表數(shù)據(jù)使用外存文件文件外存文件使用IO管理單位頁(物理IO)區(qū)(邏輯IO)2.2看的見的文件僅僅是文件嗎？

--PK252.3看不見的管理者：表空間？

表空間

:

是數(shù)據(jù)庫管理數(shù)據(jù)存儲的方式

(

也許稱作數(shù)據(jù)空間更為合適

)

歷史

:

生于

Oracle

，開花于各個數(shù)據(jù)庫

品種

:

不盡相同

意義

:

數(shù)據(jù)只有被管理起來，才能被高效利用262.3看不見的管理者：表空間？用途

:

1

控制用戶

/

表占用的儲存空間大小，配額式管理數(shù)據(jù)（按對象存儲）

如：表的數(shù)據(jù)容量可控

2

控制數(shù)據(jù)庫所占用的存儲空間大小，配額式管理應(yīng)用的空間（按需存儲）

如：不同應(yīng)用的數(shù)據(jù)容量可控（云數(shù)據(jù)庫中限制數(shù)據(jù)庫的容量）

3

分離日志文件與數(shù)據(jù)文件的存儲，提高數(shù)據(jù)庫性能（按位置存儲）

如：日志文件和數(shù)據(jù)文件分別儲存于不同的物理位置

4

控制數(shù)據(jù)的分布，提高數(shù)據(jù)庫的輸入輸出性能（按數(shù)據(jù)存儲）

如：不同數(shù)據(jù)文件分別存儲于不同的物理位置

5

控制對象管理的粒度（按業(yè)務(wù)邏輯存儲）

如：按表空間進行備份

/

恢復(fù)

272.3看不見的管理者：表空間？282.3看不見的管理者：表空間？

--PostgReSQL表空間的創(chuàng)建

:

CREATE

TABLESPACE

dbspace

LOCATION

'/data/dbs';

分析

:

表空間的名稱

dbspace

對應(yīng)

物理存儲的目錄

/data/dbs表空間的使用

:

CREATE

...

table_name

(

[

{

column_name

data_type

...}...]

)

[

TABLESPACE

dbspace

]

分析

:

表空間的名稱

tdbspace

對應(yīng)

表名

table_name

（也可以是索引名）

292.3看不見的管理者：表空間？

--PostgReSQL表空間的管理

:

1

每個數(shù)據(jù)文件是一個段“

segment”

，一個表可以有無數(shù)個段

2

ALTER

TABLESPACE

name

RENAME

TO

new_name

3

ALTER

TABLESPACE

name

OWNER

TO

new_owner

4

ALTER

TABLESPACE

name

SET

(

tablespace_option

=

value

[,

...

]

)

5

ALTER

TABLESPACE

name

RESET

(

tablespace_option

[,

...

]

)

tablespace_option

：

seq_page_cost/random_page_cost

分析

:

表空間管理方式簡單

302.3看不見的管理者：表空間？

--PostgReSQL

邏輯存儲結(jié)構(gòu)312.3看不見的管理者：表空間？

--MySQL表空間的創(chuàng)建

:

指定用戶的每個表獨立使用表空間：

innodb_file_per_table指定系統(tǒng)使用的表空間的位置

/

屬性

：

innodb_data_home_dirinnodb_data_file_path分析

:MySQL

實例運行中根據(jù)參數(shù)自動管理表空間的使用

:

自

MySQL

5.6

及之后的版本，通過調(diào)整

nnodb_file_per_table

參數(shù)，允許每個表擁有獨立的表空間。

分析

:

自動管理

322.3看不見的管理者：表空間？

--MySQL表空間的管理

:

1

通過上述參數(shù)調(diào)整

2

簡單

分析

:

表空間管理方式簡單，不方便動態(tài)切換存儲設(shè)備（通過符號鏈接支持）332.3看不見的管理者：表空間？

--MySQL

邏輯存儲結(jié)構(gòu)342.3看不見的管理者：表空間？

--

實踐中的問題1

動態(tài)增加存儲設(shè)備或數(shù)據(jù)文件？

D

盤滿了，新加的

E

盤能不能繼續(xù)存放

D

盤中

A

表的新數(shù)據(jù)？2

文件的動態(tài)擴展：數(shù)據(jù)量增加導(dǎo)致數(shù)據(jù)文件變大的過程中

產(chǎn)生

IO

等待，有辦法避免嗎？

文件擴展時，產(chǎn)生了

IO

等待，導(dǎo)致數(shù)據(jù)處理速度降低，怎么辦？3

…...352.3文件組

1...…...文件組

n看不見的管理者：表空間？

--MS

SQL

SeRveR

數(shù)據(jù)庫文件組

k表

1表

k表

n存儲層邏輯層36...2.3看不見的管理者：表空間？

--MS

SQL

SeRveRALTER

DATABASE

database_name

<add_or_modify_files>

//

可以增加

/

修改文件|

<add_or_modify_filegroups>

//

可以增加

/

修改文件組{}[;]<add_or_modify_files>::={

ADD

FILE

<filespec>

[

,...n

]

[

TO

FILEGROUP

{

filegroup_name

}

]

//

可以增加文件到文件組...}372.3看不見的管理者：表空間？

--MS

SQL

SeRveR<filespec>::=(

NAME

=

logical_file_name

[

,

NEWNAME

=

new_logical_name

]

[

,

FILENAME

=

{'os_file_name'

|

'filestream_path'

}

]

//

可以換位置（使用新的存儲

/

分布文件）

[

,

SIZE

=

size

[

KB

|

MB

|

GB

|

TB

]

]

//

指定初始大小

文件的大小提前擴展

好，避免文件系統(tǒng)擴展文件時產(chǎn)生的

IO

等待

[

,

MAXSIZE

=

{

max_size

[

KB

|

MB

|

GB

|

TB

]

|

UNLIMITED

}

]

[

,

FILEGROWTH

=

growth_increment

[

KB

|

MB

|

GB

|

TB|

%

]

]

//

可自動擴展文件（存儲空間的配額管理）

[

,

OFFLINE

])<add_or_modify_filegroups>::={

|

ADD

FILEGROUP

filegroup_name

//

可以增加

/

修改文件組

[

CONTAINS

FILESTREAM

]...}

382.3看不見的管理者：表空間？

--MS

SQL

SeRveR區(qū)，

Extent

：

區(qū)是表空間管理的基本單位，一個區(qū)包括

8

個物理相連的頁面。

頁面是

IO

的物理單位。392.3看不見的管理者：表空間？

--ORacleCREATE

TABLESPACE

tbs_02

DATAFILE

'diskb:tbs_f02_1.dbf'

SIZE

500K

REUSE

AUTOEXTEND

ON

NEXT

500K

MAXSIZE

100M;ALTER

TABLESPACE

tbs_02

ADD

DATAFILE

'tbs_f02_2.dbf'

//

為指定表空間增加數(shù)據(jù)文件，可以換位置（使用新的存儲

/

分布文件）

SIZE

100K

//

指定初始大小

文件的大小提前擴展

好，避免文件系統(tǒng)擴展文件時產(chǎn)生的

IO

等待

AUTOEXTEND

ON

//

可自動擴展文件（存儲空間的配額管理）

NEXT

10K

MAXSIZE

100K;402.3看不見的管理者：表空間？

--ORacle

extent--

最小空間分配單位

DatabaseSYSTEMTablespacetablespace1tablespace2Segment1Segment3

Extent1

Extent2Segment=FileBlock

Segment2Tablespace41比較項PostgreSQLMySQL動態(tài)增加存儲文件/改變存儲位置不能不能為每個表單獨配置不同的表空間能能數(shù)據(jù)文件的大小可以提前分配不能不能對物理IO進行管理(區(qū))不能能2.3看不見的管理者：表空間？

--

存儲能力

PK423PostgReSQL

和

MySQL

存儲層的優(yōu)化43存儲功能的改進使用方法的改進有益的實踐經(jīng)驗

3.13.23.3比較項PostgreSQLMySQL表空間的管理：改造為完全的DMS(DatabaseManagedSpace)類似Oracle、MSSQLServer