Navigation

← Previous Changeset
Next Changeset →

Changeset d2dd72fa3f0404e6651f95cdc95214380a5630a2

Timestamp:

10/27/09 15:21:51 (4 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:

sql/crontab (modified) (1 diff)
sql/sprocs/stratcon.archive_part_maint.sql (modified) (4 diffs)
src/stratcon.conf.in (modified) (4 diffs)
src/stratcon_datastore.c (modified) (4 diffs)

Legend:

: Unmodified
: Added
: Removed
: Modified
: Copied
: Moved

sql/crontab

r32caf51	rd2dd72f
2	2	* * * * * /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
3	3	#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
	4	01 00 * * * /opt/pgsql835/bin/psql -d reconnoiter -U reconnoiter -c "select stratcon.archive_part_maint('noit.metric_numeric_archive', 'day', 7);" 1>/dev/null
	5	01 00 * * * /opt/pgsql835/bin/psql -d reconnoiter -U reconnoiter -c "select stratcon.archive_part_maint('noit.metric_text_archive', 'day', 7);" 1>/dev/null
	6	01 00 * * * /opt/pgsql835/bin/psql -d reconnoiter -U reconnoiter -c "select stratcon.archive_part_maint('noit.check_status_archive', 'day' 7);" 1>/dev/null
	7	01 00 * * * /opt/pgsql835/bin/psql -d reconnoiter -U reconnoiter -c "select stratcon.archive_part_maint('noit.check_archive', 'day' 7);" 1>/dev/null
7	8	01 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
	1	CREATE OR REPLACE FUNCTION stratcon.archive_part_maint(in_parent_table text, units text, cnt integer) RETURNS void
2	2	AS $$
3	3	DECLARE
…	…
12	12	v_has_perms boolean;
13	13	BEGIN
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;
23	22
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;
25	45
26	46	LOOP
27		IF v_next_part > current_date + ~~'1 month'::interval~~ THEN
	47	IF v_next_part > current_date + (cnt * ('1 '\|\|units)::interval) THEN
28	48	EXIT;
29	49	END IF;
30	50	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');
32	53	v_constraint_name := 'check_' \|\| v_table_name;
33	54
…	…
35	56	'CONSTRAINT ' \|\| v_constraint_name \|\|
36	57	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)' \|\|
38	59	') INHERITS (' \|\| in_parent_table \|\| ')';
39	60
…	…
105	126	END LOOP;
106	127
107		v_next_part := (v_next_part + ~~'1 month'~~::interval)::date;
	128	v_next_part := (v_next_part + ('1 '\|\|units)::interval)::date;
108	129	END LOOP;
109	130	END

src/stratcon.conf.in

r47a77d0	rd2dd72f
110	110	]]></mapchecktostoragenode>
111	111	<check><![CDATA[
112		INSERT INTO check_archive
	112	INSERT INTO check_archive_%Y%m%d
113	113	(remote_address, whence, sid, id, target, module, name)
114	114	VALUES ($1, 'epoch'::timestamptz + ($2 \|\| ' seconds')::interval,
…	…
116	116	]]></check>
117	117	<status><![CDATA[
118		INSERT INTO check_status_archive
	118	INSERT INTO check_status_archive_%Y%m%d
119	119	(whence, sid, state, availability, duration, status)
120	120	VALUES ('epoch'::timestamptz + ($1 \|\| ' seconds')::interval,
…	…
122	122	]]></status>
123	123	<metric_numeric><![CDATA[
124		INSERT INTO metric_numeric_archive
	124	INSERT INTO metric_numeric_archive_%Y%m%d
125	125	(whence, sid, name, value)
126	126	VALUES ('epoch'::timestamptz + ($1 \|\| ' seconds')::interval,
…	…
128	128	]]></metric_numeric>
129	129	<metric_text><![CDATA[
130		INSERT INTO metric_text_archive
	130	INSERT INTO metric_text_archive_%Y%m%d
131	131	( whence, sid, name,value)
132	132	VALUES ('epoch'::timestamptz + ($1 \|\| ' seconds')::interval,

src/stratcon_datastore.c

r4790fc8	rd2dd72f
1048	1048
1049	1049	if(current->problematic) {
1050		~~noitL(ingest_err, "%d\t%s\n", ij->storagenode_id, current->data);~~
1051	1050	RELEASE_SAVEPOINT("batch");
1052	1051	current = current->next;
…	…
1061	1060	case DS_EXEC_ROW_FAILED:
1062	1061	/* rollback to savepoint, mark this record as bad and start again */
	1062	noitL(ingest_err, "%d\t%s\n", ij->storagenode_id, current->data);
1063	1063	current->problematic = 1;
1064	1064	current = last_sp;
…	…
1066	1066	break;
1067	1067	case DS_EXEC_TXN_FAILED:
	1068	noitL(noit_error, "txn failed '%s', retrying\n", ij->filename);
1068	1069	BUSTED(cq);
1069	1070	}
…	…
1071	1072	}
1072	1073	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	}
1074	1078	/* Cleanup the mess */
1075	1079	while(head) {