Modified: trunk/Websites/test-results/ChangeLog (203147 => 203148)
--- trunk/Websites/test-results/ChangeLog 2016-07-13 01:25:25 UTC (rev 203147)
+++ trunk/Websites/test-results/ChangeLog 2016-07-13 03:30:00 UTC (rev 203148)
@@ -1,3 +1,25 @@
+2016-07-12 Lucas Forschler <[email protected]>
+
+ <rdar://problem/22524456> Mitigate performance degradation of the flakiness dashboard
+
+ Rubber-stamped by Dean Johnson.
+
+ * init-database.sql:
+ Rewrite the init-database.sql file to allow for table partitioning, based on insert date.
+ Some important things to note:
+ The main results table is unchanged, but will no longer contain any rows.
+ Partitioned tables will be generated on demand, and will inherit from 'results'
+ It is possible to query the 'results' table directly, and that will get data from all child tables.
+ This should keep us from requiring any client side code changes.
+
+ * public_partition_maintenance: Added.
+ Maintenance script which will be called on a nightly schedule to purge expired data.
+ This data will be exported and compressed to a sub-folder, then dropped from the database.
+ I'm not sure how big it will be, so we'll likely need to keep an eye on it.
+
+ * test-database.sql: Added.
+ Simple helper function to test that results partitions are created/deleted correctly.
+
2015-11-09 Ryosuke Niwa <[email protected]>
Allow , in the builder name.
Modified: trunk/Websites/test-results/init-database.sql (203147 => 203148)
--- trunk/Websites/test-results/init-database.sql 2016-07-13 01:25:25 UTC (rev 203147)
+++ trunk/Websites/test-results/init-database.sql 2016-07-13 03:30:00 UTC (rev 203148)
@@ -1,11 +1,15 @@
-DROP TABLE results CASCADE;
-DROP TABLE tests CASCADE;
-DROP TABLE build_revisions CASCADE;
-DROP TABLE builds CASCADE;
-DROP TABLE slaves CASCADE;
-DROP TABLE repositories CASCADE;
-DROP TABLE builders CASCADE;
+-- Configuration file for postgres
+-- Drop existing schema. WARNING: this will delete all data in the database
+DROP SCHEMA IF EXISTS public CASCADE;
+CREATE SCHEMA public;
+
+SET search_path TO public;
+SET constraint_exclusion = partition;
+SET work_mem='1GB';
+
+CREATE EXTENSION plsh;
+
CREATE TABLE builders (
id serial PRIMARY KEY,
master varchar(64) NOT NULL,
@@ -64,4 +68,128 @@
CREATE INDEX results_build ON results(build);
CREATE INDEX results_is_flaky ON results(is_flaky);
-SET work_mem='1024MB';
+-- Code specific to the table partitioning functions below were borrowed from:
+-- https://blog.engineyard.com/2013/scaling-postgresql-performance-table-partitioning
+CREATE OR REPLACE FUNCTION
+public.server_partition_function()
+RETURNS TRIGGER AS
+$BODY$
+DECLARE
+_new_time int;
+_tablename text;
+_startdate text;
+_enddate text;
+_result record;
+BEGIN
+_tablename := 'results_partition_'||CURRENT_DATE;
+
+-- Check if the partition needed for the current record exists
+PERFORM 1
+FROM pg_catalog.pg_class c
+JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
+WHERE c.relkind = 'r'
+AND c.relname = _tablename
+AND n.nspname = 'public';
+
+-- If the partition needed does not yet exist, then we create it:
+-- Note that || is string concatenation (joining two strings to make one)
+IF NOT FOUND THEN
+_enddate:=_startdate::timestamp + INTERVAL '1 day';
+EXECUTE 'CREATE TABLE public.' || quote_ident(_tablename) || ' (
+) INHERITS (public.results)';
+
+-- Table permissions are not inherited from the parent.
+-- If permissions change on the master be sure to change them on the child also.
+EXECUTE 'ALTER TABLE public.' || quote_ident(_tablename) || ' OWNER TO test-results-user';
+EXECUTE 'GRANT ALL ON TABLE public.' || quote_ident(_tablename) || ' TO test-results-user';
+
+-- Indexes are defined per child, so we assign a default index that uses the partition columns
+EXECUTE 'CREATE INDEX ' || quote_ident(_tablename||'_indx1') || ' ON public.' || quote_ident(_tablename) || ' (time, id)';
+END IF;
+
+-- Insert the current record into the correct partition, which we are sure will now exist.
+EXECUTE 'INSERT INTO public.' || quote_ident(_tablename) || ' VALUES ($1.*)' USING NEW;
+RETURN NULL;
+END;
+$BODY$
+LANGUAGE plpgsql;
+
+
+CREATE TRIGGER results_trigger
+BEFORE INSERT ON public.results
+FOR EACH ROW EXECUTE PROCEDURE public.server_partition_function();
+
+
+-- Maintenance function
+CREATE OR REPLACE FUNCTION
+public.partition_maintenance(in_tablename_prefix text, in_master_tablename text, in_asof date)
+RETURNS text AS
+$BODY$
+DECLARE
+_result record;
+_current_time_without_special_characters text;
+_out_filename text;
+_return_message text;
+return_message text;
+BEGIN
+-- Get the current date in YYYYMMDD_HHMMSS.ssssss format
+_current_time_without_special_characters :=
+REPLACE(REPLACE(REPLACE(NOW()::TIMESTAMP WITHOUT TIME ZONE::TEXT, '-', ''), ':', ''), ' ', '_');
+
+-- Initialize the return_message to empty to indicate no errors hit
+_return_message := '';
+
+--Validate input to function
+IF in_tablename_prefix IS NULL THEN
+RETURN 'Child table name prefix must be provided'::text;
+ELSIF in_master_tablename IS NULL THEN
+RETURN 'Master table name must be provided'::text;
+ELSIF in_asof IS NULL THEN
+RETURN 'You must provide the as-of date, NOW() is the typical value';
+END IF;
+
+FOR _result IN SELECT * FROM pg_tables WHERE schemaname='public' LOOP
+
+IF POSITION(in_tablename_prefix in _result.tablename) > 0 AND char_length(substring(_result.tablename from '[0-9-]*$')) <> 0 AND (in_asof - interval '90 days') > to_timestamp(substring(_result.tablename from '[0-9-]*$'),'YYYY-MM-DD') THEN
+
+_out_filename := '/Volumes/Data/postgres/partition_dump/' || _result.tablename || '_' || _current_time_without_special_characters || '.sql.gz';
+BEGIN
+-- Call function export_partition(child_table text) to export the file
+PERFORM public.export_partition(_result.tablename::text, _out_filename::text);
+-- If the export was successful drop the child partition
+EXECUTE 'DROP TABLE public.' || quote_ident(_result.tablename);
+_return_message := return_message || 'Dumped table: ' || _result.tablename::text || ', ';
+RAISE NOTICE 'Dumped table %', _result.tablename::text;
+EXCEPTION WHEN OTHERS THEN
+_return_message := return_message || 'ERROR dumping table: ' || _result.tablename::text || ', ';
+RAISE NOTICE 'ERROR DUMPING %', _result.tablename::text;
+END;
+END IF;
+END LOOP;
+
+RETURN _return_message || 'Done'::text;
+END;
+$BODY$
+LANGUAGE plpgsql VOLATILE COST 100;
+
+ALTER FUNCTION public.partition_maintenance(text, text, date) OWNER TO test-results-user;
+
+GRANT EXECUTE ON FUNCTION public.partition_maintenance(text, text, date) TO test-results-user;
+GRANT EXECUTE ON FUNCTION public.partition_maintenance(text, text, date) TO test-results-user;
+
+-- The function below is again generic and allows you to pass in the table name of the file you would like to export to the operating system and the name of the compressed file that will contain the exported table.
+-- Helper Function for partition maintenance
+CREATE OR REPLACE FUNCTION public.export_partition(text, text) RETURNS text AS
+$BASH$
+#!/bin/bash
+tablename=${1}
+filename=${2}
+# NOTE: pg_dump must be available in the path.
+/usr/local/bin/pg_dump -U test-results-user -t public."${tablename}" test-results-user | gzip -c > ${filename} ;
+$BASH$
+LANGUAGE plsh;
+
+ALTER FUNCTION public.export_partition(text, text) OWNER TO test-results-user;
+
+GRANT EXECUTE ON FUNCTION public.export_partition(text, text) TO test-results-user;
+GRANT EXECUTE ON FUNCTION public.export_partition(text, text) TO test-results-user;
\ No newline at end of file
Added: trunk/Websites/test-results/test-database.sql (0 => 203148)
--- trunk/Websites/test-results/test-database.sql (rev 0)
+++ trunk/Websites/test-results/test-database.sql 2016-07-13 03:30:00 UTC (rev 203148)
@@ -0,0 +1,15 @@
+SET search_path TO public;
+INSERT INTO results (id, test, build, expected, actual, modifiers, time, is_flaky) VALUES (111, 12345, 23, 'expected1', 'actual1', 'modifiers1', 100, True);
+INSERT INTO results (id, test, build, expected, actual, modifiers, time, is_flaky) VALUES (222, 12346, 24, 'expected2', 'actual2', 'modifiers2', 200, True);
+INSERT INTO results (id, test, build, expected, actual, modifiers, time, is_flaky) VALUES (333, 12347, 25, 'expected3', 'actual3', 'modifiers3', 333, True);
+INSERT INTO results (id, test, build, expected, actual, modifiers, time, is_flaky) VALUES (444, 12348, 26, 'expected4', 'actual4', 'modifiers4', 444, True);
+
+-- older than 90 days, should be deleted by maintenance function
+CREATE TABLE "results_partition_2015-01-01" () INHERITS(results);
+
+
+-- List out inherited databases
+SELECT pg_inherits.*, c.relname AS child, p.relname AS parent
+FROM
+ pg_inherits JOIN pg_class AS c ON (inhrelid=c.oid)
+ JOIN pg_class as p ON (inhparent=p.oid);
\ No newline at end of file
