在具有SAN的AIX上加载性能 [英] Load performance on AIX w/ SAN
问题描述
大家好，
除非我有一个非常复杂的问题，否则我几乎不会发帖，
所以这个应该是好的......
我正在尝试使用SAN进行存储，将AIX服务器上的数据库加载到
DB2 v9.1数据库中。该表有几个CLOB
（小的clobs，但我们以非原生格式存储XML数据）。
这是我正在使用的加载命令：
db2"从chardel |修改的del的loadset1加载COLDEL&安培;将
插入testschema.testtable不可恢复的数据缓冲区240000
disk_parallelism 32"
现在我加载大约440行/秒，对我来说这是非常糟糕的b $ b慢。我加载的表空间有8个容器，而且我认为SAN上至少有30个磁盘，数据最终会依赖于b $ b。所以不用说，应该有可用于加载这些数据的所有I / O
电源。
我的数据文件我从生活中加载文件系统（和
因此逻辑卷相同），所以我知道IO headseek问题
可能是问题所在。但是，我理解SAN的方式是所有磁盘都在一起工作，因此SAN会处理数据分配，因此headseek不会成为问题。
我想我要问的是一个双重问题......首先（和这个论坛最合适的
），是我的加载命令最适合的
我想做什么？
其次，topas的输出是否表明磁盘是
真的只有平均工作率为20％？或者这是谎言，因为我在SAN上运行
？任何有类似经验的人都可以帮忙！
如果以下内容确实正确，为什么负载不使用所有的b $ b磁盘和/或CPU？ br />
Topas Monitor for host：server1 EVENTS / QUEUES FILE / TTY
Mon Nov 10 16:09:09 2008 Interval：2 Cswitch 8071
Readch 1520.1K
Syscall 5308
Writech 1976.3K
CPU用户％Kern％等待％空闲％读取168
Rawin 0
cpu1 69.5 6.5 0.0 24.0写入889
Ttyout 624
cpu2 1.0 4.0 7.5 87.5福克斯0
Igets 0
cpu4 0.5 4.0 21.5 74.0执行1
Namei 24
cpu0 0.5 3.0 0.0 96.5 Runqueue 1.0
Dirblk 0
cpu5 0.0 3.0 25.5 71.5 Waitqueue 0.0
cpu3 0.0 2.0 15.5 82.5
cpu6 0.0 2.0 15.0 83.0
PAGING MEMORY
cpu7 0.0 5.5 12.5 82.0
故障1032 Real，MB 24576
抢断390％比赛39.9
网络KBPS I-Pack O-Pack KB-In KB-Out PgspIn 0％
Noncomp 13.3
en6 0.8 1.0 1.5 0.1 0.7 PgspOut 0％
客户2.2
en5 0.0 0.0 0.0 0.0 0.0 PageIn 1267
lo0 0.0 0.0 0.0 0.0 0.0 PageOut 888
PAGING SPACE
Sios 2155
尺寸，MB 12032
磁盘忙％KBPS TPS KB读取KB写入％
使用0.0
hdisk17 18.0 944.0 236.0 518.0 426.0 NFS（呼叫/秒）％
免费100.0
hdisk27 14.5 1.0K 237.0 456.0 584.0 ServerV2 0
hdisk42 14。 0 780.0 195.0 384.0 396.0 ClientV2 0
按：
hdisk22 13.5 876.0 219.0 424.0 452.0 ServerV3 0" h"
求助
hdisk12 12.0 892.0 211.5 414.0 478.0 ClientV3 0" q"
退出
hdisk37 11.0 900.0 214.0 392.0 508.0
hdisk7 11.0 950.0 237.5 480.0 470.0
hdisk32 6.0 874.0 218.5 464.0 410.0
hdisk29 2.0 512.0 2.0 512.0 0.0
hdisk24 2.0 512.0 2.0 512.0 0.0
hdisk9 1.5 256.0 1.0 256.0 0.0
hdisk19 1.0 256.0 1.0 256.0 0.0
hdisk16 0.5 6.0 1.5 0.0 6.0
hdisk15 0.0 0.0 0.0 0.0 0.0
hdisk14 0.0 0.0 0.0 0.0 0.0
hdisk11 0.0 2.0 0.5 0.0 2.0
hdisk1 0.0 0.0 0.0 0.0 0.0
hdisk20 0 .0 0.0 0.0 0.0 0.0
名称PID CPU％PgSp所有者
db2sysc 1839138 10.7 0.6 tvpi01
db2sysc 574364 0.1 0.5 tvpi01
db2sysc 1188050 0.1 0.6 tvpi01
db2sysc 663620 0.1 0.7 tvpi01
db2sysc 1225124 0.1 0.6 tvpi01
db2sysc 290888 0.1 0.7 tvpi01
db2sysc 1552616 0.1 0.6 tvpi01
db2sysc 671780 0.1 0.6 tvpi01
db2sysc 1339656 0.1 0.6 tvpi01
db2sysc 1511780 0.1 0.6 tvpi01
db2sysc 1323334 0.1 0.6 tvpi01
db2sysc 1679524 0.1 0.6 tvpi01
topas 1564806 0.1 3.5 tvpi01
db2sysc 983414 0.1 0.6 tvpi01
db2sysc 143704 0.1 0.7 tvpi01
db2sysc 1216702 0.1 0.6 tvpi01
db2sysc 975286 0.0 0.6 tvpi01
db2sysc 872862 0.0 0.7 tvpi01
db2sysc 962762 0.0 0.6 tvpi01
db2sysc 1180054 0.0 0.6 tvpi01
< blockquote>发表我的帖子之后我意识到topas输出是多么困难
读取所以让我发布亮点：
8个CPU，1个工作在65 ％，全部徘徊在1％左右，等待％从5-25％。
20 +磁盘，10在10-20％繁忙，每次读写大约500kb /
s，其余几乎都没有做任何工作。
内存24GB，55％免费，0％页面空间使用
page分别输入/输出1267/888。
希望有所帮助！
rd ***** @ gmail.com 写道：
大家好，
除非我有一个非常复杂的问题，否则我几乎不会发帖，
所以这个应该是好的...
我我正试图对数据库进行加载在AIX服务器上使用SAN进行存储，将数据库转换为
DB2 v9.1数据库。该表有几个CLOB
（小的clobs，但我们以非原生格式存储XML数据）。
这是我正在使用的加载命令：
db2"从chardel |修改的del的loadset1加载COLDEL&安培;将
插入testschema.testtable不可恢复的数据缓冲区240000
disk_parallelism 32"
现在我加载大约440行/秒，对我来说这是非常糟糕的b $ b慢。我加载的表空间有8个容器，而且我认为SAN上至少有30个磁盘，数据最终会依赖于b $ b。所以不用说，应该有可用于加载这些数据的所有I / O
功率。
您是否可以发布更多信息，例如表格DDL，表格空间
定义，可能是一些样本行等？
您是否尝试过让DB2为
数据缓冲区/ disk_parallelism设置选择自己的默认值的负载？
11月10日下午6：16 *，Ian< ianb ... @ mobileaudio.comwrote：
rdud ... @ gmail.com写道：
大家好，
我几乎不会发帖子除非我是有一个非常复杂的问题，
所以这个应该是好的...
我正在尝试加载将AIX服务器上的数据库转换为使用SAN进行存储的
DB2 v9.1数据库。 *该表有几个CLOB
（小的clobs，但我们以非原生格式存储XML数据）。
这是我正在使用的加载命令：
db2" load from loaddel1 from del deldel COLDEL&安培;将
插入testschema.testtable不可恢复的数据缓冲区240000
disk_parallelism 32"
现在我加载大约440行/秒，这对我来说非常糟糕
慢。 *我加载的表空间有8个容器，我相信SAN上至少有30个磁盘，数据最终会依赖于b $ b。 *不用说，应该有可用于加载这些数据的所有I / O
功率。
您是否可以发布更多信息，例如表格DDL，表格空间
定义，可能是一些样本行等？
您是否尝试过让DB2为/ b
数据缓冲区/ disk_parallelism设置选择自己的默认值？ - 隐藏引用文本 -
- 显示引用的文本 -
我试图让数据库选择自己的加载值。我是
体验类似的表现。
我出于安全原因无法发布确切的DDL /样本行，但我会这样做
my最好描述：
表本身是其他子表的父表（但是这个
与负载无关）。它有大约25列，所有这些都是
是varchar，bigint，char或date，除了2，它们是CLOB
（大约3000个字符/ clob）。
链接到3个表空间，一个用于LOB（32k页面大小），一个用于
索引（4k页面大小），一个用于普通数据（4k页面大小） ）。所有3个
表空间都有8个容器并且存放在同一个文件系统上，这个文件系统安装在一个VG上，该VG连接到一个带有30+磁盘的SAN（可能在
100磁盘，但我完全不确定，因为我不是SAN adm）。
足够的信息可以提供帮助吗？
也是一个新发现！在将15mil记录加载到此表后，我
进行了一些表扫描并获得了topas中的磁盘使用情况以报告最多为
80％忙碌，所以它似乎是load真的没有使用磁盘的全部容量（因为负载大约占磁盘的15％）。
另外，tablescan统一使用所有8个CPU ，仅使用1或2个CPU，负载为
。
Hi all,
I hardly ever make a post unless I am having a very purplexing issue,
so this one should be good...
I am trying to do a load against a database on an AIX server into a
DB2 v9.1 database, using SAN for storage. The table has a few CLOBs
(smallish clobs but we are storing XML data in non-native format).
Here is the load command I am using:
db2 "load from loadset1 of del modified by chardel| coldel& insert
into testschema.testtable nonrecoverable data buffer 240000
disk_parallelism 32"
Now I am loading about 440 rows/second, which to me is abysmally
slow. The tablespaces I am loading into have 8 containers and I
believe there are at least 30 disks on the SAN that the data
eventually lives on. So needless to say there should be all the I/O
power available to load this data.
My data file I am loading from lives on the same filesystem (and
therefore same logical volume), so I am aware that IO headseek issues
could be the problem. However, the way I understand SAN is that all
the disks are working together, so SAN takes care of the data
allocation so that headseek does not become a problem.
I guess what I am asking is a twofold question...first (and the most
appropriate for this forum), is my load command most appopriate for
what I am trying to do?
Second, does the output of topas below indicate that the disks are
really only working at 20% on average? Or is it a lie since I am
running on SAN? Anyone with simular experience please help!
If what is below is indeed correct, why is the load not using all the
disk and/or CPU?
Topas Monitor for host: server1 EVENTS/QUEUES FILE/TTY
Mon Nov 10 16:09:09 2008 Interval: 2 Cswitch 8071
Readch 1520.1K
Syscall 5308
Writech 1976.3K
CPU User% Kern% Wait% Idle% Reads 168
Rawin 0
cpu1 69.5 6.5 0.0 24.0 Writes 889
Ttyout 624
cpu2 1.0 4.0 7.5 87.5 Forks 0
Igets 0
cpu4 0.5 4.0 21.5 74.0 Execs 1
Namei 24
cpu0 0.5 3.0 0.0 96.5 Runqueue 1.0
Dirblk 0
cpu5 0.0 3.0 25.5 71.5 Waitqueue 0.0
cpu3 0.0 2.0 15.5 82.5
cpu6 0.0 2.0 15.0 83.0
PAGING MEMORY
cpu7 0.0 5.5 12.5 82.0
Faults 1032 Real,MB 24576
Steals 390 % Comp 39.9
Network KBPS I-Pack O-Pack KB-In KB-Out PgspIn 0 %
Noncomp 13.3
en6 0.8 1.0 1.5 0.1 0.7 PgspOut 0 %
Client 2.2
en5 0.0 0.0 0.0 0.0 0.0 PageIn 1267
lo0 0.0 0.0 0.0 0.0 0.0 PageOut 888
PAGING SPACE
Sios 2155
Size,MB 12032
Disk Busy% KBPS TPS KB-Read KB-Writ %
Used 0.0
hdisk17 18.0 944.0 236.0 518.0 426.0 NFS (calls/sec) %
Free 100.0
hdisk27 14.5 1.0K 237.0 456.0 584.0 ServerV2 0
hdisk42 14.0 780.0 195.0 384.0 396.0 ClientV2 0
Press:
hdisk22 13.5 876.0 219.0 424.0 452.0 ServerV3 0 "h"
for help
hdisk12 12.0 892.0 211.5 414.0 478.0 ClientV3 0 "q"
to quit
hdisk37 11.0 900.0 214.0 392.0 508.0
hdisk7 11.0 950.0 237.5 480.0 470.0
hdisk32 6.0 874.0 218.5 464.0 410.0
hdisk29 2.0 512.0 2.0 512.0 0.0
hdisk24 2.0 512.0 2.0 512.0 0.0
hdisk9 1.5 256.0 1.0 256.0 0.0
hdisk19 1.0 256.0 1.0 256.0 0.0
hdisk16 0.5 6.0 1.5 0.0 6.0
hdisk15 0.0 0.0 0.0 0.0 0.0
hdisk14 0.0 0.0 0.0 0.0 0.0
hdisk11 0.0 2.0 0.5 0.0 2.0
hdisk1 0.0 0.0 0.0 0.0 0.0
hdisk20 0.0 0.0 0.0 0.0 0.0
Name PID CPU% PgSp Owner
db2sysc 1839138 10.7 0.6 tvpi01
db2sysc 574364 0.1 0.5 tvpi01
db2sysc 1188050 0.1 0.6 tvpi01
db2sysc 663620 0.1 0.7 tvpi01
db2sysc 1225124 0.1 0.6 tvpi01
db2sysc 290888 0.1 0.7 tvpi01
db2sysc 1552616 0.1 0.6 tvpi01
db2sysc 671780 0.1 0.6 tvpi01
db2sysc 1339656 0.1 0.6 tvpi01
db2sysc 1511780 0.1 0.6 tvpi01
db2sysc 1323334 0.1 0.6 tvpi01
db2sysc 1679524 0.1 0.6 tvpi01
topas 1564806 0.1 3.5 tvpi01
db2sysc 983414 0.1 0.6 tvpi01
db2sysc 143704 0.1 0.7 tvpi01
db2sysc 1216702 0.1 0.6 tvpi01
db2sysc 975286 0.0 0.6 tvpi01
db2sysc 872862 0.0 0.7 tvpi01
db2sysc 962762 0.0 0.6 tvpi01
db2sysc 1180054 0.0 0.6 tvpi01
After making my post I realize how difficult the topas output is to
read so let me post the highlights:
8 CPUs, 1 working at 65%, the all hovering near 1%, wait% from 5-25%.
20+disks, 10 at 10-20% busy, each reading and writing at around 500kb/
s, the rest hardly doing any work at all.
Memory 24GB, 55% free, 0% pageing space used
page in/out 1267/888 respectively.
Hope that helps!
rd*****@gmail.com wrote:Hi all,
I hardly ever make a post unless I am having a very purplexing issue,
so this one should be good...
I am trying to do a load against a database on an AIX server into a
DB2 v9.1 database, using SAN for storage. The table has a few CLOBs
(smallish clobs but we are storing XML data in non-native format).
Here is the load command I am using:
db2 "load from loadset1 of del modified by chardel| coldel& insert
into testschema.testtable nonrecoverable data buffer 240000
disk_parallelism 32"
Now I am loading about 440 rows/second, which to me is abysmally
slow. The tablespaces I am loading into have 8 containers and I
believe there are at least 30 disks on the SAN that the data
eventually lives on. So needless to say there should be all the I/O
power available to load this data.Can you post any more information, such as table DDL, tablespace
definition, maybe a few sample rows, etc?
Have you tried the load letting DB2 choose its own defaults for
data buffer / disk_parallelism settings?
On Nov 10, 6:16*pm, Ian <ianb...@mobileaudio.comwrote:rdud...@gmail.com wrote:Hi all,
I hardly ever make a post unless I am having a very purplexing issue,
so this one should be good...
I am trying to do a load against a database on an AIX server into a
DB2 v9.1 database, using SAN for storage. *The table has a few CLOBs
(smallish clobs but we are storing XML data in non-native format).
Here is the load command I am using:
db2 "load from loadset1 of del modified by chardel| coldel& insert
into testschema.testtable nonrecoverable data buffer 240000
disk_parallelism 32"
Now I am loading about 440 rows/second, which to me is abysmally
slow. *The tablespaces I am loading into have 8 containers and I
believe there are at least 30 disks on the SAN that the data
eventually lives on. *So needless to say there should be all the I/O
power available to load this data.
Can you post any more information, such as table DDL, tablespace
definition, maybe a few sample rows, etc?
Have you tried the load letting DB2 choose its own defaults for
data buffer / disk_parallelism settings?- Hide quoted text -
- Show quoted text -I have tried to allow the database to choose its own load values. I
experience simular performance.
I cant post exact DDL/sample rows for security reasons, but I will do
my best to describe:
The table itself is a parent table to other child tables ( but this
should not matter with a load). It has about 25 columns, all of which
are varchar, bigint, char, or date, except for 2, which are CLOB
(around 3000 characters/clob).
It is linked to 3 tablespaces, one for LOBs (32k pagesize), one for
indexes (4k pagesize), one for normal data (4k pagesize). All 3
tablespaces have 8 containers and live on the same filesystem which is
mounted on a VG that is tied to a SAN with 30+ disk (probably around
100 disk, but I dont know exactly as I am not the SAN adm).
Is that enough info to help?
Also a new discovery! After loading 15mil records to this table, I
did a few table scans and got the disk usage in topas to report up to
80% busy, so it seems that the load really is not using the full
capacity of the disk (as the load is using about 15% of the disk).
Also, the tablescan uses all 8 CPUs uniformially, where the load is
only using 1 or 2 CPUs.
这篇关于在具有SAN的AIX上加载性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
