1-- Copyright (c) 2010, 2021, Oracle and/or its affiliates. 2-- 3-- This program is free software; you can redistribute it and/or modify 4-- it under the terms of the GNU General Public License, version 2.0, 5-- as published by the Free Software Foundation. 6-- 7-- This program is also distributed with certain software (including 8-- but not limited to OpenSSL) that is licensed under separate terms, 9-- as designated in a particular file or component or in included license 10-- documentation. The authors of MySQL hereby grant you an additional 11-- permission to link the program and your derivative works with the 12-- separately licensed software that they have included with MySQL. 13-- 14-- This program is distributed in the hope that it will be useful, 15-- but WITHOUT ANY WARRANTY; without even the implied warranty of 16-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 17-- GNU General Public License, version 2.0, for more details. 18-- 19-- You should have received a copy of the GNU General Public License 20-- along with this program; if not, write to the Free Software 21-- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA 22 23SET default_storage_engine=NDB; 24 25DROP DATABASE IF EXISTS crunddb; 26CREATE DATABASE crunddb; 27USE crunddb; 28 29-- schema details: $ ndb_desc -c localhost <table lower case> -d <database> 30 31CREATE TABLE a ( 32 id INT PRIMARY KEY, 33 cint INT, 34 clong BIGINT, 35 cfloat FLOAT, 36 cdouble DOUBLE 37); 38 39CREATE TABLE b ( 40 id INT PRIMARY KEY, 41 cint INT, 42 clong BIGINT, 43 cfloat FLOAT, 44 cdouble DOUBLE, 45 a_id INT, 46 -- XXX crund c++ code currently does not support VARBINARY/CHAR > 255 47 cvarbinary_def VARBINARY(202), 48 -- default charset: latin1 (~ISO 8859-1) 49 cvarchar_def VARCHAR(202), 50 -- cvarchar_ascii VARCHAR(202) CHARACTER SET ASCII, 51 -- cvarchar_ucs2 VARCHAR(202) CHARACTER SET UCS2, 52 -- cvarchar_utf8 VARCHAR(202) CHARACTER SET UTF8, 53 cblob_def BLOB(1000004), 54 ctext_def TEXT(1000004), 55 -- ctext_utf8 TEXT(202) CHARACTER SET UTF8, 56 CONSTRAINT FK_B_1 FOREIGN KEY (a_id) REFERENCES a (id), 57 INDEX I_B_FK (a_id) 58); 59 60CREATE TABLE s ( 61 c0 VARCHAR(10) NOT NULL, 62 c1 VARCHAR(10) NOT NULL, 63 c2 INT NOT NULL, 64 c3 INT NOT NULL, 65 c4 INT NULL, 66 c5 VARCHAR(50) NULL, 67 c6 VARCHAR(50) NULL, 68 c7 VARCHAR(10) NOT NULL, 69 c8 VARCHAR(10) NOT NULL, 70 c9 CHAR NULL, 71 c10 CHAR NULL, 72 c11 VARCHAR(10) NULL, 73 c12 VARCHAR(10) NULL, 74 c13 CHAR NULL, 75 c14 VARCHAR(50) NULL, 76 PRIMARY KEY (c0) 77 -- not clear why these additional hash+ordered indexes: 78 -- UNIQUE INDEX c0_UNIQUE USING BTREE (c0 ASC) 79 -- @10k rows, bulk insert: "job buffer congestion" node failures 80 -- with these additional hash+ordered indexes: 81 -- UNIQUE INDEX c1_UNIQUE USING BTREE (c1 ASC), 82 -- @1k rows, bulk insert: "job buffer congestion" node failures 83 -- with these additional hash+ordered indexes: 84 -- UNIQUE INDEX c2_UNIQUE (c2 ASC), 85 -- UNIQUE INDEX c7_UNIQUE (c7 ASC), 86 -- UNIQUE INDEX c8_UNIQUE (c8 ASC) 87); 88 89-- ---------------------------------------------------------------------- 90-- Results Schema 91-- ---------------------------------------------------------------------- 92 93-- aggregated results from crund runs 94-- example queries: 95-- load data infile '/Users/mz/mysql/crundcharting/log_results.csv' ignore into table results fields terminated by ',' ignore 1 lines; 96CREATE TABLE results ( 97 metric VARCHAR(16) NOT NULL, 98 cload VARCHAR(16) NOT NULL, 99 nrows INT NOT NULL, 100 nruns INT NOT NULL, 101 op VARCHAR(32) NOT NULL, 102 xmode ENUM('indy','each','bulk') NOT NULL, 103 nrows_metric DECIMAL(8,2) NOT NULL, 104 metric_nrows DECIMAL(8,2) NOT NULL, 105 rsdev DECIMAL(8,2) NOT NULL, 106 UNIQUE KEY (metric, cload, nrows, op, xmode) 107) ENGINE=MEMORY; 108 109-- base view with rtime as metric 110CREATE VIEW results_rtime 111 AS (SELECT cload, op, xmode, nrows, nrows_metric 112 FROM results 113 WHERE metric = 'rtime[ms]'); 114 115-- base views for fixed xMode values 116CREATE VIEW results_rtime_indy AS 117 (SELECT cload, op, nrows_metric AS 'indy', nrows 118 FROM results_rtime 119 WHERE xmode='indy'); 120CREATE VIEW results_rtime_each AS 121 (SELECT cload, op, nrows_metric AS 'each', nrows 122 FROM results_rtime 123 WHERE xmode='each'); 124CREATE VIEW results_rtime_bulk AS 125 (SELECT cload, op, nrows_metric AS 'bulk', nrows 126 FROM results_rtime 127 WHERE xmode='bulk'); 128 129-- joined view with xmode values in columns 130-- using left outer joines (full outer joins not supported, emulateable by union of left+right) 131-- i.e., ok for bulk-only results but have to deal with indy- and each-only results separately 132-- example queries: 133-- select * from results_rtime_xmode where op like '%getAttr%' order by nrows, cload, op; 134-- select * from results_rtime_xmode where op like '%getAttr%' and nrows=1000 order by cload, op; 135CREATE VIEW results_rtime_xmode AS 136 (SELECT nrows, 137 cload, 138 op, 139 IFNULL(i.indy,0) AS 'indy', 140 IFNULL(e.each,0) AS 'each', 141 IFNULL(b.bulk,0) AS 'bulk' 142 FROM results_rtime_bulk AS b 143 NATURAL LEFT OUTER JOIN results_rtime_each AS e 144 NATURAL LEFT OUTER JOIN results_rtime_indy AS i 145 ORDER BY nrows, cload, op); 146 147-- base views for fixed nrows values (10^2, 10^3, 10^4) 148CREATE VIEW results_rtime_nrows2 AS 149 (SELECT cload, op, nrows_metric AS 'nrows2', xmode 150 FROM results_rtime 151 WHERE nrows=100); 152CREATE VIEW results_rtime_nrows3 AS 153 (SELECT cload, op, nrows_metric AS 'nrows3', xmode 154 FROM results_rtime 155 WHERE nrows=1000); 156CREATE VIEW results_rtime_nrows4 AS 157 (SELECT cload, op, nrows_metric AS 'nrows4', xmode 158 FROM results_rtime 159 WHERE nrows=10000); 160 161-- joined view with nrows values in columns 162-- using inner joines (full outer joins not supported, emulateable by union of left+right) 163-- i.e., have to deal with r2/r3/r4-only results separately 164-- example queries: 165-- select * from results_rtime_nrows where op like '%getAttr%' order by xmode, cload, op; 166-- select * from results_rtime_nrows where op like '%getAttr%' and xmode='bulk' order by cload, op; 167-- select * from results_rtime_nrows where op like '%delAll%' and xmode='bulk' order by cload, op; 168CREATE VIEW results_rtime_nrows AS 169 (SELECT xmode, 170 cload, 171 op, 172 IFNULL(r2.nrows2,0) AS 'nrows2', 173 IFNULL(r3.nrows3,0) AS 'nrows3', 174 IFNULL(r4.nrows4,0) AS 'nrows4' 175 FROM results_rtime_nrows2 AS r2 176 NATURAL JOIN results_rtime_nrows3 AS r3 177 NATURAL JOIN results_rtime_nrows4 AS r4 178 ORDER BY xmode, cload, op); 179