ITPUB论坛-中国最专业的IT技术社区

 找回密码
 注册
查看: 2286|回复: 36

每秒900,一天400万数据,如何控制并发操作表数据?

[复制链接]
论坛徽章:
0
发表于 2016-9-5 13:19 | 显示全部楼层 |阅读模式
本帖最后由 skyspeed 于 2016-9-5 13:19 编辑

监控项目,16个传感器,采样频率50hz,要求实时显示和历史查询。业务逻辑较为简单就是按照时间查询数据,无其他操作。

为了便于实时和历史显示的效率,设计 了实时表+历史表的方式存储数据,每隔1分钟从实时表导入到历史表中,实时显示读事实表,历史查询读历史表。

实时表, 存储一分钟数据,供WEb客户端调用
  1. Create Table real_data(
  2. record_time timestamp(3),
  3. ac_1 Float,
  4. ac_2 Float,
  5. ac_3 Float,
  6. ac_4 Float,
  7. ac_5 Float,
  8. ac_6 Float,
  9. ac_7 Float,
  10. ac_8 Float,
  11. ac_9 Float,
  12. ac_10 Float,
  13. ac_11 Float,
  14. ac_12 Float,
  15. ac_13 Float,
  16. ac_14 Float,
  17. ac_15 Float,
  18. ac_16 Float
  19. )
  20. Tablespace data_test;
复制代码
历史表结构一样,只是多了时间主键 和 时间分区。
  1. Create Table history_data(
  2. record_time timestamp(3),
  3. ac_1 Float,
  4. ac_2 Float,
  5. ac_3 Float,
  6. ac_4 Float,
  7. ac_5 Float,
  8. ac_6 Float,
  9. ac_7 Float,
  10. ac_8 Float,
  11. ac_9 Float,
  12. ac_10 Float,
  13. ac_11 Float,
  14. ac_12 Float,
  15. ac_13 Float,
  16. ac_14 Float,
  17. ac_15 Float,
  18. ac_16 Float
  19. )
  20. Tablespace data_test
  21. PARTITION BY RANGE(record_time)  
  22. INTERVAL(numtodsinterval(1,'day'))  
  23. (  
  24.   PARTITION P1 VALUES LESS THAN (TO_DATE('2016-08-01', 'YYYY-MM-DD'))  
  25. );  

  26. alter table history_data add constraint RECORD_DATE primary key (RECORD_TIME);
复制代码
历史表采用间隔分区,按照一天来分区,主要是基于以下的考虑
1. 主要的查询 是基于时间的,没有其他的查询。 比如
   
  1. Select ac_1 From ac_test
  2. Where record_time >= to_timestamp('2016-08-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
  3. And  record_time <=to_timestamp('2016-08-01 00:30:00', 'yyyy-mm-dd hh24:mi:ss');
复制代码

2. 按天分区,主要看了一下执行计划,每天430万条数据,给出的执行时间大概50s, 如果换号一点的服务器,应该可以提高。


新建一个job,完成从实时表导入历史表
  1. create or replace procedure copy_to_history Is
  2. begin
  3.   Insert Into history_data Select * From real_data;  
  4.     Commit;
  5.   execute   immediate 'truncate table real_data';
  6.   Commit;
  7.    Exception   
  8.   WHEN OTHERS THEN
  9.     log_write(SQLCODE||'---'||SQLERRM);      
  10. end copy_to_history;
复制代码
现在模拟了实时数据插入,
  1. Declare  
  2. time_index Number;
  3. start_time Timestamp(3);
  4. tmp_time Timestamp(3);
  5. tmp_value1 Float;
  6. tmp_value2 Float;
  7.   tmp_value3 Float;
  8. tmp_value4 Float;
  9.   tmp_value5 Float;
  10. tmp_value6 Float;
  11.   tmp_value7 Float;
  12. tmp_value8 Float;
  13.   tmp_value9 Float;
  14. tmp_value10 Float;
  15.   tmp_value11 Float;
  16. tmp_value12 Float;
  17.   tmp_value13 Float;
  18. tmp_value14 Float;
  19.   tmp_value15 Float;
  20. tmp_value16 Float;
  21. Begin   
  22.    --initiaze the variable  
  23.     time_index:=0;
  24.   --  start_time:=to_timpstamp('2016-08-01 00:00:00.000','yyyy-mm-dd hh24:mi:ss');        
  25.     SELECT to_timestamp('2016-08-01 00:00:00:000', 'yyyy-mm-dd hh24:mi:ss:ff') Into start_time  FROM DUAL;  
  26.     dbms_output.put_line(start_time);   
  27.   
  28.           While time_index<(50*60*60*24*7)
  29.            Loop
  30.              -- add 20 millionseconds
  31.              SELECT start_time+numtodsinterval((0.02*time_index),'SECOND') Into tmp_time  FROM DUAL;
  32.             -- dbms_output.put_line(tmp_time);
  33.              -- create random number
  34.              select dbms_random.value Into tmp_value1 from dual ;
  35.              select dbms_random.value Into tmp_value2 from dual ;
  36.              select dbms_random.value Into tmp_value3 from dual ;
  37.              select dbms_random.value Into tmp_value4 from dual ;
  38.              select dbms_random.value Into tmp_value5 from dual ;
  39.              select dbms_random.value Into tmp_value6 from dual ;
  40.              select dbms_random.value Into tmp_value7 from dual ;
  41.              select dbms_random.value Into tmp_value8 from dual ;
  42.              select dbms_random.value Into tmp_value9 from dual ;
  43.              select dbms_random.value Into tmp_value10 from dual ;
  44.              select dbms_random.value Into tmp_value11 from dual ;
  45.              select dbms_random.value Into tmp_value12 from dual ;
  46.              select dbms_random.value Into tmp_value13 from dual ;
  47.              select dbms_random.value Into tmp_value14 from dual ;
  48.              select dbms_random.value Into tmp_value15 from dual ;
  49.              select dbms_random.value Into tmp_value16 from dual ;
  50.              --dbms_output.put_line(tmp_value);            
  51.             -- Insert Into ac_data (sensor_id,data,record_time) Values(sensor_index,tmp_value,tmp_time);
  52.             Insert Into real_data Values(tmp_time,tmp_value1,tmp_value2,tmp_value3,tmp_value4,tmp_value5,tmp_value6,tmp_value7,tmp_value8,tmp_value9,tmp_value10,tmp_value11,tmp_value12,tmp_value13,tmp_value14,tmp_value15,tmp_value16);
  53.             if mod(time_index,50)=0 then
  54.             commit;
  55.             dbms_lock.sleep(1);
  56.             End If;           
  57.              time_index:=time_index+1;
  58.            End Loop;         
  59.     Exception
  60.      WHEN OTHERS THEN
  61.      dbms_output.put_line('exception done!');
  62.      End;
复制代码

发现有两种不同结果:
1) 插入的速度过快的话(  不加延时等待  dbms_lock.sleep(1); 的情况下) ,出现 实时表 和历史表数据不一样的情况,历史表中的数据会存在丢失。 分析可能的原因是 copy_to_history 存储过程中,速度过快, insert into 执行较慢,此时又有新的数据插入实时表,到了truncate ,就把新的数据删掉了,没有导入到历史表。
2) 插入的速度延时 (加入延时等待dbms_lock.sleep(1); 的情况下),出现插了一段时间,历史表的数据停滞了,不增长了。
查看原因是,---ORA-00001: 违反唯一约束条件 (SYSTEM.RECORD_DATE), 即实时表中有一部分数据和历史表的数据有重叠,导致copy_to_history 存储过程中插入,违反了历史表中的主键约束。 分析可能的原因 是删除的数据时,insert into 还没完成,导致一部分数据还在实时表。

分析两种情况就是一个session 要不停的插入,一个session 要不停的拷贝和删除,造成对资源的并行操作,造成资源的使用问题,主要是整个存储过程的写法的问题,如何解决? 谢谢各位大侠

另外还有一个问题就是:  由于历史表中有主键索引,插入的效率会不会随着时间的推移而变得非常慢,尽管每次插入就是3000条数据? 比较担心插入效率问题。

谢谢各位大侠,问题有点多。

认证徽章
论坛徽章:
1
优秀写手
日期:2014-07-02 06:00:12
发表于 2016-9-5 16:19 | 显示全部楼层
建议你在从实时表导数据到历史表时,使用时间区分,如2016-09-02 01:01:00到2016-09-02 01:02:00,每次记录记录当前导入的结束时间,
或者使用序列,比如当前到100,那么只导入小于100的,下次获取序列为1000,那么只导入小于1000的。

使用道具 举报

回复
认证徽章
论坛徽章:
1
优秀写手
日期:2014-07-02 06:00:12
发表于 2016-9-5 16:20 | 显示全部楼层
还有一个问题就是实时表频繁的这么删除、插入,导致的高水位线的问题,建议定期move一下

使用道具 举报

回复
认证徽章
论坛徽章:
1
优秀写手
日期:2014-07-02 06:00:12
发表于 2016-9-5 16:27 | 显示全部楼层
--就是类似于这种
create or replace procedure copy_to_history Is
  d_top_backup_date  date;
  d_curr_backup_date date;
begin
  select top_backup_date + 1 / 24 / 60 / 60, top_backup_date + 1 / 24 / 60
    into d_top_backup_date, d_curr_backup_date
    from real_data_top_backup_date;

  Insert Into history_data
    Select *
      From real_data
     where record_time between d_top_backup_date and d_curr_backup_date;
  Commit;
  execute immediate 'delete from real_data where record_time between :1 and :2'
    using d_top_backup_date, d_curr_backup_date;
  Commit;
  execute immediate 'truncate tabele real_data_top_backup_date';
  Commit;
  insert into real_data_top_backup_date
    (top_backup_date)
  values
    (d_curr_backup_date);
  commit;
Exception
  WHEN OTHERS THEN
    log_write(SQLCODE || '---' || SQLERRM);
end copy_to_history;

使用道具 举报

回复
论坛徽章:
0
 楼主| 发表于 2016-9-8 15:01 | 显示全部楼层
陈洪光 发表于 2016-9-5 16:19
建议你在从实时表导数据到历史表时,使用时间区分,如2016-09-02 01:01:00到2016-09-02 01:02:00,每次记录 ...

历史表已经分区了,按天间隔自动分区的。 这样可以提高检索的效率问题。

另外从实时表导入历史表,我是间隔一分钟导一次,导完后把实时表清空的。这样做主要是防止实时表过快增长,影响实时显示的检索。

使用道具 举报

回复
论坛徽章:
0
 楼主| 发表于 2016-9-8 15:03 | 显示全部楼层
陈洪光 发表于 2016-9-5 16:20
还有一个问题就是实时表频繁的这么删除、插入,导致的高水位线的问题,建议定期move一下

谢谢提醒。不过存储过程中使用的 truncate,而不是delete ,应该高水位问题不是太明显吧。

使用道具 举报

回复
论坛徽章:
0
 楼主| 发表于 2016-9-8 15:25 | 显示全部楼层
陈洪光 发表于 2016-9-5 16:27
--就是类似于这种
create or replace procedure copy_to_history Is
  d_top_backup_date  date;

谢谢,我试试看。

这样还要维护插入时间的表,会不会影响插入的效率啊。。

使用道具 举报

回复
论坛徽章:
0
 楼主| 发表于 2016-9-11 15:45 | 显示全部楼层
自己顶一下,哈哈哈

使用道具 举报

回复
论坛徽章:
0
 楼主| 发表于 2016-9-12 19:53 | 显示全部楼层
我自己顶一下,我自己顶一下

使用道具 举报

回复
论坛徽章:
0
发表于 2016-9-12 19:59 | 显示全部楼层
用exchange partition 试试

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

TOP技术积分榜 社区积分榜 徽章 电子杂志 团队 统计 虎吧 老博客 知识索引树 读书频道 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 | IT博客
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统
CopyRight 1999-2011 itpub.net All Right Reserved. 北京皓辰网域网络信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
京ICP证:060528号 北京市公安局海淀分局网监中心备案编号:1101082001 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表