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ù)存儲單機(jī)時代多機(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ù)管理單位頁（默認(rèn)8k）區(qū)（每區(qū)默認(rèn)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)定運(yùn)行，包括存儲系統(tǒng)長期的穩(wěn)定運(yù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個單個文件最大尺寸默認(rèn)1G受OS限制單個文件初始大小自動擴(kuò)展到單個文件最大自動擴(kuò)展到最大文件在外存的形式filenode.1,filenode.2,etc表名.ibd2.1存儲層，我們看重什么？單表比較132.1存儲層，我們看重什么？PostgreSQL

單表容量示例：單表

2GB*(1024*100

個文件

)

=

200TB/

表空間（

64-bits

機(jī)器，實際上限為

2

的

64

次方

)

次方）MySQL

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

,

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

依賴脫機(jī)配置參數(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

運(yùn)行信息162.2看的見的文件僅僅是文件嗎？

--PostgReSQL

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

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

1

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

2

運(yùn)行信息

運(yùn)行信息與數(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)運(yùn)行信息、元

數(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)運(yùn)行信息與數(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

進(jìn)行了一

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

力受

OS

限制

單個文件大小

依賴

OS24比較項PostgreSQLMySQL系統(tǒng)運(yùn)行的信息使用獨立的外存文件存放沒有獨立文件存放系統(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ù)邏輯存儲）

如：按表空間進(jìn)行備份

/

恢復(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

實例運(yùn)行中根據(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)擴(kuò)展：數(shù)據(jù)量增加導(dǎo)致數(shù)據(jù)文件變大的過程中

產(chǎn)生

IO

等待，有辦法避免嗎？

文件擴(kuò)展時，產(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

]

]

//

指定初始大小

文件的大小提前擴(kuò)展

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

IO

等待

[

,

MAXSIZE

=

{

max_size

[

KB

|

MB

|

GB

|

TB

]

|

UNLIMITED

}

]

[

,

FILEGROWTH

=

growth_increment

[

KB

|

MB

|

GB

|

TB|

%

]

]

//

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

[

,

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

//

指定初始大小

文件的大小提前擴(kuò)展

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

IO

等待

AUTOEXTEND

ON

//

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

NEXT

10K

MAXSIZE

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

--ORacle

extent--

最小空間分配單位

DatabaseSYSTEMTablespacetablespace1tablespace2Segment1Segment3

Extent1

Extent2Segment=FileBlock

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

--

存儲能力

PK423PostgReSQL

和

MySQL

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

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