----------分析目前的磁盘空间---------------------------
卷组名称 /dev/vg_db
卷组写操作 读/写
卷组状态 可用的, 专有的
最大逻辑卷 255
当前逻辑卷 50
打开逻辑卷 50
最大物理卷 16
当前物理卷 2
活动物理卷 2
每个物理卷的最大物理区 65535
卷组描述区 4
物理区大小 (Mbytes) 64
物理区合计 17597
分配物理区 15623
空闲物理区 1974
物理卷组合计 0
合计备用的物理卷 0
合计使用的备用物理卷 0
从上面的信息可以看到，/dev/vg_db剩余空间为1974*64/1024=123G空间，
加上把之前已经创建的10/10g个逻辑卷重新划分就有223G的空间。
历史告警表每行为8K，数据量每天50W，计划每个分片存储7天的告警数据，
则7天的数据量为：8K*500000*7/1024=26G左右，加上预留，准备每个dbs30G空间。
1、drop掉ems_event_history
drop table ems_event_history;
2、删除tnms_his01-5表空间，然后重新划分；
onspaces -d tnms_his01
onspaces -d tnms_his02
onspaces -d tnms_his03
onspaces -d tnms_his04
onspaces -d tnms_his05
onspaces -d tnms_his06
onspaces -d tnms_his07
onspaces -d tnms_his08
onspaces -d tnms_his09
onspaces -d tnms_his09
onspaces -d remainder
3、使用vgdisplay /dev/vg_db查看空间是否增大为223G左右
4、创建逻辑卷
lvcreate -L 30720 -n tnms_his01 /dev/vg_db
lvcreate -L 30720 -n tnms_his02 /dev/vg_db
lvcreate -L 30720 -n tnms_his03 /dev/vg_db
lvcreate -L 30720 -n tnms_his04 /dev/vg_db
lvcreate -L 30720 -n tnms_his05 /dev/vg_db
lvcreate -L 30720 -n tnms_his06 /dev/vg_db
lvcreate -L 30720 -n remainder /dev/vg_db
5、设置权限和属性
cd /dev/vg_db
chown informix:informix tnms_his*
chown informix:informix remainder1
chmod 660 tnms_his*
chmod 660 remainder1
6、创建软连接
cd /opt/dbnm/chunks/
ln -s /dev/vg_db/tnms_his01 tnms_his01
ln -s /dev/vg_db/tnms_his01 tnms_his02
ln -s /dev/vg_db/tnms_his01 tnms_his03
ln -s /dev/vg_db/tnms_his01 tnms_his04
ln -s /dev/vg_db/tnms_his01 tnms_his05
ln -s /dev/vg_db/tnms_his01 tnms_his06
ln -s /dev/vg_db/tnms_his01 remainder
7、创建表空间
onspaces -c -d tnms_his01 -k 8 -p /home/informix/app/data/tnms_his01 -s 31457280 -o 0
onspaces -c -d tnms_his02 -k 8 -p /home/informix/app/data/tnms_his02 -s 31457280 -o 0
onspaces -c -d tnms_his03 -k 8 -p /home/informix/app/data/tnms_his03 -s 31457280 -o 0
onspaces -c -d tnms_his04 -k 8 -p /home/informix/app/data/tnms_his04 -s 31457280 -o 0
onspaces -c -d tnms_his05 -k 8 -p /home/informix/app/data/tnms_his05 -s 31457280 -o 0
onspaces -c -d tnms_his06 -k 8 -p /home/informix/app/data/tnms_his06 -s 31457280 -o 0
onspaces -c -d remainder -k 8 -p /home/informix/app/data/remainder -s 31457280 -o 0
8、创建表和索引
create table "informix".ems_event_history
(
event_id decimal(32,0) not null ,
severity decimal(16)
default 0.0000000000000000,
resource_name varchar(128),
resource_class varchar(16),
event_class varchar(16),
event_type varchar(16),
event_sub_type varchar(16),
title varchar(64),
first_occurrence_time datetime year to second
default current year to second,
last_occurrence_time datetime year to second,
occurrence_times decimal(16),
summary lvarchar,
event_key varchar(32),
is_derivative decimal(16)
default 0.0000000000000000,
is_acknowledged decimal(16)
default 0.0000000000000000,
ack_user varchar(64),
ack_time datetime year to second,
is_cleared decimal(16)
default 0.0000000000000000,
clear_time datetime year to second,
clear_user varchar(64),
clear_reason varchar(128),
layer_rate varchar(64),
vendor varchar(16),
original_severity decimal(16),
ems_time datetime year to second,
ne_time datetime year to second,
ems_clear_time datetime year to second,
ne_clear_time datetime year to second,
finish_time datetime year to second,
to_eoms decimal(16)
default 0.0000000000000000,
eoms_no varchar(32),
to_eoms_time datetime year to second,
eoms_send_type decimal(16),
city varchar(16),
district varchar(16),
site varchar(64),
location varchar(255),
native_object_name varchar(255),
ems_alarm_id varchar(64),
native_probable_cause varchar(64),
additional_info lvarchar,
ems_id varchar(64),
ems_name varchar(64),
me_id varchar(64),
me_name varchar(64),
card_id varchar(64),
card_name varchar(64),
ptp_id varchar(64),
ptp_name varchar(64),
ctp_id varchar(64),
circuit_id varchar(64),
circuit varchar(64),
psn_id varchar(64),
remark varchar(255),
remark_update_time datetime year to second,
remark_user varchar(64),
port_rate varchar(16),
system_level varchar(16),
management_domain varchar(16),
transmit_integrative_status varchar(16)
default '0',
transmit_integrative_time datetime year to second,
transmit_huawu_status varchar(16)
default '0',
transmit_huawu_time datetime year to second,
transmit_led_status varchar(16)
default '0',
transmit_led_time datetime year to second,
additional_event_type varchar(32)
default '普通告警',
project_name varchar(64),
device_type varchar(8),
device_model varchar(32),
system_alarm_id varchar(32),
affect_device varchar(32),
affect_business varchar(32),
correlate_flag varchar(16),
alarm_standard_name varchar(32),
original_event_type varchar(32),
service_level varchar(32),
business varchar(16),
customer varchar(32),
vendor_alarm_id varchar(16),
additional_info1 varchar(32),
additional_info2 varchar(32),
additional_info3 varchar(32),
additional_info4 varchar(255),
additional_time1 datetime year to second,
additional_number1 decimal(32,0),
primary key (event_id)
)
fragment by expression
((first_occurrence_time >= datetime(2012-05-01 00:00:00) year to second
) AND (first_occurrence_time < datetime(2012-05-07 00:00:00)
year to second ) ) in tnms_his01,
((first_occurrence_time >= datetime(2012-05-07 00:00:00) year to second
) AND (first_occurrence_time < datetime(2012-05-14 00:00:00)
year to second ) ) in tnms_his02,
((first_occurrence_time >= datetime(2012-05-14 00:00:00) year to second
) AND (first_occurrence_time < datetime(2012-05-21 00:00:00)
year to second ) ) in tnms_his03,
((first_occurrence_time >= datetime(2012-05-21 00:00:00) year to second
) AND (first_occurrence_time < datetime(2012-05-28 00:00:00)
year to second ) ) in tnms_his04,
((first_occurrence_time >= datetime(2012-05-28 00:00:00) year to second
) AND (first_occurrence_time < datetime(2012-06-04 00:00:00)
year to second ) ) in tnms_his05,
((first_occurrence_time >= datetime(2012-06-04 00:00:00) year to second
) AND (first_occurrence_time < datetime(2012-06-11 00:00:00)
year to second ) ) in tnms_his06,
remainder in remainder
extent size 20000000 next size 5000000 lock mode row;
8、做统计更新
统计表信息
update statistics for table ems_event_history;
9、验证分片是否起作用
set explain on;
select * from ems_event_history e where (first_occurrence_time >= '2012-02-20 12:00:00' and first_occurrence_time < '2012-03-25 12:00:00');
-bash-3.2$ cat sqexplain.out
QUERY: (OPTIMIZATION TIMESTAMP: 05-25-2012 14:21:56)
------
select * from ems_event_history e where (first_occurrence_time >= '2012-02-20 12:00:00' and first_occurrence_time < '2012-03-25 12:00:00')
Estimated Cost: 34
Estimated # of Rows Returned: 33
1) informix.e: SEQUENTIAL SCAN (Serial, fragments: 6)
Fragments Scanned: (6) remainder
Filters: (informix.e.first_occurrence_time < datetime(2012-03-25 12:00:00) year to second AND informix.e.first_occurrence_time >= datetime(2012-02-20 12:00:00) year to second )
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 e
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 33 33 33 00:00.00 35
