Sybaseでテーブル分割とその検証　〜その２　１つのデバイスでテーブル分割してみる〜

Sybaseでテーブル分割とその検証

〜その２　１つのデバイスでテーブル分割してみる〜

０．更新履歴

2002.04.24　新規作成

１．はじめに

　このドキュメントでは，ASE11.0.3.3#ESD6にて，テーブル分割を行う手順と，そのパフォーマンスについて検証した結果について説明する．

　なお，「テーブル分割」とは，Oracleでいうパーティションテーブルのことで，概略はドキュメント「テーブルのパーティション分割」を参照のこと．

２．前回のおさらいと今回のテストの説明

前回のテストでは，物理的に３つのデータベースファイル（デバイスファイル）を作成して，パーティションを３つに分け検証した．

デバイス名	サイズ
`PTest1_dat01`	5MB
`PTest1_dat02`	5MB
`PTest1_dat03`	5MB

今回は，１つのデバイスファイルで構成される，同じ容量で構成したセグメントにテーブルを作成して，同様のテストを尾行ってみる．

デバイス名	サイズ
`PTest1_dat04`	15MB

デバイスが，物理的に分かれていないが，テーブル分割する事によって効果があるのかを検証する．

３．準備

デバイスを作成する．

bash$ isql -Usa -P -STESTDB -Jsjis -zjapanese
1> set language us_english
2> go
1> print   'Create PTest_dat04 15MB'
2> declare @vdevno int
3> select @vdevno = max(convert(tinyint, substring(convert(binary(4),d.low), v.low,
1))) + 1
4> from master.dbo.sysdevices d, master.dbo.spt_values v
5> where v.type = 'E' and v.number = 3
6> disk init name='PTest_dat04',
7>   physname='/opt/sybase/database/datdevice/PTest_dat04.dat',
8>   vdevno = @vdevno ,
9>   size=7680
10> go
01:2002/04/24 16:17:13.18 kernel  Initializing virtual device 15, '/opt/sybase/data
base/datdevice/PTest_dat04.dat'
01:2002/04/24 16:17:13.18 kernel  Virtual device 15 started using standard unix i/o.
Create PTest_dat04 5MB
(1 row affected)
1>

作成したデバイスを，データベースに追加する．

1> alter database TESTDB
2> on PTest_dat04 = 15
3> go
Extending database by 7680 pages on disk PTest_dat04
1>

PTest15M_segを，PTest_dat04上に作成する．

1> use TESTDB
2> go
1> sp_addsegment PTest15M_seg,TESTDB,PTest_dat04 
2> go
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
Segment created. 

(return status = 0)
1>

デフォルトセグメントを削除する．

1> sp_dropsegment "default",TESTDB,PTest_dat04
2> go
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
Segment reference to device dropped.
(return status = 0)
1>

システムセグメントを削除する．

1> sp_dropsegment system,TESTDB,PTest_dat04
2> go
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
Segment reference to device dropped.
(return status = 0)
1>

作成したセグメントを確認してみる．

1> sp_helpsegment
2> go
 segment name                           status 
 ------- ------------------------------ ------ 
       0 system                              0 
       1 default                             1 
       2 logsegment                          0 
       3 dat_seg                             0 
       4 idx_seg                             0 
       5 PTest_seg                           0 
       6 NTest_seg                           0 
       7 PTest15M_seg                        0 

(return status = 0)
1>

セグメントの詳細を確認してみる．

1> sp_helpsegment PTest15M_seg
2> go
 segment name                           status 
 ------- ------------------------------ ------ 
       7 PTest15M_seg                        0 

 device                         size                   free_pages  
 ------------------------------ ---------------------- ----------- 
 PTest_dat04                    15.0MB                        7680 

(return status = 0)
1>

サイズ15ＭＢでセグメントが作成されている．
パーティションテーブルを作成する．

1> create table TestTable15M    
2> (
3> fieldA numeric(10,0) identity,
4> fieldB char(10)
5> ) on PTest15M_seg
6> go
1>

作成したテーブルを確認してみる．

1> sp_help TestTable15M
2> go
 Name                           Owner                         
         Type                   
 ------------------------------ ------------------------------ 
        ---------------------- 
 TestTable15M                   dbo                           
         user table             

 Data_located_on_segment        When_created               
 ------------------------------ -------------------------- 
 PTest15M_seg                          Apr 24 2002  4:30PM

 Column_name     Type            Length Prec Scale Nulls Default_name   
         Rule_name       Identity 
 --------------- --------------- ------ ---- ----- ----- --------------- 
        --------------- -------- 
 fieldA          numeric              6   10     0     0 NULL           
         NULL                   1 
 fieldB          char                10 NULL  NULL     0 NULL           
         NULL                   0 
Object does not have any indexes.
No defined keys for this object.
Object is not partitioned.

(return status = 0)
1>

alter table文で，作成したテーブル分割を行う．

1> alter table TestTable15M partition 3
2> go
1>

alter table partitionを行う事で，各デバイスに分割されてテーブルが格納される．
再度，テーブルの情報を確認する．

1> sp_help TestTable15M
2> go
 Name                           Owner                         
         Type                   
 ------------------------------ ------------------------------ 
        ---------------------- 
 TestTable15M                   dbo                           
         user table    
         
 Data_located_on_segment        When_created               
 ------------------------------ -------------------------- 
 PTest15M_seg                          Apr 22 2002  8:14PM 

 Column_name     Type            Length Prec Scale Nulls Default_name   
         Rule_name       Identity 
 --------------- --------------- ------ ---- ----- ----- --------------- 
        --------------- -------- 
 fieldA          numeric              6   10     0     0 NULL           
         NULL                   1 
 fieldB          char                10 NULL  NULL     0 NULL           
         NULL                   0 
Object does not have any indexes.
No defined keys for this object.

 partitionid firstpage   controlpage 
 ----------- ----------- ----------- 
           1       25602       25603 
           2       25608       25609 
           3       25616       25617 

(return status = 0)
1>

テーブルが，３つに分割されている事がわかる．

４．直列時の計測テスト

　この章での計測方法は，１プロセスだけでデータベースにI/O負荷をかけ，未分割のテーブルと分割テーブルの性能差について検証する．

４．１．直列でinsert文でデータ投入してみる

テーブル分割したテーブルにデータを追加する．

1> set nocount on 
2> go
1> declare @c1 int
2> select @c1 = 1
3> print "Start"
4> select convert(char(12),getdate(),108)
5> while @c1 < 5000
6> begin
7>         insert into TestTable15M values("aaa")
8>         select @c1 = @c1 + 1
9> end
10> print "End"
11> select convert(char(12),getdate(),108)
12> go
Start

 ------------ 
 16:35:17     
End

 ------------ 
 16:36:03     
1>

46秒．
ファイルが分かれていて，テーブル分割した前回のテストでは，同じテストで47秒だった．
微妙ではあるが，分割を行っていないほうが速い．
よって，前回のテストの結論の「デバイスの切り替えを行うオーバヘッド分，ファイルが分かれている方が若干遅くなっている」という推定に一致していると考えられる．

４．２．直列でデータを削除してみる

テーブル分割を行っているテーブルのデータを削除する．

1> select convert(char(12),getdate(),108)
2> delete TestTable15MB
3> select convert(char(12),getdate(),108)
4> go
              
 ------------ 
 16:52:47    
              
 ------------ 
 16:52:50     
1>

３秒．
前回のテストでは，2秒だったため，これはテーブル分割が行われていても，１ファイル上にあるため，I/O Waitに消費した時間だと思われる．

３．１．並列テスト用のプログラムを準備する

前回のテストで作成したinsertTable.sqlファイルのテーブル名を修正する．

`insertTable.sql`
bash$ cat insertTable.sql declare @c1 int select @c1 = 1 print "Start" while @c1 < 5000 begin insert into TestTable15M values("aaa") select @c1 = @c1 + 1 end print "End" go bash$

その他のシェル等は，前回のテストので利用したものが再利用できる．

５．２．シェルを実行してみる

作成したシェルを実行して，計測を行う．

[sybase@poweredge sybase]$ insertTable.Multi.sh
Wed Apr 24 17:00:17 JST 2002
Wed Apr 24 17:00:17 JST 2002
Wed Apr 24 17:00:17 JST 2002
Wed Apr 24 17:00:17 JST 2002
Wed Apr 24 17:00:17 JST 2002
Wed Apr 24 17:01:46 JST 2002
Wed Apr 24 17:01:46 JST 2002
Wed Apr 24 17:01:46 JST 2002
Wed Apr 24 17:01:47 JST 2002
Wed Apr 24 17:01:47 JST 2002

[sybase@poweredge sybase]$

90秒．
そんな感じで計測する

６．まとめ

計測の結果は，次の表のようになった．

１ファイルでテーブル分割したテーブル
	開始時間	終了時間	実行時間	実行時間（秒）
5個１回目	17:00:17	17:01:47	00:01:30	90
5個２回目	17:28:37	17:30:07	00:01:30	90
5個３回目	17:31:53	17:33:24	00:01:31	91
平均				90

前回検証した，３つのデバイスファイルを使って，パーティションを３つとした場合の検証データと見比べてみる．

前回検証した，３ファイルでテーブル分割したテーブル
	開始時間	終了時間	実行時間	実行時間（秒）
分割1回目	18:41:00	18:42:29	00:01:29	89
分割2回目	18:45:31	18:46:59	00:01:28	88
分割3回目	18:51:33	18:53:02	00:01:29	89
平均				89

前回が89秒，今回が90秒なので，やはり１つのファイルのオープンに関するI/O待ち分，今回のパターンが遅いようである．
よって，テーブル分割する場合は，デバイスファイルを分ける事をお勧めする．