<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head><meta http-equiv="content-type" content="text/html; charset=utf-8" />
<title>[203148] trunk/Websites/test-results</title>
</head>
<body>
<style type="text/css"><!--
#msg dl.meta { border: 1px #006 solid; background: #369; padding: 6px; color: #fff; }
#msg dl.meta dt { float: left; width: 6em; font-weight: bold; }
#msg dt:after { content:':';}
#msg dl, #msg dt, #msg ul, #msg li, #header, #footer, #logmsg { font-family: verdana,arial,helvetica,sans-serif; font-size: 10pt; }
#msg dl a { font-weight: bold}
#msg dl a:link { color:#fc3; }
#msg dl a:active { color:#ff0; }
#msg dl a:visited { color:#cc6; }
h3 { font-family: verdana,arial,helvetica,sans-serif; font-size: 10pt; font-weight: bold; }
#msg pre { overflow: auto; background: #ffc; border: 1px #fa0 solid; padding: 6px; }
#logmsg { background: #ffc; border: 1px #fa0 solid; padding: 1em 1em 0 1em; }
#logmsg p, #logmsg pre, #logmsg blockquote { margin: 0 0 1em 0; }
#logmsg p, #logmsg li, #logmsg dt, #logmsg dd { line-height: 14pt; }
#logmsg h1, #logmsg h2, #logmsg h3, #logmsg h4, #logmsg h5, #logmsg h6 { margin: .5em 0; }
#logmsg h1:first-child, #logmsg h2:first-child, #logmsg h3:first-child, #logmsg h4:first-child, #logmsg h5:first-child, #logmsg h6:first-child { margin-top: 0; }
#logmsg ul, #logmsg ol { padding: 0; list-style-position: inside; margin: 0 0 0 1em; }
#logmsg ul { text-indent: -1em; padding-left: 1em; }#logmsg ol { text-indent: -1.5em; padding-left: 1.5em; }
#logmsg > ul, #logmsg > ol { margin: 0 0 1em 0; }
#logmsg pre { background: #eee; padding: 1em; }
#logmsg blockquote { border: 1px solid #fa0; border-left-width: 10px; padding: 1em 1em 0 1em; background: white;}
#logmsg dl { margin: 0; }
#logmsg dt { font-weight: bold; }
#logmsg dd { margin: 0; padding: 0 0 0.5em 0; }
#logmsg dd:before { content:'\00bb';}
#logmsg table { border-spacing: 0px; border-collapse: collapse; border-top: 4px solid #fa0; border-bottom: 1px solid #fa0; background: #fff; }
#logmsg table th { text-align: left; font-weight: normal; padding: 0.2em 0.5em; border-top: 1px dotted #fa0; }
#logmsg table td { text-align: right; border-top: 1px dotted #fa0; padding: 0.2em 0.5em; }
#logmsg table thead th { text-align: center; border-bottom: 1px solid #fa0; }
#logmsg table th.Corner { text-align: left; }
#logmsg hr { border: none 0; border-top: 2px dashed #fa0; height: 1px; }
#header, #footer { color: #fff; background: #636; border: 1px #300 solid; padding: 6px; }
#patch { width: 100%; }
#patch h4 {font-family: verdana,arial,helvetica,sans-serif;font-size:10pt;padding:8px;background:#369;color:#fff;margin:0;}
#patch .propset h4, #patch .binary h4 {margin:0;}
#patch pre {padding:0;line-height:1.2em;margin:0;}
#patch .diff {width:100%;background:#eee;padding: 0 0 10px 0;overflow:auto;}
#patch .propset .diff, #patch .binary .diff {padding:10px 0;}
#patch span {display:block;padding:0 10px;}
#patch .modfile, #patch .addfile, #patch .delfile, #patch .propset, #patch .binary, #patch .copfile {border:1px solid #ccc;margin:10px 0;}
#patch ins {background:#dfd;text-decoration:none;display:block;padding:0 10px;}
#patch del {background:#fdd;text-decoration:none;display:block;padding:0 10px;}
#patch .lines, .info {color:#888;background:#fff;}
--></style>
<div id="msg">
<dl class="meta">
<dt>Revision</dt> <dd><a href="http://trac.webkit.org/projects/webkit/changeset/203148">203148</a></dd>
<dt>Author</dt> <dd>lforschler@apple.com</dd>
<dt>Date</dt> <dd>2016-07-12 20:30:00 -0700 (Tue, 12 Jul 2016)</dd>
</dl>
<h3>Log Message</h3>
<pre><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.</pre>
<h3>Modified Paths</h3>
<ul>
<li><a href="#trunkWebsitestestresultsChangeLog">trunk/Websites/test-results/ChangeLog</a></li>
<li><a href="#trunkWebsitestestresultsinitdatabasesql">trunk/Websites/test-results/init-database.sql</a></li>
</ul>
<h3>Added Paths</h3>
<ul>
<li><a href="#trunkWebsitestestresultspublic_partition_maintenance">trunk/Websites/test-results/public_partition_maintenance</a></li>
<li><a href="#trunkWebsitestestresultstestdatabasesql">trunk/Websites/test-results/test-database.sql</a></li>
</ul>
</div>
<div id="patch">
<h3>Diff</h3>
<a id="trunkWebsitestestresultsChangeLog"></a>
<div class="modfile"><h4>Modified: trunk/Websites/test-results/ChangeLog (203147 => 203148)</h4>
<pre class="diff"><span>
<span class="info">--- 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)
</span><span class="lines">@@ -1,3 +1,25 @@
</span><ins>+2016-07-12 Lucas Forschler <lforschler@apple.com>
+
+ <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.
+
</ins><span class="cx"> 2015-11-09 Ryosuke Niwa <rniwa@webkit.org>
</span><span class="cx">
</span><span class="cx"> Allow , in the builder name.
</span></span></pre></div>
<a id="trunkWebsitestestresultsinitdatabasesql"></a>
<div class="modfile"><h4>Modified: trunk/Websites/test-results/init-database.sql (203147 => 203148)</h4>
<pre class="diff"><span>
<span class="info">--- 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)
</span><span class="lines">@@ -1,11 +1,15 @@
</span><del>-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;
</del><ins>+-- Configuration file for postgres
</ins><span class="cx">
</span><ins>+-- 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;
+
</ins><span class="cx"> CREATE TABLE builders (
</span><span class="cx"> id serial PRIMARY KEY,
</span><span class="cx"> master varchar(64) NOT NULL,
</span><span class="lines">@@ -64,4 +68,128 @@
</span><span class="cx"> CREATE INDEX results_build ON results(build);
</span><span class="cx"> CREATE INDEX results_is_flaky ON results(is_flaky);
</span><span class="cx">
</span><del>-SET work_mem='1024MB';
</del><ins>+-- 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;
</ins><span class="cx">\ No newline at end of file
</span></span></pre></div>
<a id="trunkWebsitestestresultspublic_partition_maintenance"></a>
<div class="addfile"><h4>Added: trunk/Websites/test-results/public_partition_maintenance (0 => 203148)</h4>
<pre class="diff"><span>
<span class="info">--- trunk/Websites/test-results/public_partition_maintenance (rev 0)
+++ trunk/Websites/test-results/public_partition_maintenance 2016-07-13 03:30:00 UTC (rev 203148)
</span><span class="lines">@@ -0,0 +1,5 @@
</span><ins>+#!/bin/bash
+# NOTE: psql must be available in the path.
+psql -U "test-results-user" <<SQL
+SELECT public.partition_maintenance('results'::text, 'public'::text, now()::date );
+SQL
</ins><span class="cx">\ No newline at end of file
</span><span class="cx">Property changes on: trunk/Websites/test-results/public_partition_maintenance
</span><span class="cx">___________________________________________________________________
</span></span></pre></div>
<a id="svnexecutable"></a>
<div class="addfile"><h4>Added: svn:executable</h4></div>
<ins>+*
</ins><span class="cx">\ No newline at end of property
</span><a id="trunkWebsitestestresultstestdatabasesql"></a>
<div class="addfile"><h4>Added: trunk/Websites/test-results/test-database.sql (0 => 203148)</h4>
<pre class="diff"><span>
<span class="info">--- 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)
</span><span class="lines">@@ -0,0 +1,15 @@
</span><ins>+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);
</ins><span class="cx">\ No newline at end of file
</span></span></pre>
</div>
</div>
</body>
</html>