历史告警表分片步骤：
一、创建分片所用的库
----------分析目前的磁盘空间---------------------------
卷组名称 /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
--删除裸设备
lvremove /dev/vg_db/tnms_his01
lvremove /dev/vg_db/tnms_his02
lvremove /dev/vg_db/tnms_his03
lvremove /dev/vg_db/tnms_his04
lvremove /dev/vg_db/tnms_his05
lvremove /dev/vg_db/tnms_his06
lvremove /dev/vg_db/tnms_his07
lvremove /dev/vg_db/tnms_his08
lvremove /dev/vg_db/tnms_his09
lvremove /dev/vg_db/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/rtnms_his01 tnms_his01
ln -s /dev/vg_db/rtnms_his01 tnms_his02
ln -s /dev/vg_db/rtnms_his01 tnms_his03
ln -s /dev/vg_db/rtnms_his01 tnms_his04
ln -s /dev/vg_db/rtnms_his01 tnms_his05
ln -s /dev/vg_db/rtnms_his01 tnms_his06
ln -s /dev/vg_db/rremainder remainder
7、创建表空间
onspaces -c -d tnms_his01 -k 8 -p /opt/dbnm/chunks/tnms_his01 -s 31457280 -o 0
onspaces -c -d tnms_his02 -k 8 -p /opt/dbnm/chunks/tnms_his02 -s 31457280 -o 0
onspaces -c -d tnms_his03 -k 8 -p /opt/dbnm/chunks/tnms_his03 -s 31457280 -o 0
onspaces -c -d tnms_his04 -k 8 -p /opt/dbnm/chunks/tnms_his04 -s 31457280 -o 0
onspaces -c -d tnms_his05 -k 8 -p /opt/dbnm/chunks/tnms_his05 -s 31457280 -o 0
onspaces -c -d tnms_his06 -k 8 -p /opt/dbnm/chunks/tnms_his06 -s 31457280 -o 0
onspaces -c -d remainder -k 8 -p /opt/dbnm/chunks/remainder -s 31457280 -o 0
二、开始创建分片
1、删除历史告警表索引
drop index index_name
1.1、如果有primary key主键，先删除主键（主键可以用unique索引代替）；
select constrname from sysconstraints where constrtype='P' and tabid=(select tabid from systables where tabname='ems_event_history');
alter table yuyue drop constraint u153_31;
----创建主键（这一步不要做）
alter table ems_event_history add constraint primary key (event_id);
2、rename历史告警表
rename table ems_event_history to ems_event_history_old;
3、创建历史告警表分片（暂时不要创建索引）
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)
)
fragment by expression
((first_occurrence_time >= datetime(2012-05-20 00:00:00)
year to second ) AND (first_occurrence_time < datetime(2012-05-20
03:00:00) year to second ) ) in tnms_his01,
((first_occurrence_time >= datetime(2012-05-20 03:00:00)
year to second ) AND (first_occurrence_time < datetime(2012-05-20
06:00:00) year to second ) ) in tnms_his02,
((first_occurrence_time >= datetime(2012-05-20 06:00:00)
year to second ) AND (first_occurrence_time < datetime(2012-05-20
09:00:00) year to second ) ) in tnms_his03,
((first_occurrence_time >= datetime(2012-05-20 09:00:00)
year to second ) AND (first_occurrence_time < datetime(2012-05-20
12:00:00) year to second ) ) in tnms_his04,
((first_occurrence_time >= datetime(2012-05-20 12:00:00)
year to second ) AND (first_occurrence_time < datetime(2012-05-20
16:00:00) year to second ) ) in tnms_his05,
((first_occurrence_time >= datetime(2012-05-20 16:00:00)
year to second ) AND (first_occurrence_time < datetime(2012-05-20
20:00:00) year to second ) ) in tnms_his06,
remainder in remainder;
4、将ems_event_history_old表作为新历史告警表的一个分片ATTACH
(注:时间最好是能够包括没有分片之前的所有告警内容,如果时间和创建分片表里面的时间重复，数据就会发生迁移，ATTACH将非常慢。)
ALTER FRAGMENT ON TABLE ems_event_history ATTACH ems_event_history_old AS ((first_occurrence_time >= datetime(2012-05-22 20:00:00)year to second) AND (first_occurrence_time < datetime(2012-05-24 00:00:00) year to second));
5、检查分片是否创建
-bash-3.2$ dbschema -d tnmsdb2 -t ems_event_history -ss
fragment by expression
((first_occurrence_time >= datetime(2012-05-20 00:00:00)
year to second ) AND (first_occurrence_time < datetime(2012-05-20
03:00:00) year to second ) ) in tnms_his01,
((first_occurrence_time >= datetime(2012-05-20 03:00:00)
year to second ) AND (first_occurrence_time < datetime(2012-05-20
06:00:00) year to second ) ) in tnms_his02,
((first_occurrence_time >= datetime(2012-05-20 06:00:00)
year to second ) AND (first_occurrence_time < datetime(2012-05-20
09:00:00) year to second ) ) in tnms_his03,
((first_occurrence_time >= datetime(2012-05-20 09:00:00)
year to second ) AND (first_occurrence_time < datetime(2012-05-20
12:00:00) year to second ) ) in tnms_his04,
((first_occurrence_time >= datetime(2012-05-20 12:00:00)
year to second ) AND (first_occurrence_time < datetime(2012-05-20
16:00:00) year to second ) ) in tnms_his05,
((first_occurrence_time >= datetime(2012-05-20 16:00:00)
year to second ) AND (first_occurrence_time < datetime(2012-05-20
20:00:00) year to second ) ) in tnms_his06,
((first_occurrence_time >= datetime(2012-05-22 20:00:00)
year to second ) AND (first_occurrence_time < datetime(2012-05-24
00:00:00) year to second ) ) in tnms_db01,
remainder in remainder
extent size 16 next size 16 lock mode page;
5、创建索引
打开PDQ
set pdqpriority 50;
开始创建索引
create unique index "informix".idx_ems_event_history001 on "informix"
.ems_event_history (event_id) using btree in tnms_idx01;
create index "informix".idx_ems_event_history111 on "informix"
.ems_event_history (title,location) using btree in tnms_idx01;
create index "informix".idx_ems_event_history1110 on "informix"
.ems_event_history (ne_time desc) using btree in tnms_idx01;
create index "informix".idx_ems_event_history1111 on "informix"
.ems_event_history (ems_time desc) using btree in tnms_idx01;
create index "informix".idx_ems_event_history1112 on "informix"
.ems_event_history (eoms_no) using btree in tnms_idx01;
create index "informix".idx_ems_event_history1113 on "informix"
.ems_event_history (management_domain) using btree in tnms_idx01;
create index "informix".idx_ems_event_history1114 on "informix"
.ems_event_history (vendor) using btree in tnms_idx01;
create index "informix".idx_ems_event_history1115 on "informix"
.ems_event_history (clear_reason) using btree in tnms_idx01;
create index "informix".idx_ems_event_history1116 on "informix"
.ems_event_history (resource_name) using btree in tnms_idx01;
create index "informix".idx_ems_event_history1117 on "informix"
.ems_event_history (additional_event_type,last_occurrence_time)
using btree in tnms_idx01;
create index "informix".idx_ems_event_history1118 on "informix"
.ems_event_history (finish_time) using btree in tnms_idx01;
create index "informix".idx_ems_event_history1119 on "informix"
.ems_event_history (ne_time,port_rate,severity,additional_event_type,
management_domain) using btree in tnms_idx01;
create index "informix".idx_ems_event_history112 on "informix"
.ems_event_history (is_cleared,is_acknowledged) using btree
in tnms_idx01;
create index "informix".idx_ems_event_history1120 on "informix"
.ems_event_history (project_name) using btree in tnms_idx01;
create index "informix".idx_ems_event_history113 on "informix"
.ems_event_history (last_occurrence_time desc) using btree
in tnms_idx01;
create index "informix".idx_ems_event_history114 on "informix"
.ems_event_history (first_occurrence_time desc) using btree
in tnms_idx01;
create index "informix".idx_ems_event_history115 on "informix"
.ems_event_history (severity) using btree in tnms_idx01;
create index "informix".idx_ems_event_history116 on "informix"
.ems_event_history (ems_id) using btree in tnms_idx01;
create index "informix".idx_ems_event_history117 on "informix"
.ems_event_history (me_id) using btree in tnms_idx01;
create index "informix".idx_ems_event_history118 on "informix"
.ems_event_history (card_id) using btree in tnms_idx01;
create index "informix".idx_ems_event_history119 on "informix"
.ems_event_history (ptp_id) using btree in tnms_idx01;
create index "informix".idx_ems_event_history120 on "informix"
.ems_event_history (event_type,first_occurrence_time,management_domain)
using btree in tnms_idx01;
create index "informix".idx_ems_event_history1200 on "informix"
.ems_event_history (title,first_occurrence_time,management_domain)
using btree in tnms_idx01;
create index "informix".idx_ems_event_history1201 on "informix"
.ems_event_history (severity,first_occurrence_time,management_domain)
using btree in tnms_idx01;
create index "informix".idx_ems_event_history1202 on "informix"
.ems_event_history (is_acknowledged,is_cleared,first_occurrence_time,
management_domain) using btree in tnms_idx01;
create index "informix".idx_ems_event_history1203 on "informix"
.ems_event_history (management_domain,first_occurrence_time,
severity) using btree in tnms_idx01;
6、验证分片是否起作用
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
针对山东历史告警分片维护所遇问题总结
在做山东informix分片维护时，detach一个分片时非常慢，发现表结构中出现了主键（primary key），删除主键后再对表detach时耗时在1秒中；
之后将detach下来的分片策略进行修改从新attach回去，发现非常非常慢，经过多次测试，发现是由于以下2种情况导致：
1、由于新增加的分片时间和remainder分片中的数据时间有重复的，导致数据进行迁移。。。这个过程将是非常漫长的，做分片维护时，尽量避免发生数据迁移;
2、remainder分片空间满了也会导致attach非常慢，这个做过测试的;
解决方法：
将remainder分片先detach掉，然后再对其他分片进行操作，速度非常快，但新的问题又出来了，由于之前分片都满了，
新到的数据都储存在了remainder分片中，而且数据量非常大如果进行导出导入的话就相当费时;
解决方法如下：
remainder分片detach下来，数据被存储到临时表remainder_1w中，我们主要是想要remainder分片中的数据，
创建一个与ems_event_history表结构完全相同的表ems_event_history_new（不用创建索引），然后将临时表remainder_1w中的数据insert到
ems_event_history_new表中，然后再将ems_event_history_new表挂载到ems_event_history下面的一个分片上，最后从新创建remainder分片分片；
具体步骤：
1、detach remainder分片
alter fragment on table ems_event_history detach partition remainder remainder_1w;
2、创建yuyue表，要和ems_event_history表结构相同
create table ems_event_history_new
(
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),
)
3、将临时remainder_1w中的数据insert到ems_event_history_new表中(临时表是无法作为分片进行挂载)
insert into yuyue select * from remainder_1w;
4、给ems_event_history表创建remainder分片（先创建remainder是为防止ems_event_history_new表中的数据不符合策略时间）
alter fragment on table ems_event_history add remainder in remainder;
5、如果有primary key主键，先删除主键（主键可以用unique索引代替）;
select constrname from sysconstraints where constrtype='P' and tabid=(select tabid from systables where tabname='yuyue');
alter table yuyue drop constraint u153_31;
6、将ems_event_history_new表挂载到分片表ems_event_history中
ALTER FRAGMENT ON TABLE ems_event_history ATTACH ems_event_history_new AS ((first_occurrence_time >= datetime(2012-07-28 20:00:00)year to second) AND (first_occurrence_time < datetime(2012-07-29 00:00:00) year to second));
7、创建索引
