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