1
/*****************************************************************************\
2
* mysql_rollup.c - functions for rolling up data for associations
3
* and machines from the mysql storage.
4
*****************************************************************************
6
* Copyright (C) 2004-2007 The Regents of the University of California.
7
* Copyright (C) 2008 Lawrence Livermore National Security.
8
* Produced at Lawrence Livermore National Laboratory (cf, DISCLAIMER).
9
* Written by Danny Auble <da@llnl.gov>
12
* This file is part of SLURM, a resource management program.
13
* For details, see <http://www.llnl.gov/linux/slurm/>.
15
* SLURM is free software; you can redistribute it and/or modify it under
16
* the terms of the GNU General Public License as published by the Free
17
* Software Foundation; either version 2 of the License, or (at your option)
20
* In addition, as a special exception, the copyright holders give permission
21
* to link the code of portions of this program with the OpenSSL library under
22
* certain conditions as described in each individual source file, and
23
* distribute linked combinations including the two. You must obey the GNU
24
* General Public License in all respects for all of the code used other than
25
* OpenSSL. If you modify file(s) with this exception, you may extend this
26
* exception to your version of the file(s), but you are not obligated to do
27
* so. If you do not wish to do so, delete this exception statement from your
28
* version. If you delete this exception statement from all source files in
29
* the program, then also delete it here.
31
* SLURM is distributed in the hope that it will be useful, but WITHOUT ANY
32
* WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
33
* FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
36
* You should have received a copy of the GNU General Public License along
37
* with SLURM; if not, write to the Free Software Foundation, Inc.,
38
* 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
39
\*****************************************************************************/
41
#include "mysql_rollup.h"
48
} local_assoc_usage_t;
61
} local_cluster_usage_t;
64
extern void _destroy_local_assoc_usage(void *object)
66
local_assoc_usage_t *a_usage = (local_assoc_usage_t *)object;
72
extern void _destroy_local_cluster_usage(void *object)
74
local_cluster_usage_t *c_usage = (local_cluster_usage_t *)object;
81
extern int mysql_hourly_rollup(mysql_conn_t *mysql_conn,
82
time_t start, time_t end)
84
int rc = SLURM_SUCCESS;
87
time_t now = time(NULL);
88
time_t curr_start = start;
89
time_t curr_end = curr_start + add_sec;
91
MYSQL_RES *result = NULL;
93
ListIterator a_itr = NULL;
94
ListIterator c_itr = NULL;
95
List assoc_usage_list = list_create(_destroy_local_assoc_usage);
96
List cluster_usage_list = list_create(_destroy_local_cluster_usage);
97
char *event_req_inx[] = {
104
char *event_str = NULL;
113
char *job_req_inx[] = {
125
char *job_str = NULL;
139
char *suspend_req_inx[] = {
143
char *suspend_str = NULL;
151
xstrfmtcat(event_str, "%s", event_req_inx[i]);
152
for(i=1; i<EVENT_REQ_COUNT; i++) {
153
xstrfmtcat(event_str, ", %s", event_req_inx[i]);
157
xstrfmtcat(job_str, "%s", job_req_inx[i]);
158
for(i=1; i<JOB_REQ_COUNT; i++) {
159
xstrfmtcat(job_str, ", %s", job_req_inx[i]);
163
xstrfmtcat(suspend_str, "%s", suspend_req_inx[i]);
164
for(i=1; i<SUSPEND_REQ_COUNT; i++) {
165
xstrfmtcat(suspend_str, ", %s", suspend_req_inx[i]);
168
/* info("begin start %s", ctime(&curr_start)); */
169
/* info("begin end %s", ctime(&curr_end)); */
170
a_itr = list_iterator_create(assoc_usage_list);
171
c_itr = list_iterator_create(cluster_usage_list);
172
while(curr_start < end) {
175
local_cluster_usage_t *c_usage = NULL;
176
local_assoc_usage_t *a_usage = NULL;
177
debug3("curr hour is now %d-%d", curr_start, curr_end);
178
/* info("start %s", ctime(&curr_start)); */
179
/* info("end %s", ctime(&curr_end)); */
181
// first get the events during this time
182
query = xstrdup_printf("select %s from %s where "
183
"(period_start < %d "
184
"&& (period_end >= %d "
185
"|| period_end = 0)) "
186
"order by node_name, period_start",
187
event_str, event_table,
188
curr_end, curr_start);
190
debug3("%d query\n%s", mysql_conn->conn, query);
191
if(!(result = mysql_db_query_ret(
192
mysql_conn->acct_mysql_db, query, 0))) {
198
while((row = mysql_fetch_row(result))) {
199
int row_start = atoi(row[EVENT_REQ_START]);
200
int row_end = atoi(row[EVENT_REQ_END]);
201
int row_cpu = atoi(row[EVENT_REQ_CPU]);
203
if(row_start < curr_start)
204
row_start = curr_start;
206
if(!row_end || row_end > curr_end)
209
/* Don't worry about it if the time is less
212
if((row_end - row_start) < 1)
215
if(!row[EVENT_REQ_NAME][0]) {
216
list_iterator_reset(c_itr);
217
while((c_usage = list_next(c_itr))) {
218
if(!strcmp(c_usage->name,
219
row[EVENT_REQ_CLUSTER])) {
223
/* if the cpu count changes we will
224
* only care about the last cpu count but
225
* we will keep a total of the time for
226
* all cpus to get the correct cpu time
227
* for the entire period.
231
sizeof(local_cluster_usage_t));
233
xstrdup(row[EVENT_REQ_CLUSTER]);
234
c_usage->cpu_count = row_cpu;
235
c_usage->total_time =
236
(row_end - row_start) * row_cpu;
237
c_usage->start = row_start;
238
c_usage->end = row_end;
239
list_append(cluster_usage_list,
242
c_usage->cpu_count = row_cpu;
243
c_usage->total_time +=
244
(row_end - row_start) * row_cpu;
245
c_usage->end = row_end;
250
list_iterator_reset(c_itr);
251
while((c_usage = list_next(c_itr))) {
252
if(!strcmp(c_usage->name,
253
row[EVENT_REQ_CLUSTER])) {
254
int local_start = row_start;
255
int local_end = row_end;
256
if(c_usage->start > local_start)
257
local_start = c_usage->start;
258
if(c_usage->end < local_end)
259
local_end = c_usage->end;
261
if((local_end - local_start) < 1)
264
seconds = (local_end - local_start);
266
/* info("node %s adds " */
267
/* "(%d)(%d-%d) * %d = %d " */
269
/* row[EVENT_REQ_NAME], */
271
/* local_end, local_start, */
273
/* seconds * row_cpu, */
275
c_usage->d_cpu += seconds * row_cpu;
281
mysql_free_result(result);
283
query = xstrdup_printf("select %s from %s as t1, "
285
"(eligible < %d && (end >= %d "
286
"|| end = 0)) && associd=t2.id "
287
"order by associd, eligible",
288
job_str, job_table, assoc_table,
289
curr_end, curr_start, curr_start);
291
debug3("%d query\n%s", mysql_conn->conn, query);
292
if(!(result = mysql_db_query_ret(
293
mysql_conn->acct_mysql_db, query, 0))) {
299
while((row = mysql_fetch_row(result))) {
300
int job_id = atoi(row[JOB_REQ_JOBID]);
301
int assoc_id = atoi(row[JOB_REQ_ASSOCID]);
302
int row_eligible = atoi(row[JOB_REQ_ELG]);
303
int row_start = atoi(row[JOB_REQ_START]);
304
int row_end = atoi(row[JOB_REQ_END]);
305
int row_acpu = atoi(row[JOB_REQ_ACPU]);
306
int row_rcpu = atoi(row[JOB_REQ_RCPU]);
309
if(row_start && (row_start < curr_start))
310
row_start = curr_start;
312
if(!row_start && row_end)
315
if(!row_end || row_end > curr_end)
318
if(last_id != assoc_id) {
320
xmalloc(sizeof(local_cluster_usage_t));
321
a_usage->assoc_id = assoc_id;
322
list_append(assoc_usage_list, a_usage);
327
if(!row_start || ((row_end - row_start) < 1))
330
seconds = (row_end - row_start);
332
if(row[JOB_REQ_SUSPENDED]) {
333
MYSQL_RES *result2 = NULL;
335
/* get the suspended time for this job */
336
query = xstrdup_printf(
337
"select %s from %s where "
338
"(start < %d && (end >= %d "
339
"|| end = 0)) && id=%s "
341
suspend_str, suspend_table,
342
curr_end, curr_start,
343
row[JOB_REQ_DB_INX]);
345
debug4("%d query\n%s", mysql_conn->conn, query);
346
if(!(result2 = mysql_db_query_ret(
347
mysql_conn->acct_mysql_db,
353
while((row2 = mysql_fetch_row(result2))) {
355
atoi(row2[SUSPEND_REQ_START]);
357
atoi(row2[SUSPEND_REQ_END]);
362
if(row_start > local_start)
363
local_start = row_start;
364
if(row_end < local_end)
367
if((local_end - local_start) < 1)
370
seconds -= (local_end - local_start);
372
mysql_free_result(result2);
376
debug4("This job (%u) was suspended "
377
"the entire hour", job_id);
382
a_usage->a_cpu += seconds * row_acpu;
385
if(!row[JOB_REQ_CLUSTER])
388
list_iterator_reset(c_itr);
389
while((c_usage = list_next(c_itr))) {
390
if(!strcmp(c_usage->name,
391
row[JOB_REQ_CLUSTER])) {
392
if(!row_start || seconds < 1)
395
/* info("%d assoc %d adds " */
396
/* "(%d)(%d-%d) * %d = %d " */
399
/* a_usage->assoc_id, */
401
/* row_end, row_start, */
403
/* seconds * row_acpu, */
406
c_usage->a_cpu += seconds * row_acpu;
409
/* now reserved time */
411
row_start < c_usage->start)
415
row_start = row_eligible;
416
if(c_usage->start > row_start)
417
row_start = c_usage->start;
418
if(c_usage->end < row_end)
419
row_end = c_usage->end;
421
if((row_end - row_start) < 1)
424
seconds = (row_end - row_start);
426
/* info("%d assoc %d reserved " */
427
/* "(%d)(%d-%d) * %d = %d " */
432
/* row_end, row_start, */
434
/* seconds * row_rcpu, */
436
c_usage->r_cpu += seconds * row_rcpu;
442
mysql_free_result(result);
444
list_iterator_reset(c_itr);
445
while((c_usage = list_next(c_itr))) {
446
c_usage->i_cpu = c_usage->total_time - c_usage->a_cpu -
447
c_usage->d_cpu - c_usage->r_cpu;
448
/* sanity check just to make sure we have a
449
* legitimate time after we calulated
450
* idle/reserved time put extra in the over
454
if(c_usage->i_cpu < 0) {
455
c_usage->r_cpu += c_usage->i_cpu;
456
c_usage->o_cpu -= c_usage->i_cpu;
460
/* info("cluster %s(%d) down %d alloc %d " */
461
/* "resv %d idle %d over %d " */
462
/* "total= %d = %d from %s", */
464
/* c_usage->cpu_count, c_usage->d_cpu, */
465
/* c_usage->a_cpu, */
466
/* c_usage->r_cpu, c_usage->i_cpu, c_usage->o_cpu, */
467
/* c_usage->d_cpu + c_usage->a_cpu + */
468
/* c_usage->r_cpu + c_usage->i_cpu, */
469
/* c_usage->total_time, */
470
/* ctime(&c_usage->start)); */
471
/* info("to %s", ctime(&c_usage->end)); */
474
", (%d, %d, '%s', %d, %d, "
475
"%d, %d, %d, %d, %d) "
476
"on duplicate key update "
477
"mod_time=%d, cpu_count=%d, "
478
"alloc_cpu_secs=%d, "
481
"over_cpu_secs=%d, resv_cpu_secs=%d",
483
c_usage->name, c_usage->start,
484
c_usage->cpu_count, c_usage->a_cpu,
485
c_usage->d_cpu, c_usage->i_cpu,
486
c_usage->o_cpu, c_usage->r_cpu,
488
c_usage->cpu_count, c_usage->a_cpu,
489
c_usage->d_cpu, c_usage->i_cpu,
490
c_usage->o_cpu, c_usage->r_cpu);
493
"insert into %s (creation_time, "
494
"mod_time, cluster, period_start, "
495
"cpu_count, alloc_cpu_secs, "
496
"down_cpu_secs, idle_cpu_secs, "
497
"over_cpu_secs, resv_cpu_secs) "
498
"values (%d, %d, '%s', %d, %d, "
499
"%d, %d, %d, %d, %d) "
500
"on duplicate key update "
501
"mod_time=%d, cpu_count=%d, "
502
"alloc_cpu_secs=%d, "
505
"over_cpu_secs=%d, resv_cpu_secs=%d",
506
cluster_hour_table, now, now,
507
c_usage->name, c_usage->start,
508
c_usage->cpu_count, c_usage->a_cpu,
509
c_usage->d_cpu, c_usage->i_cpu,
510
c_usage->o_cpu, c_usage->r_cpu,
512
c_usage->cpu_count, c_usage->a_cpu,
513
c_usage->d_cpu, c_usage->i_cpu,
514
c_usage->o_cpu, c_usage->r_cpu);
518
rc = mysql_db_query(mysql_conn->acct_mysql_db, query);
520
if(rc != SLURM_SUCCESS) {
521
error("Couldn't add cluster hour rollup");
526
list_iterator_reset(a_itr);
527
while((a_usage = list_next(a_itr))) {
528
/* info("association (%d) %d alloc %d", */
529
/* a_usage->assoc_id, last_id, */
530
/* a_usage->a_cpu); */
533
", (%d, %d, %d, %d, %d, "
535
"on duplicate key update "
536
"mod_time=%d, alloc_cpu_secs=%d",
538
a_usage->assoc_id, curr_start,
540
now, a_usage->a_cpu);
543
"insert into %s (creation_time, "
544
"mod_time, id, period_start, "
545
"alloc_cpu_secs) values "
546
"(%d, %d, %d, %d, %d) "
547
"on duplicate key update "
548
"mod_time=%d, alloc_cpu_secs=%d",
549
assoc_hour_table, now, now,
550
a_usage->assoc_id, curr_start,
552
now, a_usage->a_cpu);
557
debug3("%d query\n%s", mysql_conn->conn, query);
558
rc = mysql_db_query(mysql_conn->acct_mysql_db, query);
560
if(rc != SLURM_SUCCESS) {
561
error("Couldn't add assoc hour rollup");
565
list_flush(assoc_usage_list);
566
list_flush(cluster_usage_list);
567
curr_start = curr_end;
568
curr_end = curr_start + add_sec;
574
list_iterator_destroy(a_itr);
575
list_iterator_destroy(c_itr);
577
list_destroy(assoc_usage_list);
578
list_destroy(cluster_usage_list);
579
/* info("stop start %s", ctime(&curr_start)); */
580
/* info("stop end %s", ctime(&curr_end)); */
583
extern int mysql_daily_rollup(mysql_conn_t *mysql_conn,
584
time_t start, time_t end)
586
/* can't just add 86400 since daylight savings starts and ends every
589
int rc = SLURM_SUCCESS;
591
time_t curr_start = start;
593
time_t now = time(NULL);
596
if(!localtime_r(&curr_start, &start_tm)) {
597
error("Couldn't get localtime from day start %d", curr_start);
602
start_tm.tm_hour = 0;
604
start_tm.tm_isdst = -1;
605
curr_end = mktime(&start_tm);
607
while(curr_start < end) {
608
debug3("curr day is now %d-%d", curr_start, curr_end);
609
/* info("start %s", ctime(&curr_start)); */
610
/* info("end %s", ctime(&curr_end)); */
611
query = xstrdup_printf(
612
"insert into %s (creation_time, mod_time, id, "
613
"period_start, alloc_cpu_secs) select %d, %d, id, "
614
"%d, @ASUM:=SUM(alloc_cpu_secs) from %s where "
615
"(period_start < %d && period_start >= %d) "
616
"group by id on duplicate key update mod_time=%d, "
617
"alloc_cpu_secs=@ASUM;",
618
assoc_day_table, now, now, curr_start,
620
curr_end, curr_start, now);
622
"insert into %s (creation_time, "
623
"mod_time, cluster, period_start, cpu_count, "
624
"alloc_cpu_secs, down_cpu_secs, idle_cpu_secs, "
625
"over_cpu_secs, resv_cpu_secs) "
626
"select %d, %d, cluster, "
627
"%d, @CPU:=MAX(cpu_count), "
628
"@ASUM:=SUM(alloc_cpu_secs), "
629
"@DSUM:=SUM(down_cpu_secs), "
630
"@ISUM:=SUM(idle_cpu_secs), "
631
"@OSUM:=SUM(over_cpu_secs), "
632
"@RSUM:=SUM(resv_cpu_secs) from %s where "
633
"(period_start < %d && period_start >= %d) "
634
"group by cluster on duplicate key update "
635
"mod_time=%d, cpu_count=@CPU, "
636
"alloc_cpu_secs=@ASUM, down_cpu_secs=@DSUM, "
637
"idle_cpu_secs=@ISUM, over_cpu_secs=@OSUM, "
638
"resv_cpu_secs=@RSUM;",
639
cluster_day_table, now, now, curr_start,
641
curr_end, curr_start, now);
642
debug3("%d query\n%s", mysql_conn->conn, query);
643
rc = mysql_db_query(mysql_conn->acct_mysql_db, query);
645
if(rc != SLURM_SUCCESS) {
646
error("Couldn't add day rollup");
650
curr_start = curr_end;
651
if(!localtime_r(&curr_start, &start_tm)) {
652
error("Couldn't get localtime from day start %d",
658
start_tm.tm_hour = 0;
660
start_tm.tm_isdst = -1;
661
curr_end = mktime(&start_tm);
663
/* remove all data from suspend table that was older than
666
query = xstrdup_printf("delete from %s where end < %d && end != 0",
667
suspend_table, start);
668
rc = mysql_db_query(mysql_conn->acct_mysql_db, query);
670
if(rc != SLURM_SUCCESS) {
671
error("Couldn't remove old suspend data");
676
/* info("stop start %s", ctime(&curr_start)); */
677
/* info("stop end %s", ctime(&curr_end)); */
679
return SLURM_SUCCESS;
681
extern int mysql_monthly_rollup(mysql_conn_t *mysql_conn,
682
time_t start, time_t end)
684
int rc = SLURM_SUCCESS;
686
time_t curr_start = start;
688
time_t now = time(NULL);
691
if(!localtime_r(&curr_start, &start_tm)) {
692
error("Couldn't get localtime from month start %d", curr_start);
697
start_tm.tm_hour = 0;
698
start_tm.tm_mday = 1;
700
start_tm.tm_isdst = -1;
701
curr_end = mktime(&start_tm);
703
while(curr_start < end) {
704
debug3("curr month is now %d-%d", curr_start, curr_end);
705
/* info("start %s", ctime(&curr_start)); */
706
/* info("end %s", ctime(&curr_end)); */
707
query = xstrdup_printf(
708
"insert into %s (creation_time, mod_time, id, "
709
"period_start, alloc_cpu_secs) select %d, %d, id, "
710
"%d, @ASUM:=SUM(alloc_cpu_secs) from %s where "
711
"(period_start < %d && period_start >= %d) "
712
"group by id on duplicate key update mod_time=%d, "
713
"alloc_cpu_secs=@ASUM;",
714
assoc_month_table, now, now, curr_start,
716
curr_end, curr_start, now);
718
"insert into %s (creation_time, "
719
"mod_time, cluster, period_start, cpu_count, "
720
"alloc_cpu_secs, down_cpu_secs, idle_cpu_secs, "
721
"over_cpu_secs, resv_cpu_secs) "
722
"select %d, %d, cluster, "
723
"%d, @CPU:=MAX(cpu_count), "
724
"@ASUM:=SUM(alloc_cpu_secs), "
725
"@DSUM:=SUM(down_cpu_secs), "
726
"@ISUM:=SUM(idle_cpu_secs), "
727
"@OSUM:=SUM(over_cpu_secs), "
728
"@RSUM:=SUM(resv_cpu_secs) from %s where "
729
"(period_start < %d && period_start >= %d) "
730
"group by cluster on duplicate key update "
731
"mod_time=%d, cpu_count=@CPU, "
732
"alloc_cpu_secs=@ASUM, down_cpu_secs=@DSUM, "
733
"idle_cpu_secs=@ISUM, over_cpu_secs=@OSUM, "
734
"resv_cpu_secs=@RSUM;",
735
cluster_month_table, now, now, curr_start,
737
curr_end, curr_start, now);
738
debug3("%d query\n%s", mysql_conn->conn, query);
739
rc = mysql_db_query(mysql_conn->acct_mysql_db, query);
741
if(rc != SLURM_SUCCESS) {
742
error("Couldn't add day rollup");
746
curr_start = curr_end;
747
if(!localtime_r(&curr_start, &start_tm)) {
748
error("Couldn't get localtime from month start %d",
753
start_tm.tm_hour = 0;
754
start_tm.tm_mday = 1;
756
start_tm.tm_isdst = -1;
757
curr_end = mktime(&start_tm);
759
return SLURM_SUCCESS;
added
removed
src/plugins/accounting_storage/mysql/mysql_rollup.c
