-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathscr.mysql
147 lines (134 loc) · 5.68 KB
/
scr.mysql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
# This file defines the database tables used for MySQL logging.
# This feature is considered to be at beta-level support. The
# following instructions should work, however, they have not
# been well tested. If you find any problems, please notify
# the SCR developers. To disable this feature, configure SCR
# with the --without-mysql flag, or set SCR_DB_ENABLE=0 at
# runtime or in the scr.conf file.
#
# To use this feature, one must run a MySQL server on a host
# accessible to the compute nodes as well as the nodes that run
# the batch job scripts. One must also have the libmysqlclient
# library installed, and SCR should be configured with the
# --with-mysql flag.
#
# To set up the database, first set passwords in this file for
# the "scr" and "scr_insert" users in the "IDENTIFIED BY" fields
# in the lines below, e.g.,
#
# GRANT ALL
# ON scr.* TO `scr`
# IDENTIFIED BY 'MySecretPassword1'
# GRANT INSERT,SELECT
# ON scr.* TO `scr_insert`
# IDENTIFIED BY 'MySecretPassword2'
#
# Then provide this file as input to a mysql command on the host
# running the MySQL server, e.g.,.
#
# mysql -u root -p < scr.mysql
#
# The SCR library and the scripts both read and write records to
# this database. Thus, the database must be accessible to both
# the compute nodes and the nodes where the batch scripts run.
#
# (There may be a step necessary here to adjust the "users" table
# in the "mysql" database to enable remote access to the scr and
# scr_insert users from the compute nodes and the nodes that run
# the batch job script.)
#
# After creating the database, one must then specify the hostname,
# database name, username, and password in the system scr.conf file.
# For the time being, only the scr_insert user is used, e.g.,
#
# >>: cat /etc/scr.conf
# SCR_DB_ENABLE=1
# SCR_DB_HOST=host.running.mysql.server
# SCR_DB_NAME=scr
# SCR_DB_USER=scr_insert
# SCR_DB_PASS=MySecretPassword2
# !!!! WARNING !!!!
# Currently all users have full read and write access to the
# SCR logging database, so clever users can see data on jobs from
# other users.
# !!!! WARNING !!!!
CREATE DATABASE IF NOT EXISTS `scr`;
# !!!! TODO !!!!
# Define passwords in the IDENTIFIED BY fields before creating the
# database.
# !!!! TODO !!!!
# !!!! WARNING !!!!
# Be sure no passwords are recorded in this file when checking it
# into a revision control system.
# !!!! WARNING !!!!
GRANT ALL
ON scr.* TO `scr`
IDENTIFIED BY '';
GRANT INSERT,SELECT
ON scr.* TO `scr_insert`
IDENTIFIED BY '';
USE `scr`;
DROP TABLE IF EXISTS `types`;
DROP TABLE IF EXISTS `usernames`;
DROP TABLE IF EXISTS `jobnames`;
DROP TABLE IF EXISTS `jobs`;
DROP TABLE IF EXISTS `transfers`;
DROP TABLE IF EXISTS `events`;
# maps a type string to an id
CREATE TABLE IF NOT EXISTS `types` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(512) NOT NULL,
UNIQUE INDEX `name` (`name`(512))
) TYPE=MyISAM;
# maps a username string to an id
CREATE TABLE IF NOT EXISTS `usernames` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(512) NOT NULL,
UNIQUE INDEX `name` (`name`(512))
) TYPE=MyISAM;
# maps a jobname string to an id
CREATE TABLE IF NOT EXISTS `jobnames` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(512) NOT NULL,
UNIQUE INDEX `name` (`name`(512))
) TYPE=MyISAM;
# records username, jobname, and starttime of a job
# the same username and jobname maps to the same "job"
# which enables one to track data across a series of runs
CREATE TABLE IF NOT EXISTS `jobs` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`username_id` INT UNSIGNED NOT NULL, # id of user running the job
`jobname_id` INT UNSIGNED NOT NULL, # id of the name of the job
`start` DATETIME NOT NULL, # time that job started
UNIQUE INDEX `name` (`username_id`,`jobname_id`)
) TYPE=MyISAM;
# records checkpoint, fetch, and flush operation times and directories
CREATE TABLE IF NOT EXISTS `transfers` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`job_id` INT NOT NULL, # id to jobs table
`type_id` INT UNSIGNED NOT NULL, # fetch, flush, or drain?
`dset_id` INT UNSIGNED NOT NULL, # SCR dataset number
`start` DATETIME NOT NULL, # start time of operation
`end` DATETIME NOT NULL, # end time of operation
`time` FLOAT NOT NULL, # total time of operation
`bytes` FLOAT NOT NULL, # total bytes moved
`bw` FLOAT NOT NULL, # bandwidth of operation
`from` BLOB NOT NULL, # source directory
`to` BLOB NOT NULL, # target directory
INDEX `jobid_start` (`job_id`,`start`),
INDEX `jobid_typeid_start` (`job_id`,`type_id`,`start`),
INDEX `typeid_start` (`type_id`,`start`)
) TYPE=MyISAM;
# records various events, including failed nodes
CREATE TABLE IF NOT EXISTS `events` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`job_id` INT NOT NULL, # id to jobs table
`type_id` INT UNSIGNED NOT NULL, # fetch, flush, or drain?
`dset_id` INT UNSIGNED NULL, # SCR dataset number
`start` DATETIME NULL, # start time of operation
`time` FLOAT NULL, # total time of operation
`note` BLOB NULL, # source directory
INDEX `jobid_start` (`job_id`,`start`),
INDEX `jobid_typeid_start` (`job_id`,`type_id`,`start`),
INDEX `typeid_start` (`type_id`,`start`)
) TYPE=MyISAM;