Changeset d2dd72fa3f0404e6651f95cdc95214380a5630a2

Show
Ignore:
Timestamp:
10/27/09 15:21:51 (5 years ago)
Author:
Theo Schlossnagle <jesus@omniti.com>
git-committer:
Theo Schlossnagle <jesus@omniti.com> 1256656911 +0000
git-parent:

[2cc6b4021c0e492bd4b350e60bada4519596d7c2]

git-author:
Theo Schlossnagle <jesus@omniti.com> 1256656911 +0000
Message:

partitioning by day now -- so says the Treat, refs #150

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved
  • sql/crontab

    r32caf51 rd2dd72f  
    22* * * * * /opt/pgsql835/bin/psql -d reconnoiter -U stratcon -c "select stratcon.rollup_metric_numeric(rollup) from metric_numeric_rollup_config order by seconds asc;" >/tmp/rollup.log 2>&1 
    33#cleanup jobs 
    4 #01 00 * * * /opt/pgsql835/bin/psql -d reconnoiter -U reconnoiter -c "select stratcon.archive_part_maint('noit.metric_numeric_archive');" 1>/dev/null 
    5 #01 00 * * * /opt/pgsql835/bin/psql -d reconnoiter -U reconnoiter -c "select stratcon.archive_part_maint('noit.metric_text_archive');" 1>/dev/null 
    6 #01 00 * * * /opt/pgsql835/bin/psql -d reconnoiter -U reconnoiter -c "select stratcon.archive_part_maint('noit.check_status_archive');" 1>/dev/null 
     401 00 * * * /opt/pgsql835/bin/psql -d reconnoiter -U reconnoiter -c "select stratcon.archive_part_maint('noit.metric_numeric_archive', 'day', 7);" 1>/dev/null 
     501 00 * * * /opt/pgsql835/bin/psql -d reconnoiter -U reconnoiter -c "select stratcon.archive_part_maint('noit.metric_text_archive', 'day', 7);" 1>/dev/null 
     601 00 * * * /opt/pgsql835/bin/psql -d reconnoiter -U reconnoiter -c "select stratcon.archive_part_maint('noit.check_status_archive', 'day' 7);" 1>/dev/null 
     701 00 * * * /opt/pgsql835/bin/psql -d reconnoiter -U reconnoiter -c "select stratcon.archive_part_maint('noit.check_archive', 'day' 7);" 1>/dev/null 
    7801 00 * * * /opt/pgsql835/bin/psql -d reconnoiter -c "delete from prism.saved_graphs where saved = false and last_update<current_timestamp - '1 day' ::interval;" 1>/dev/null 
  • sql/sprocs/stratcon.archive_part_maint.sql

    r014002a rd2dd72f  
    1 CREATE OR REPLACE FUNCTION stratcon.archive_part_maint(in_parent_table text, in_start date) RETURNS void  
     1CREATE OR REPLACE FUNCTION stratcon.archive_part_maint(in_parent_table text, units text, cnt integer) RETURNS void  
    22AS $$ 
    33DECLARE 
     
    1212    v_has_perms boolean; 
    1313BEGIN 
    14   select (in_start - '1 month'::interval)::date into v_recent_part; 
    15   v_parent_table := substring(in_parent_table from E'\\.(.+)'); 
    16   IF v_parent_table IS NULL THEN 
    17     v_parent_table := in_parent_table; 
    18   END IF; 
    19   v_schema_name := substring(in_parent_table from E'^([^.]+)'); 
    20   IF v_schema_name IS NULL THEN 
    21     v_schema_name := 'stratcon'; 
    22   END IF; 
     14    v_parent_table := substring(in_parent_table from E'\\.(.+)'); 
     15    IF v_parent_table IS NULL THEN 
     16      v_parent_table := in_parent_table; 
     17    END IF; 
     18    v_schema_name := substring(in_parent_table from E'^([^.]+)'); 
     19    IF v_schema_name IS NULL THEN 
     20      v_schema_name := 'stratcon'; 
     21    END IF; 
    2322 
    24     select date_trunc('month', v_recent_part + '1 month'::interval)::date into v_next_part; 
     23    select d into v_recent_part from ( 
     24        select overlay( 
     25                   overlay( 
     26                       substring(relname from E'_([0-9]{8})$') 
     27                       placing '-' from 7 for 0) 
     28                   placing '-' from 5 for 0)::date as d from pg_class 
     29         where oid in (select inhrelid 
     30                         from pg_inherits as i 
     31                         join pg_class as c on(i.inhparent = c.oid) 
     32                         join pg_namespace as n on (c.relnamespace = n.oid) 
     33                        where nspname = v_schema_name 
     34                          and relname = v_parent_table)) as x 
     35    order by d desc 
     36    limit 1; 
     37 
     38    IF v_recent_part IS NULL THEN 
     39        select (date_trunc(units, current_date) - ('1 '||units)::interval)::date 
     40          into v_recent_part; 
     41    END IF; 
     42 
     43    select date_trunc(units, v_recent_part + ('1 '||units)::interval)::date 
     44      into v_next_part; 
    2545 
    2646    LOOP 
    27         IF v_next_part > current_date + '1 month'::interval THEN 
     47        IF v_next_part > current_date + (cnt * ('1 '||units)::interval) THEN 
    2848            EXIT; 
    2949        END IF; 
    3050        v_table_name := v_parent_table || '_' || extract(YEAR from v_next_part) ||  
    31                         lpad(extract(month from v_next_part)::text, 2, '0'); 
     51                        lpad(extract(month from v_next_part)::text, 2, '0') || 
     52                        lpad(extract(day from v_next_part)::text, 2, '0'); 
    3253        v_constraint_name := 'check_' || v_table_name; 
    3354 
     
    3556                'CONSTRAINT ' || v_constraint_name || 
    3657                E' CHECK (whence >= \'' || v_next_part::text || E' 00:00:00-00\'::timestamptz AND ' || 
    37                 E'        whence < \'' || (v_next_part + '1 month'::interval)::date::text || E' 00:00:00-00\'::timestamptz)' || 
     58                E'        whence < \'' || (v_next_part + ('1 '||units)::interval)::date::text || E' 00:00:00-00\'::timestamptz)' || 
    3859                ') INHERITS (' || in_parent_table || ')'; 
    3960 
     
    105126        END LOOP; 
    106127 
    107         v_next_part := (v_next_part + '1 month'::interval)::date; 
     128        v_next_part := (v_next_part + ('1 '||units)::interval)::date; 
    108129    END LOOP; 
    109130END 
  • src/stratcon.conf.in

    r47a77d0 rd2dd72f  
    110110      ]]></mapchecktostoragenode> 
    111111      <check><![CDATA[ 
    112         INSERT INTO check_archive 
     112        INSERT INTO check_archive_%Y%m%d 
    113113                    (remote_address, whence, sid, id, target, module, name) 
    114114             VALUES ($1, 'epoch'::timestamptz + ($2 || ' seconds')::interval, 
     
    116116      ]]></check> 
    117117      <status><![CDATA[ 
    118         INSERT INTO check_status_archive 
     118        INSERT INTO check_status_archive_%Y%m%d 
    119119                    (whence, sid, state, availability, duration, status) 
    120120             VALUES ('epoch'::timestamptz + ($1 || ' seconds')::interval, 
     
    122122      ]]></status> 
    123123      <metric_numeric><![CDATA[ 
    124         INSERT INTO metric_numeric_archive 
     124        INSERT INTO metric_numeric_archive_%Y%m%d 
    125125                    (whence, sid, name, value) 
    126126             VALUES ('epoch'::timestamptz + ($1 || ' seconds')::interval, 
     
    128128      ]]></metric_numeric> 
    129129      <metric_text><![CDATA[ 
    130         INSERT INTO metric_text_archive 
     130        INSERT INTO metric_text_archive_%Y%m%d 
    131131                    ( whence, sid, name,value) 
    132132             VALUES ('epoch'::timestamptz + ($1 || ' seconds')::interval, 
  • src/stratcon_datastore.c

    r4790fc8 rd2dd72f  
    10481048  
    10491049      if(current->problematic) { 
    1050         noitL(ingest_err, "%d\t%s\n", ij->storagenode_id, current->data); 
    10511050        RELEASE_SAVEPOINT("batch"); 
    10521051        current = current->next; 
     
    10611060        case DS_EXEC_ROW_FAILED: 
    10621061          /* rollback to savepoint, mark this record as bad and start again */ 
     1062          noitL(ingest_err, "%d\t%s\n", ij->storagenode_id, current->data); 
    10631063          current->problematic = 1; 
    10641064          current = last_sp; 
     
    10661066          break; 
    10671067        case DS_EXEC_TXN_FAILED: 
     1068          noitL(noit_error, "txn failed '%s', retrying\n", ij->filename); 
    10681069          BUSTED(cq); 
    10691070      } 
     
    10711072  } 
    10721073  if(last_sp) RELEASE_SAVEPOINT("batch"); 
    1073   if(stratcon_datastore_do(cq, "COMMIT")) BUSTED(cq); 
     1074  if(stratcon_datastore_do(cq, "COMMIT")) { 
     1075    noitL(noit_error, "txn commit failed '%s', retrying\n", ij->filename); 
     1076    BUSTED(cq); 
     1077  } 
    10741078  /* Cleanup the mess */ 
    10751079  while(head) {