• Vertica删除历史分区数据


    假设test用户下创建的t_jingyu表
    vsql -Utest -wtestpwd
    create table t_jingyu(
    col1 int,
    col2 varchar,
    col3 timestamp not null)
    PARTITION BY (date_part('doy', t_jingyu.col3));
     
    需求:删除doy分区表t_jingyu的历史分区数据(8月份)
    $vi /tmp/jingyu/del_part08_01-31.sql
    	iming
    SELECT DROP_PARTITION('test.t_jingyu', EXTRACT('doy' FROM '2014-08-01'::date)); 
    SELECT DROP_PARTITION('test.t_jingyu', EXTRACT('doy' FROM '2014-08-02'::date)); 
    SELECT DROP_PARTITION('test.t_jingyu', EXTRACT('doy' FROM '2014-08-03'::date)); 
    SELECT DROP_PARTITION('test.t_jingyu', EXTRACT('doy' FROM '2014-08-04'::date)); 
    SELECT DROP_PARTITION('test.t_jingyu', EXTRACT('doy' FROM '2014-08-05'::date)); 
    SELECT DROP_PARTITION('test.t_jingyu', EXTRACT('doy' FROM '2014-08-06'::date)); 
    SELECT DROP_PARTITION('test.t_jingyu', EXTRACT('doy' FROM '2014-08-07'::date)); 
    SELECT DROP_PARTITION('test.t_jingyu', EXTRACT('doy' FROM '2014-08-08'::date)); 
    SELECT DROP_PARTITION('test.t_jingyu', EXTRACT('doy' FROM '2014-08-09'::date)); 
    SELECT DROP_PARTITION('test.t_jingyu', EXTRACT('doy' FROM '2014-08-10'::date)); 
    SELECT DROP_PARTITION('test.t_jingyu', EXTRACT('doy' FROM '2014-08-11'::date)); 
    SELECT DROP_PARTITION('test.t_jingyu', EXTRACT('doy' FROM '2014-08-12'::date)); 
    SELECT DROP_PARTITION('test.t_jingyu', EXTRACT('doy' FROM '2014-08-13'::date)); 
    SELECT DROP_PARTITION('test.t_jingyu', EXTRACT('doy' FROM '2014-08-14'::date)); 
    SELECT DROP_PARTITION('test.t_jingyu', EXTRACT('doy' FROM '2014-08-15'::date)); 
    SELECT DROP_PARTITION('test.t_jingyu', EXTRACT('doy' FROM '2014-08-16'::date)); 
    SELECT DROP_PARTITION('test.t_jingyu', EXTRACT('doy' FROM '2014-08-17'::date)); 
    SELECT DROP_PARTITION('test.t_jingyu', EXTRACT('doy' FROM '2014-08-18'::date)); 
    SELECT DROP_PARTITION('test.t_jingyu', EXTRACT('doy' FROM '2014-08-19'::date)); 
    SELECT DROP_PARTITION('test.t_jingyu', EXTRACT('doy' FROM '2014-08-20'::date)); 
    SELECT DROP_PARTITION('test.t_jingyu', EXTRACT('doy' FROM '2014-08-21'::date)); 
    SELECT DROP_PARTITION('test.t_jingyu', EXTRACT('doy' FROM '2014-08-22'::date)); 
    SELECT DROP_PARTITION('test.t_jingyu', EXTRACT('doy' FROM '2014-08-23'::date)); 
    SELECT DROP_PARTITION('test.t_jingyu', EXTRACT('doy' FROM '2014-08-24'::date)); 
    SELECT DROP_PARTITION('test.t_jingyu', EXTRACT('doy' FROM '2014-08-25'::date)); 
    SELECT DROP_PARTITION('test.t_jingyu', EXTRACT('doy' FROM '2014-08-26'::date)); 
    SELECT DROP_PARTITION('test.t_jingyu', EXTRACT('doy' FROM '2014-08-27'::date)); 
    SELECT DROP_PARTITION('test.t_jingyu', EXTRACT('doy' FROM '2014-08-28'::date)); 
    SELECT DROP_PARTITION('test.t_jingyu', EXTRACT('doy' FROM '2014-08-29'::date)); 
    SELECT DROP_PARTITION('test.t_jingyu', EXTRACT('doy' FROM '2014-08-30'::date)); 
    SELECT DROP_PARTITION('test.t_jingyu', EXTRACT('doy' FROM '2014-08-31'::date)); 
    $vsql -Utest -wtestpwd -f /tmp/jingyu/del_part08_01-31.sql
    Timing is on.
      DROP_PARTITION   
    -------------------
     Partition dropped
    (1 row)
    
    Time: First fetch (1 row): 12732.105 ms. All rows formatted: 12732.215 ms
      DROP_PARTITION   
    -------------------
     Partition dropped
    (1 row)
    
    Time: First fetch (1 row): 71796.168 ms. All rows formatted: 71796.237 ms
    ……
    Time: First fetch (1 row): 16020.296 ms. All rows formatted: 16020.362 ms
    [dbadmin@Vertica01 jingyu]$ 

    数据库表有入库时,drop历史分区会有锁等待,如下:

    dbadmin=> SELECT DROP_PARTITION('test.t_jingyu', EXTRACT('doy' FROM '2014-08-04'::date)); 
     ERROR 5157:  Unavailable: [Txn 0xa000000005fb08] O lock table - timeout error Timed out O locking Table:test.t_jingyu. I held by [user dbadmin (copy test.t_jingyu(col1,col2,col3)
    from '/home/dbadmin/bin/vertica97/data/t_jingyudat1411006284757/*' on any node 
    delimiter E'~' NULL '' NO ESCAPE DIRECT;)], I held by [user dbadmin (copy test.t_jingyu(col1,col2,col3)
    from '/home/dbadmin/bin/vertica101/data/t_jingyudat1411006351418/*' on any node 
    delimiter E'~' NULL '' NO ESCAPE DIRECT;)]. Your current transaction isolation level is SERIALIZABLE
    dbadmin=>   
    同时关注数据库的队列,发现同时有drop_partition和copy入库。
    dbadmin=> select substr(current_statement, 1, 200), count(1) 
    dbadmin-> from sessions 
    dbadmin-> where not current_statement is null and (transaction_id, statement_id)<>(current_trans_id(), current_statement()) 
    dbadmin-> group by 1; 
                                                                                                      substr                                                                                                  | count 
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------
                                                                                                                                                                                                              |    10
     SELECT DROP_PARTITION('test.t_jingyu', EXTRACT('doy' FROM '2014-08-04'::date));                                                                                                                |     1
     copy test.t_jingyu(col1,col2,col3) f |     1
    (3 rows)

    在没有copy入库时,drop_partition 大概20s内就可以删除一个历史分区(50亿左右数据量)。

  • 相关阅读:
    SQlite数据库
    关于如何获取剪切板的多个图片处理
    aes 和 Md5 分析
    SIP消息
    getItemAt
    C++ map的方法
    C++ 解析Json
    CentOS 6.3安装配置LAMP服务器(Apache+PHP5+MySQL)
    阿里云服务器CentOS 5.7(64位)安装配置LAMP服务器(Apache+PHP5+MySQL)
    Apache虚拟主机(vhost)配置教程
  • 原文地址:https://www.cnblogs.com/jyzhao/p/3978582.html
Copyright © 2020-2023  润新知