[201804012]關于hugepages 3.txt

--//有一段時間我一直強調安裝oracle一定要配置hugepage,因為現在的服務器內存越來越大,如果還使用4K的頁面表,如果內存表占用內存巨大,
--//特別連接數量很大的情況下,更加明顯,結果導致內存緊張,使用交換,這些類似的例子網上很多.
--//鏈接:
http://blog.itpub.net/267265/viewspace-2128811/=>[20161121]關于使用hugepage的討論.txt
http://blog.itpub.net/267265/viewspace-2134900/=>[20170308]再談hugepages.txt

--//感覺我第2次測試,可能沒有排除直接路徑讀干擾,1:3效果不是很明顯,我決定重復測試看看

1.環境:
SCOTT@book> @ &r/ver1
PORT_STRING??????????????????? VERSION??????? BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx??????????? 11.2.0.4.0???? Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> create table t as select rownum id from dual connect by level<=2;
Table created.

SCOTT@book> ALTER TABLE t MINIMIZE RECORDS_PER_BLOCK ;
Table altered.
--//這樣可以實現每塊2條記錄.

SCOTT@book> insert into t select rownum+2 from dual connect by level <=64000-2;
63998 rows created.

SCOTT@book> commit ;
Commit complete.

SYS@book> create pfile='/tmp/@.ora' from spfile;
File created.

--//重啟數據庫:
SYS@book> startup? pfile='/tmp/book.ora';
ORACLE instance started.
Total System Global Area? 634732544 bytes
Fixed Size????????????????? 2255792 bytes
Variable Size???????????? 197133392 bytes
Database Buffers????????? 427819008 bytes
Redo Buffers??????????????? 7524352 bytes
Database mounted.
Database opened.

SYS@book> show sga
Total System Global Area??? 634732544 bytes
Fixed Size??????????????????? 2255792 bytes
Variable Size?????????????? 197133392 bytes
Database Buffers??????????? 427819008 bytes
Redo Buffers????????????????? 7524352 bytes

2.測試說明:
--//首先說一下我的想法,如果執行計劃走直接路徑讀,相關數據塊并沒有進入緩存,這樣測試使用pagesizes的結果就不包括這部分.
--//這樣執行計劃走直接路徑讀與不走直接路徑讀的比較就是這部分緩存使用pagesizes.

--//建立測試連接的執行腳本
$ cat b.sh
#!/bin/bash
grep -i page /proc/meminfo
echo
for i in $(seq 100)
do
nohup??????? sqlplus -s scott/book <<EOF > /dev/null 2>&1 &
variable a number;
exec :a := 0;
alter session set "_serial_direct_read"=never;
select count(*) from t where 1=:a;
host sleep 10
commit;
quit;
EOF
done
echo sleep 5s
sleep 5
echo
grep -i page /proc/meminfo

3.測試使用hugepages的情況:

--// 執行b.sh腳本,exec :a := 0;
$ . b.sh
AnonPages:??????? 348784 kB
PageTables:??????? 63448 kB
AnonHugePages:???????? 0 kB
HugePages_Total:???? 305
HugePages_Free:??????? 3
HugePages_Rsvd:??????? 3
HugePages_Surp:????? 201
Hugepagesize:?????? 2048 kB

sleep 5s

AnonPages:??????? 983640 kB
PageTables:?????? 118716 kB
AnonHugePages:???????? 0 kB
HugePages_Total:???? 305
HugePages_Free:??????? 3
HugePages_Rsvd:??????? 3
HugePages_Surp:????? 201
Hugepagesize:?????? 2048 kB

--//條件1=0,不用訪問表
--//說明:開啟100個會話,僅僅執行select count(*) from t where 1=:a;頁面表
--//從63448kb變化到 118716kB. 118716-63448 = 55268,每個會話消耗550K.
--//這個是我以前測試沒有注意的問題.

--//修改b.sh換成exec :a := 1;訪問表之外,與前面沒有區別:
--// 執行b.sh腳本,exec :a := 0;
$ . b.sh
AnonPages:??????? 348788 kB
PageTables:??????? 63404 kB
AnonHugePages:???????? 0 kB
HugePages_Total:???? 305
HugePages_Free:??????? 3
HugePages_Rsvd:??????? 3
HugePages_Surp:????? 201
Hugepagesize:?????? 2048 kB

sleep 5s

AnonPages:??????? 983548 kB
PageTables:?????? 118948 kB
AnonHugePages:???????? 0 kB
HugePages_Total:???? 305
HugePages_Free:??????? 3
HugePages_Rsvd:??????? 3
HugePages_Surp:????? 201
Hugepagesize:?????? 2048 kB

--//你可以對比發現2個差距不大,使用hugepages后,訪問表與不訪問表PageTables的變化很小.

4.測試不使用hugepages的情況:
--//重啟數據庫./tmp/book.ora 參數修改不使用hugepages看看.
--//*.use_large_pages='FALSE'

SYS@book> startup? pfile='/tmp/book.ora';
ORACLE instance started.
Total System Global Area? 634732544 bytes
Fixed Size????????????????? 2255792 bytes
Variable Size???????????? 197133392 bytes
Database Buffers????????? 427819008 bytes
Redo Buffers??????????????? 7524352 bytes
Database mounted.
Database opened.

SYS@book> show parameter use_large_pages
NAME??????????? TYPE?? VALUE
--------------- ------ ------
use_large_pages string FALSE

--// 執行b.sh腳本,exec :a := 0;
$ . b.sh
AnonPages:??????? 265200 kB
PageTables:??????? 63952 kB
AnonHugePages:???????? 0 kB
HugePages_Total:???? 104
HugePages_Free:????? 104
HugePages_Rsvd:??????? 0
HugePages_Surp:??????? 0
Hugepagesize:?????? 2048 kB

sleep 5s

AnonPages:??????? 905808 kB
PageTables:?????? 148476 kB
AnonHugePages:???????? 0 kB
HugePages_Total:???? 104
HugePages_Free:????? 104
HugePages_Rsvd:??????? 0
HugePages_Surp:??????? 0
Hugepagesize:?????? 2048 kB
--//說明:開啟100個會話,僅僅執行select count(*) from t where 1=:a;頁面表
--//從63952kb變化到148476 kB. 148476-63952 = 84524 kb,每個會話消耗840K.但是前面使用hupages每個會話僅僅消耗550K,
--//這樣如果會話很多累積起來差別還是很大的.

--// 執行b.sh腳本,exec :a := 1;
$ . b.sh
AnonPages:??????? 268096 kB
PageTables:??????? 64276 kB
AnonHugePages:???????? 0 kB
HugePages_Total:???? 104
HugePages_Free:????? 104
HugePages_Rsvd:??????? 0
HugePages_Surp:??????? 0
Hugepagesize:?????? 2048 kB

sleep 5s

AnonPages:??????? 904432 kB
PageTables:?????? 215188 kB
AnonHugePages:???????? 0 kB
HugePages_Total:???? 104
HugePages_Free:????? 104
HugePages_Rsvd:??????? 0
HugePages_Surp:??????? 0
Hugepagesize:?????? 2048 kB

--//你可以發現訪問表與不訪問表,PageTables變化148476 kB =>215188 kB.存在很大差距.

--//以下計算存在很大偏差,不過還是能說明問題:

--//(215188-64276)-(148476-63952) = 66388,使用hugepages,訪問表頁面表增加66388.
--//(118948-63404)-(118716-63448) = 276,? 不使用hugepages,訪問表頁面表增加276.

--//66388/276 = 240.53623188405797101449
--//可以發現對比訪問數據與不訪問數據,hugepages存在240倍差距.當然我這樣計算誤差很大.哈哈也不知道這樣算是否有問題.

--//我的數據庫使用手工管理內存,全部參數手工設置,這樣內存的分配是連續.看看內存分配情況.參考鏈接:http://blog.itpub.net/267265/viewspace-2136689/

SYS@book> @ &r/memalloc

MIN(BASEADDR)??? MAX(BASEADDR)????? GRANULES???????? MB? GRANFLAGS COMPONENT??????????????????????? GRANSTATE
---------------- ---------------- ---------- ---------- ---------- -------------------------------- ----------------
0000000060C00000 000000007A000000??????? 102??????? 408????????? 4 DEFAULT buffer cache???????????? ALLOC
000000007A400000 000000007A400000????????? 1????????? 4????????? 4 java pool??????????????????????? ALLOC
000000007A800000 000000007B000000????????? 3???????? 12????????? 4 large pool?????????????????????? ALLOC
000000007B400000 0000000085C00000???????? 43??????? 172????????? 4 shared pool????????????????????? ALLOC

SYS@book> show parameter db_cache_size
NAME????????? TYPE??????? VALUE
------------- ----------- ------
db_cache_size big integer 408M

--//gransize=4*1024*1024=4194304
--//4194304 = 0x400000
--//0x7A400000+0x400000= 0x7A400000
--//可以看出緩存分配范圍0x0000000060C00000-0x000000007A400000.
--//轉換10進制 0x60C00000=1623195648, 0x7A400000=2051014656.
SYS@book> select OBJECT_ID,DATA_OBJECT_ID from dba_objects where owner='SCOTT' and object_name='T';

?OBJECT_ID DATA_OBJECT_ID
---------- --------------
???? 90610????????? 90610

SELECT xx, COUNT (*)
??? FROM (SELECT ROUND ( (TO_NUMBER (ba, 'xxxxxxxxxxxxxxxxxxxxxx') - 1623195648) / (2 * 1024 * 1024) ,0) xx
??????????? FROM x$bh
?????????? WHERE obj = 90610 AND state <> 0)
GROUP BY rollup(xx)
ORDER BY xx;

??????? XX?? COUNT(*)
---------- ----------
??????? 27???????? 18
??????? 29???????? 44
??????? 31??????? 120
??????? 32???????? 21
??????? 33??????? 153
??????? 34???????? 50
??????? 35??????? 172
??????? 36??????? 106
??????? 37??????? 208
??????? 38??????? 204
??????? 39??????? 256
??????? 40??????? 227
??????? 41??????? 256
??????? 42??????? 230
??????? 43??????? 256
??????? 44??????? 234
??????? 45??????? 256
??????? 46??????? 234
??????? 47??????? 256
??????? 48??????? 234
??????? 49??????? 256
??????? 50??????? 234
??????? 51??????? 256
??????? 52??????? 234
??????? 53??????? 256
??????? 54??????? 234
??????? 55??????? 256
??????? 56??????? 234
??????? 57??????? 256
??????? 58??????? 234
??????? 59??????? 256
??????? 60??????? 234
??????? 61??????? 256
??????? 62??????? 234
??????? 63??????? 256
??????? 64??????? 234
??????? 65??????? 256
??????? 66??????? 234
??????? 67??????? 256
??????? 68??????? 234
??????? 69??????? 256
??????? 70??????? 234
??????? 71??????? 256
??????? 72??????? 234
??????? 73??????? 256
??????? 74??????? 234
??????? 75??????? 256
??????? 76??????? 234
??????? 77??????? 256
??????? 78??????? 234
??????? 79??????? 256
??????? 80??????? 234
??????? 81??????? 256
??????? 82??????? 234
??????? 83??????? 256
??????? 84??????? 234
??????? 85??????? 256
??????? 86??????? 234
??????? 87??????? 256
??????? 88??????? 234
??????? 89??????? 256
??????? 90??????? 234
??????? 91??????? 256
??????? 92??????? 234
??????? 93??????? 256
??????? 94??????? 234
??????? 95??????? 256
??????? 96??????? 234
??????? 97??????? 256
??????? 98??????? 234
??????? 99??????? 256
?????? 100??????? 234
?????? 101??????? 256
?????? 102??????? 234
?????? 103??????? 256
?????? 104??????? 234
?????? 105??????? 256
?????? 106??????? 234
?????? 107??????? 256
?????? 108??????? 234
?????? 109??????? 256
?????? 110??????? 234
?????? 111??????? 256
?????? 112??????? 234
?????? 113??????? 256
?????? 114??????? 234
?????? 115??????? 256
?????? 116??????? 234
?????? 117??????? 256
?????? 118??????? 234
?????? 119??????? 256
?????? 120??????? 234
?????? 121??????? 256
?????? 122??????? 234
?????? 123??????? 256
?????? 124??????? 234
?????? 125??????? 256
?????? 126??????? 234
?????? 127??????? 256
?????? 128??????? 234
?????? 129??????? 256
?????? 130??????? 234
?????? 131??????? 256
?????? 132??????? 234
?????? 133??????? 256
?????? 134??????? 234
?????? 135??????? 256
?????? 136??????? 232
?????? 137??????? 256
?????? 138??????? 234
?????? 139??????? 256
?????? 140??????? 234
?????? 141??????? 256
?????? 142??????? 234
?????? 143??????? 256
?????? 144??????? 234
?????? 145??????? 256
?????? 146??????? 234
?????? 147??????? 256
?????? 148??????? 234
?????? 149??????? 256
?????? 150??????? 234
?????? 151??????? 256
?????? 152??????? 234
?????? 153??????? 256
?????? 154??????? 234
?????? 155??????? 256
?????? 156??????? 234
?????? 157??????? 256
?????? 158??????? 234
?????? 159??????? 256
?????? 160??????? 234
?????? 161??????? 256
?????? 162??????? 234
?????? 163??????? 220
?????? 164??????? 200
?????? 165???????? 88
?????? 166???????? 81
?????? 167???????? 10
??????????????? 32062

140 rows selected.
--//頁面表占用139項(如果使用hugepages).占用數據塊數 32062.
--//如果使用4k的頁面表.32062*8192/4096? = 64124.
--//64128/139? = 461.35251798561151079136.為什么不是前面計算240被.
--//如果你仔細看查詢x$bh輸出,就可以發現問題,我數據庫重啟后(不使用hugepages)看到的數據分布,它全部集中在前面.
--//而我前面使用hugepages時機器很長時間沒有關閉數據庫,我估計數據會均勻分布在這個數據緩存,這樣我的測試環境
--//db_cache_size=408M,這樣頁面表應該接近204項.
--//64128/204 = 314.35294117647058823529,還是存在很大誤差.或許我這樣計算存在問題...^_^.

5.測試不使用hugepages的情況,建立索引的情況呢?
SCOTT@book> alter table t modify (id not null);
Table altered.

SCOTT@book> create index i_t_id on t(id);
Index created.

--// 執行b.sh腳本,exec :a := 0;
$ . b.sh
AnonPages:??????? 282884 kB
PageTables:??????? 67876 kB
AnonHugePages:???????? 0 kB
HugePages_Total:???? 104
HugePages_Free:????? 104
HugePages_Rsvd:??????? 0
HugePages_Surp:??????? 0
Hugepagesize:?????? 2048 kB

sleep 5s

AnonPages:??????? 918952 kB
PageTables:?????? 152108 kB
AnonHugePages:???????? 0 kB
HugePages_Total:???? 104
HugePages_Free:????? 104
HugePages_Rsvd:??????? 0
HugePages_Surp:??????? 0
Hugepagesize:?????? 2048 kB

--// 執行b.sh腳本,exec :a := 1;
$ . b.sh
AnonPages:??????? 282840 kB
PageTables:??????? 67860 kB
AnonHugePages:???????? 0 kB
HugePages_Total:???? 104
HugePages_Free:????? 104
HugePages_Rsvd:??????? 0
HugePages_Surp:??????? 0
Hugepagesize:?????? 2048 kB

sleep 5s

AnonPages:??????? 917564 kB
PageTables:?????? 159184 kB
AnonHugePages:???????? 0 kB
HugePages_Total:???? 104
HugePages_Free:????? 104
HugePages_Rsvd:??????? 0
HugePages_Surp:??????? 0
Hugepagesize:?????? 2048 kB

--//可以建立索引后,執行計劃走索引全 INDEX FAST FULL SCAN.這樣訪問的塊明顯減少.
--//對比PageTables使用內存變化也不是很大,從另外一個訪問也說明一個問題,當應該優化不好PageTables占用空間也會很大.
--//當轉換使用hugepages直接把問題掩蓋起來.

5.測試不使用hugepages的情況,訪問表走直接路徑讀的情況呢?

--//清除數據緩存.
SYS@book> alter system flush buffer_cache;
System altered.

--// 修改腳本注解alter session set "_serial_direct_read"=never;.
$ cat b.sh
#!/bin/bash
grep -i page /proc/meminfo
echo
for i in $(seq 100)
do
nohup??????? sqlplus -s scott/book <<EOF > /dev/null 2>&1 &
variable a number;
exec :a := 0;
--//alter session set "_serial_direct_read"=never;
Select count(*) from t where 1=:a;
host sleep 10
commit;
quit;
EOF
done
echo sleep 5s
sleep 5
echo
grep -i page /proc/meminfo

--//執行b.sh腳本,exec :a := 0;
$ . b.sh
AnonPages:??????? 280592 kB
PageTables:??????? 68312 kB
AnonHugePages:???????? 0 kB
HugePages_Total:???? 104
HugePages_Free:????? 104
HugePages_Rsvd:??????? 0
HugePages_Surp:??????? 0
Hugepagesize:?????? 2048 kB

sleep 5s

AnonPages:??????? 917436 kB
PageTables:?????? 155852 kB
AnonHugePages:???????? 0 kB
HugePages_Total:???? 104
HugePages_Free:????? 104
HugePages_Rsvd:??????? 0
HugePages_Surp:??????? 0
Hugepagesize:?????? 2048 kB

--//執行b.sh腳本,exec :a := 1;
$ . b.sh
AnonPages:??????? 281504 kB
PageTables:??????? 68348 kB
AnonHugePages:???????? 0 kB
HugePages_Total:???? 104
HugePages_Free:????? 104
HugePages_Rsvd:??????? 0
HugePages_Surp:??????? 0
Hugepagesize:?????? 2048 kB

sleep 5s

AnonPages:?????? 1129404 kB
PageTables:?????? 159832 kB
AnonHugePages:???????? 0 kB
HugePages_Total:???? 104
HugePages_Free:????? 104
HugePages_Rsvd:??????? 0
HugePages_Surp:??????? 0
Hugepagesize:?????? 2048 kB

--//對比可以發現走直接路徑讀的情況下,PageTables基本沒有變化.

--//但愿這個帖子能說明hugepage的好處.有許多錯誤希望大家指正...
--//以前寫的帖子問題多多,希望看我博客的朋友不要見怪..哈哈.

轉載于:https://www.cnblogs.com/lfree/p/8818575.html

總結

以上是生活随笔為你收集整理的[201804012]关于hugepages 3.txt的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。