Home > all dump 1000結果のログ

all dump 1000結果のログ

May 7, 2008 1:19 PM

まっさらな状態から1000行追加した状態

2008-05-07 11:31:22 [MgmSrvr] INFO -- Node 2: Data usage is 2%(62 32K pages of total 2560)
2008-05-07 11:31:22 [MgmSrvr] INFO -- Node 2: Index usage is 0%(22 8K pages of total 2336)
2008-05-07 11:31:22 [MgmSrvr] INFO -- Node 2: Resource 0 min: 0 max: 639 curr: 0
2008-05-07 11:31:22 [MgmSrvr] INFO -- Node 3: Data usage is 2%(62 32K pages of total 2560)
2008-05-07 11:31:22 [MgmSrvr] INFO -- Node 3: Index usage is 0%(22 8K pages of total 2336)
2008-05-07 11:31:22 [MgmSrvr] INFO -- Node 3: Resource 0 min: 0 max: 639 curr: 0
2008-05-07 11:31:23 [MgmSrvr] INFO -- Node 4: Data usage is 2%(74 32K pages of total 2560)
2008-05-07 11:31:23 [MgmSrvr] INFO -- Node 4: Index usage is 1%(24 8K pages of total 2336)
2008-05-07 11:31:23 [MgmSrvr] INFO -- Node 4: Resource 0 min: 0 max: 639 curr: 0
2008-05-07 11:31:23 [MgmSrvr] INFO -- Node 5: Data usage is 2%(74 32K pages of total 2560)
2008-05-07 11:31:23 [MgmSrvr] INFO -- Node 5: Index usage is 1%(24 8K pages of total 2336)
2008-05-07 11:31:23 [MgmSrvr] INFO -- Node 5: Resource 0 min: 0 max: 639 curr: 0

1000行追加して、2000行になった状態

2008-05-07 11:43:16 [MgmSrvr] INFO -- Node 2: Data usage is 3%(82 32K pages of total 2560)
2008-05-07 11:43:16 [MgmSrvr] INFO -- Node 2: Index usage is 0%(22 8K pages of total 2336)
2008-05-07 11:43:16 [MgmSrvr] INFO -- Node 2: Resource 0 min: 0 max: 639 curr: 0
2008-05-07 11:43:16 [MgmSrvr] INFO -- Node 3: Data usage is 3%(82 32K pages of total 2560)
2008-05-07 11:43:16 [MgmSrvr] INFO -- Node 3: Index usage is 0%(22 8K pages of total 2336)
2008-05-07 11:43:16 [MgmSrvr] INFO -- Node 3: Resource 0 min: 0 max: 639 curr: 0
2008-05-07 11:43:16 [MgmSrvr] INFO -- Node 4: Data usage is 3%(84 32K pages of total 2560)
2008-05-07 11:43:16 [MgmSrvr] INFO -- Node 4: Index usage is 1%(24 8K pages of total 2336)
2008-05-07 11:43:16 [MgmSrvr] INFO -- Node 4: Resource 0 min: 0 max: 639 curr: 0
2008-05-07 11:43:16 [MgmSrvr] INFO -- Node 5: Data usage is 3%(84 32K pages of total 2560)
2008-05-07 11:43:16 [MgmSrvr] INFO -- Node 5: Index usage is 1%(24 8K pages of total 2336)
2008-05-07 11:43:16 [MgmSrvr] INFO -- Node 5: Resource 0 min: 0 max: 639 curr: 0

mysql> delete from t1 limit 1000;
で1000行削除した状態

2008-05-07 11:50:39 [MgmSrvr] INFO -- Node 2: Data usage is 3%(82 32K pages of total 2560)
2008-05-07 11:50:39 [MgmSrvr] INFO -- Node 2: Index usage is 0%(22 8K pages of total 2336)
2008-05-07 11:50:39 [MgmSrvr] INFO -- Node 2: Resource 0 min: 0 max: 639 curr: 0
2008-05-07 11:50:39 [MgmSrvr] INFO -- Node 3: Data usage is 3%(82 32K pages of total 2560)
2008-05-07 11:50:39 [MgmSrvr] INFO -- Node 3: Index usage is 0%(22 8K pages of total 2336)
2008-05-07 11:50:39 [MgmSrvr] INFO -- Node 3: Resource 0 min: 0 max: 639 curr: 0
2008-05-07 11:50:39 [MgmSrvr] INFO -- Node 4: Data usage is 3%(84 32K pages of total 2560)
2008-05-07 11:50:39 [MgmSrvr] INFO -- Node 4: Index usage is 1%(24 8K pages of total 2336)
2008-05-07 11:50:39 [MgmSrvr] INFO -- Node 4: Resource 0 min: 0 max: 639 curr: 0
2008-05-07 11:50:40 [MgmSrvr] INFO -- Node 5: Data usage is 3%(84 32K pages of total 2560)
2008-05-07 11:50:40 [MgmSrvr] INFO -- Node 5: Index usage is 1%(24 8K pages of total 2336)
2008-05-07 11:50:40 [MgmSrvr] INFO -- Node 5: Resource 0 min: 0 max: 639 curr: 0

そこへ1000行追加した状態(データは2000行)

2008-05-07 11:51:16 [MgmSrvr] INFO -- Node 2: Local checkpoint 124 started. Keep GCI = 179891 oldest restorable GCI = 179944
2008-05-07 11:51:28 [MgmSrvr] INFO -- Node 2: Data usage is 3%(82 32K pages of total 2560)
2008-05-07 11:51:28 [MgmSrvr] INFO -- Node 2: Index usage is 0%(22 8K pages of total 2336)
2008-05-07 11:51:28 [MgmSrvr] INFO -- Node 2: Resource 0 min: 0 max: 639 curr: 0
2008-05-07 11:51:28 [MgmSrvr] INFO -- Node 3: Data usage is 3%(82 32K pages of total 2560)
2008-05-07 11:51:28 [MgmSrvr] INFO -- Node 3: Index usage is 0%(22 8K pages of total 2336)
2008-05-07 11:51:28 [MgmSrvr] INFO -- Node 3: Resource 0 min: 0 max: 639 curr: 0
2008-05-07 11:51:28 [MgmSrvr] INFO -- Node 4: Data usage is 3%(84 32K pages of total 2560)
2008-05-07 11:51:28 [MgmSrvr] INFO -- Node 4: Index usage is 1%(24 8K pages of total 2336)
2008-05-07 11:51:28 [MgmSrvr] INFO -- Node 4: Resource 0 min: 0 max: 639 curr: 0
2008-05-07 11:51:28 [MgmSrvr] INFO -- Node 5: Data usage is 3%(84 32K pages of total 2560)
2008-05-07 11:51:28 [MgmSrvr] INFO -- Node 5: Index usage is 1%(24 8K pages of total 2336)
2008-05-07 11:51:28 [MgmSrvr] INFO -- Node 5: Resource 0 min: 0 max: 639 curr: 0

そこへ1000行追加した状態(データは3000行)

2008-05-07 13:05:06 [MgmSrvr] INFO -- Node 2: Local checkpoint 125 started. Keep GCI = 181081 oldest restorable GCI = 181083
2008-05-07 13:05:14 [MgmSrvr] INFO -- Node 2: Data usage is 3%(82 32K pages of total 2560)
2008-05-07 13:05:14 [MgmSrvr] INFO -- Node 2: Index usage is 1%(24 8K pages of total 2336)
2008-05-07 13:05:14 [MgmSrvr] INFO -- Node 2: Resource 0 min: 0 max: 639 curr: 0
2008-05-07 13:05:14 [MgmSrvr] INFO -- Node 3: Data usage is 3%(82 32K pages of total 2560)
2008-05-07 13:05:14 [MgmSrvr] INFO -- Node 3: Index usage is 1%(24 8K pages of total 2336)
2008-05-07 13:05:14 [MgmSrvr] INFO -- Node 3: Resource 0 min: 0 max: 639 curr: 0
2008-05-07 13:05:14 [MgmSrvr] INFO -- Node 4: Data usage is 3%(94 32K pages of total 2560)
2008-05-07 13:05:14 [MgmSrvr] INFO -- Node 4: Index usage is 1%(25 8K pages of total 2336)
2008-05-07 13:05:14 [MgmSrvr] INFO -- Node 4: Resource 0 min: 0 max: 639 curr: 0
2008-05-07 13:05:14 [MgmSrvr] INFO -- Node 5: Data usage is 3%(94 32K pages of total 2560)
2008-05-07 13:05:14 [MgmSrvr] INFO -- Node 5: Index usage is 1%(25 8K pages of total 2336)
2008-05-07 13:05:14 [MgmSrvr] INFO -- Node 5: Resource 0 min: 0 max: 639 curr: 0

最後にndb_desc

[root@h1 mysql-cluster]# /usr/local/mysql/bin/ndb_desc -c 192.168.47.128 t1 -d mysqlslap -p
-- t1 --
Version: 1
Fragment type: 5
K Value: 6
Min load factor: 78
Max load factor: 80
Temporary table: no
Number of attributes: 11
Number of primary keys: 1
Length of frm data: 382
Row Checksum: 1
Row GCI: 1
SingleUserMode: 0
ForceVarPart: 1
TableStatus: Retrieved
-- Attributes --
id Varchar(32;latin1_swedish_ci) PRIMARY KEY DISTRIBUTION KEY AT=SHORT_VAR ST=MEMORY
intcol1 Int NULL AT=FIXED ST=MEMORY
intcol2 Int NULL AT=FIXED ST=MEMORY
intcol3 Int NULL AT=FIXED ST=MEMORY
intcol4 Int NULL AT=FIXED ST=MEMORY
intcol5 Int NULL AT=FIXED ST=MEMORY
charcol1 Varchar(128;latin1_swedish_ci) NULL AT=SHORT_VAR ST=MEMORY
charcol2 Varchar(128;latin1_swedish_ci) NULL AT=SHORT_VAR ST=MEMORY
charcol3 Varchar(128;latin1_swedish_ci) NULL AT=SHORT_VAR ST=MEMORY
charcol4 Varchar(128;latin1_swedish_ci) NULL AT=SHORT_VAR ST=MEMORY
charcol5 Varchar(128;latin1_swedish_ci) NULL AT=SHORT_VAR ST=MEMORY

-- Indexes --
PRIMARY KEY(id) - UniqueHashIndex
PRIMARY(id) - OrderedIndex

-- Per partition info --
Partition Row count Commit count Frag fixed memory Frag varsized memory
0 763 1355 65536 655360
1 800 1184 65536 655360
3 685 1197 65536 655360
2 752 1264 65536 655360

NDBT_ProgramExit: 0 - OK

-----

INSERTを繰り返して、10000件のデータとなった状態

-- Indexes --
PRIMARY KEY(id) - UniqueHashIndex
PRIMARY(id) - OrderedIndex

-- Per partition info --
Partition Row count Commit count Frag fixed memory Frag varsized memory
0 2515 3107 131072 1966080
2 2572 3084 131072 1966080
1 2507 2891 131072 1966080
3 2406 2918 131072 1966080

NDBT_ProgramExit: 0 - OK

all dump 1000の結果

2008-05-07 14:45:43 [MgmSrvr] INFO -- Node 2: Data usage is 6%(166 32K pages of total 2560)
2008-05-07 14:45:43 [MgmSrvr] INFO -- Node 2: Index usage is 1%(28 8K pages of total 2336)
2008-05-07 14:45:43 [MgmSrvr] INFO -- Node 2: Resource 0 min: 0 max: 639 curr: 0
2008-05-07 14:45:43 [MgmSrvr] INFO -- Node 3: Data usage is 6%(166 32K pages of total 2560)
2008-05-07 14:45:43 [MgmSrvr] INFO -- Node 3: Index usage is 1%(28 8K pages of total 2336)
2008-05-07 14:45:43 [MgmSrvr] INFO -- Node 3: Resource 0 min: 0 max: 639 curr: 0
2008-05-07 14:45:43 [MgmSrvr] INFO -- Node 4: Data usage is 6%(178 32K pages of total 2560)
2008-05-07 14:45:43 [MgmSrvr] INFO -- Node 4: Index usage is 1%(31 8K pages of total 2336)
2008-05-07 14:45:43 [MgmSrvr] INFO -- Node 4: Resource 0 min: 0 max: 639 curr: 0
2008-05-07 14:45:43 [MgmSrvr] INFO -- Node 5: Data usage is 6%(178 32K pages of total 2560)
2008-05-07 14:45:43 [MgmSrvr] INFO -- Node 5: Index usage is 1%(31 8K pages of total 2336)
2008-05-07 14:45:43 [MgmSrvr] INFO -- Node 5: Resource 0 min: 0 max: 639 curr: 0

---

32Kページに、15行くらい入る計算になる。

1行：2200バイト

Partition 0, 1:5022行、166ページ
Partition 2, 3:4978行、178ページ

-----

INT: 4*5=20
VARCHAR: (12+12)+5*(128+12)=724
オーバーヘッド：16
Total 760 byte

(32*1024 - 128) / 760 = 43行：1ページに入るデータの量

合計233ページ必要なはず

344ページ利用中ということは、286～344ページが実際に利用されている計算になる。
（20%ずつページを増やしていくので）

2515rows: 60 pages
1page:

all dump 1000結果のログ

関連エントリー