ci_builds.rb 19.7 KB
Newer Older
1
module GitLab
2
  module Exporter
3
    module Database
Ben Kochie's avatar
Ben Kochie committed
4
      # A helper class to collect CI builds metrics.
5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
      class CiBuildsCollector < Base # rubocop:disable Metrics/ClassLength
        SET_RANDOM_PAGE_COST = "SET LOCAL random_page_cost TO 1".freeze

        BUILDS_QUERY_EE =
          <<~SQL.freeze
            SELECT
              projects.namespace_id,
              ci_builds.status,
              projects.shared_runners_enabled,
              (COALESCE(namespaces.shared_runners_minutes_limit, application_settings.shared_runners_minutes, 0) = 0 OR
                 COALESCE(namespace_statistics.shared_runners_seconds, 0) < COALESCE(namespaces.shared_runners_minutes_limit, application_settings.shared_runners_minutes, 0) * 60) as has_minutes,
              COUNT(*) AS count
            FROM ci_builds
            JOIN projects
              ON projects.id = ci_builds.project_id
            JOIN namespaces
              ON namespaces.id = projects.namespace_id
            LEFT JOIN namespace_statistics
              ON namespace_statistics.namespace_id = namespaces.id
            JOIN application_settings
              ON application_settings.id = 1
            WHERE ci_builds.type = 'Ci::Build'
27
              AND ci_builds.status = '%s'
28 29
              -- The created_at filter has been introduced for performance reasons only
              AND ci_builds.created_at > NOW() - INTERVAL '7 days'
30 31 32 33 34 35 36 37 38 39 40
              AND projects.pending_delete = 'f'
            GROUP BY
              projects.namespace_id,
              ci_builds.status,
              projects.shared_runners_enabled,
              namespaces.shared_runners_minutes_limit,
              namespace_statistics.shared_runners_seconds,
              application_settings.shared_runners_minutes
          SQL

        BUILDS_QUERY_CE =
Tomasz Maczukin's avatar
Tomasz Maczukin committed
41 42 43 44 45 46
          <<~SQL.freeze
            SELECT
              projects.namespace_id,
              ci_builds.status,
              projects.shared_runners_enabled,
              COUNT(*) AS count
47 48 49 50
            FROM ci_builds
            JOIN projects
              ON projects.id = ci_builds.project_id
            WHERE ci_builds.type = 'Ci::Build'
51
              AND ci_builds.status = '%s'
52 53 54 55 56
              AND projects.pending_delete = 'f'
            GROUP BY
              projects.namespace_id,
              ci_builds.status,
              projects.shared_runners_enabled
Tomasz Maczukin's avatar
Tomasz Maczukin committed
57
          SQL
58

Ben Kochie's avatar
Ben Kochie committed
59
        STALE_BUILDS_QUERY =
Tomasz Maczukin's avatar
Tomasz Maczukin committed
60 61 62
          <<~SQL.freeze
            SELECT
              COUNT(*) AS count
63 64 65 66 67 68 69
            FROM ci_builds
            JOIN projects
              ON projects.id = ci_builds.project_id
            WHERE ci_builds.type = 'Ci::Build'
              AND ci_builds.status = 'running'
              AND ci_builds.updated_at < NOW() - INTERVAL '1 hour'
              AND projects.pending_delete = 'f'
Tomasz Maczukin's avatar
Tomasz Maczukin committed
70
          SQL
71

72
        PER_RUNNER_QUERY_EE =
Zeger-Jan van de Weg's avatar
Zeger-Jan van de Weg committed
73 74 75 76
          <<~SQL.freeze
            SELECT
              ci_builds.runner_id,
              ci_runners.is_shared,
Tomasz Maczukin's avatar
Tomasz Maczukin committed
77
              projects.namespace_id,
78 79 80 81
              projects.mirror,
              projects.mirror_trigger_builds,
              ci_pipelines.pipeline_schedule_id,
              ci_builds.trigger_request_id,
82 83
              (COALESCE(namespaces.shared_runners_minutes_limit, application_settings.shared_runners_minutes, 0) = 0 OR
                 COALESCE(namespace_statistics.shared_runners_seconds, 0) < COALESCE(namespaces.shared_runners_minutes_limit, application_settings.shared_runners_minutes, 0) * 60) as has_minutes,
84 85 86 87 88 89 90 91
              COUNT(*) AS count
            FROM ci_builds
            JOIN ci_runners
              ON ci_runners.id = ci_builds.runner_id
            JOIN projects
              ON projects.id = ci_builds.project_id
            JOIN ci_pipelines
              ON ci_pipelines.id = ci_builds.commit_id
92 93 94 95 96 97
            JOIN namespaces
              ON namespaces.id = projects.namespace_id
            LEFT JOIN namespace_statistics
              ON namespace_statistics.namespace_id = namespaces.id
            JOIN application_settings
              ON application_settings.id = 1
98 99
            WHERE ci_builds.type = 'Ci::Build'
              AND ci_builds.status = 'running'
100 101
              -- The created_at filter has been introduced for performance reasons only
              AND ci_builds.created_at > NOW() - INTERVAL '7 days'
Tomasz Maczukin's avatar
Tomasz Maczukin committed
102
              AND projects.pending_delete = 'f'
103 104 105
            GROUP BY
              ci_builds.runner_id,
              ci_runners.is_shared,
106 107 108
              projects.namespace_id,
              projects.mirror,
              projects.mirror_trigger_builds,
109
              ci_pipelines.pipeline_schedule_id,
Zeger-Jan van de Weg's avatar
Zeger-Jan van de Weg committed
110
              ci_builds.trigger_request_id,
111 112 113
              namespaces.shared_runners_minutes_limit,
              namespace_statistics.shared_runners_seconds,
              application_settings.shared_runners_minutes
114 115 116 117 118 119 120
          SQL

        PER_RUNNER_QUERY_CE =
          <<~SQL.freeze
            SELECT
              ci_builds.runner_id,
              ci_runners.is_shared,
Tomasz Maczukin's avatar
Tomasz Maczukin committed
121
              projects.namespace_id,
Zeger-Jan van de Weg's avatar
Zeger-Jan van de Weg committed
122 123 124 125 126 127 128 129 130 131 132 133
              ci_pipelines.pipeline_schedule_id,
              ci_builds.trigger_request_id,
              COUNT(*) AS count
            FROM ci_builds
            JOIN ci_runners
              ON ci_runners.id = ci_builds.runner_id
            JOIN projects
              ON projects.id = ci_builds.project_id
            JOIN ci_pipelines
              ON ci_pipelines.id = ci_builds.commit_id
            WHERE ci_builds.type = 'Ci::Build'
              AND ci_builds.status = 'running'
Tomasz Maczukin's avatar
Tomasz Maczukin committed
134
              AND projects.pending_delete = 'f'
Zeger-Jan van de Weg's avatar
Zeger-Jan van de Weg committed
135 136 137
            GROUP BY
              ci_builds.runner_id,
              ci_runners.is_shared,
Tomasz Maczukin's avatar
Tomasz Maczukin committed
138
              projects.namespace_id,
Zeger-Jan van de Weg's avatar
Zeger-Jan van de Weg committed
139 140 141
              ci_pipelines.pipeline_schedule_id,
              ci_builds.trigger_request_id
          SQL
142

Tomasz Maczukin's avatar
Tomasz Maczukin committed
143 144 145
        MIRROR_COLUMN_QUERY =
          <<~SQL.freeze
            SELECT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='projects' AND column_name='mirror')
Tomasz Maczukin's avatar
Tomasz Maczukin committed
146
          SQL
Tomasz Maczukin's avatar
Tomasz Maczukin committed
147

148 149 150
        REPEATED_COMMANDS_QUERY_EE =
          <<~SQL.freeze
            SELECT
151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177
              subquery.namespace_id,
              subquery.shared_runners_enabled,
              subquery.project_id,
              subquery.status,
              subquery.has_minutes,
              MAX(subquery.count) as count
            FROM (
              SELECT
                projects.namespace_id,
                projects.shared_runners_enabled,
                ci_builds.project_id,
                ci_builds.commit_id,
                ci_builds.status,
                (COALESCE(namespaces.shared_runners_minutes_limit, application_settings.shared_runners_minutes, 0) = 0 OR
                   COALESCE(namespace_statistics.shared_runners_seconds, 0) < COALESCE(namespaces.shared_runners_minutes_limit, application_settings.shared_runners_minutes, 0) * 60) as has_minutes,
                COUNT(*) AS count
              FROM ci_builds
              JOIN projects
                ON projects.id = ci_builds.project_id
              JOIN namespaces
                ON namespaces.id = projects.namespace_id
              LEFT JOIN namespace_statistics
                ON namespace_statistics.namespace_id = namespaces.id
              JOIN application_settings
                ON application_settings.id = 1
              WHERE ci_builds.type = 'Ci::Build'
                AND ci_builds.status IN ('running', 'pending')
178 179
                -- The created_at filter has been introduced for performance reasons only
                AND ci_builds.created_at > NOW() - INTERVAL '7 days'
180 181 182 183 184 185 186 187 188 189 190 191
              GROUP BY
                projects.namespace_id,
                projects.shared_runners_enabled,
                ci_builds.project_id,
                ci_builds.commit_id,
                ci_builds.status,
                ci_builds.commands,
                namespaces.shared_runners_minutes_limit,
                namespace_statistics.shared_runners_seconds,
                application_settings.shared_runners_minutes
              HAVING COUNT(*) > %d
            ) AS subquery
192
            GROUP BY
193 194 195 196 197 198
              subquery.namespace_id,
              subquery.shared_runners_enabled,
              subquery.project_id,
              subquery.commit_id,
              subquery.status,
              subquery.has_minutes
199 200 201 202 203
          SQL

        REPEATED_COMMANDS_QUERY_CE =
          <<~SQL.freeze
            SELECT
204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232
              subquery.namespace_id,
              subquery.shared_runners_enabled,
              subquery.project_id,
              subquery.status,
              MAX(subquery.count) as count
            FROM (
              SELECT
                projects.namespace_id,
                projects.shared_runners_enabled,
                ci_builds.project_id,
                ci_builds.commit_id,
                ci_builds.status,
                COUNT(*) AS count
              FROM ci_builds
              JOIN projects
                ON projects.id = ci_builds.project_id
              JOIN namespaces
                ON namespaces.id = projects.namespace_id
              WHERE ci_builds.type = 'Ci::Build'
                AND ci_builds.status IN ('running', 'pending')
              GROUP BY
                projects.namespace_id,
                projects.shared_runners_enabled,
                ci_builds.project_id,
                ci_builds.commit_id,
                ci_builds.status,
                ci_builds.commands
              HAVING COUNT(*) > %d
            ) AS subquery
233
            GROUP BY
234 235 236 237 238
              subquery.namespace_id,
              subquery.shared_runners_enabled,
              subquery.project_id,
              subquery.commit_id,
              subquery.status
239 240
          SQL

241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256
        UNARCHIVED_TRACES_QUERY =
          <<~SQL.freeze
            SELECT
              COUNT(*) as count
            FROM ci_builds
            JOIN ci_build_trace_chunks
              ON ci_build_trace_chunks.build_id = ci_builds.id
            LEFT JOIN ci_job_artifacts
              ON ci_job_artifacts.job_id = ci_builds.id
              AND ci_job_artifacts.file_type = 3
            WHERE ci_builds.type = 'Ci::Build'
              AND ci_builds.status IN ('success', 'failed', 'canceled')
              AND ci_builds.finished_at < '%s'
              AND ci_job_artifacts.job_id IS NULL
          SQL

257 258 259
        STATUS_CREATED = "created".freeze
        STATUS_PENDING = "pending".freeze

260 261
        DEFAULT_UNARCHIVED_TRACES_OFFSET_MINUTES = 1440

262 263 264 265
        def initialize(opts)
          super(opts)

          @allowed_repeated_commands_count = opts[:allowed_repeated_commands_count]
266
          @created_builds_counting_disabled = opts[:created_builds_counting_disabled]
267
          @unarchived_traces_offset_minutes = opts[:unarchived_traces_offset_minutes]
268 269
        end

270 271
        def run
          results = {}
272
          results[:created_builds] = builds(STATUS_CREATED) unless @created_builds_counting_disabled
273
          results[:pending_builds] = builds(STATUS_PENDING)
274 275
          results[:stale_builds] = stale_builds
          results[:per_runner] = per_runner_builds
276
          results[:repeated_commands] = repeated_commands
277
          results[:unarchived_traces] = unarchived_traces
278 279 280 281 282
          results
        end

        private

283 284
        def builds(status)
          results = []
Tomasz Maczukin's avatar
Tomasz Maczukin committed
285

286
          query = mirror_column? ? BUILDS_QUERY_EE : BUILDS_QUERY_CE
287
          query = query % [status] # rubocop:disable Style/FormatString
288
          exec_query_with_custom_random_page_cost(query).each do |row|
289
            results << transform_builds_row_to_values(row)
Tomasz Maczukin's avatar
Tomasz Maczukin committed
290 291 292 293 294 295 296
          end

          results
        rescue PG::UndefinedTable, PG::UndefinedColumn
          results
        end

297 298 299 300 301 302 303 304
        def transform_builds_row_to_values(row)
          values = { namespace: row["namespace_id"].to_s,
                     shared_runners: row["shared_runners_enabled"] == "t" ? "yes" : "no",
                     value: row["count"].to_i }
          include_ee_fields(values, row)
        end

        def stale_builds
305
          with_connection_pool do |conn|
306 307
            conn.exec(STALE_BUILDS_QUERY)[0]["count"].to_i
          end
Tomasz Maczukin's avatar
Tomasz Maczukin committed
308 309 310 311
        rescue PG::UndefinedTable, PG::UndefinedColumn
          0
        end

312
        def per_runner_builds
Ben Kochie's avatar
Ben Kochie committed
313
          results = []
314

315 316 317
          query = mirror_column? ? PER_RUNNER_QUERY_EE : PER_RUNNER_QUERY_CE
          exec_query_with_custom_random_page_cost(query).each do |row|
            results << transform_per_runners_builds_row_to_values(row)
318 319 320
          end

          results
Ben Kochie's avatar
Ben Kochie committed
321 322
        rescue PG::UndefinedTable, PG::UndefinedColumn
          []
323 324
        end

325
        def transform_per_runners_builds_row_to_values(row)
Tomasz Maczukin's avatar
Tomasz Maczukin committed
326 327 328 329 330 331
          values = { runner: row["runner_id"].to_s,
                     shared_runner: row["is_shared"] == "t" ? "yes" : "no",
                     namespace: row["namespace_id"].to_s,
                     scheduled: row["pipeline_schedule_id"] ? "yes" : "no",
                     triggered: row["trigger_request_id"] ? "yes" : "no",
                     value: row["count"].to_i }
332
          include_ee_fields(values, row)
Tomasz Maczukin's avatar
Tomasz Maczukin committed
333 334
        end

335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362
        def repeated_commands
          results = []

          query = mirror_column? ? REPEATED_COMMANDS_QUERY_EE : REPEATED_COMMANDS_QUERY_CE
          query = query % [allowed_repeated_commands_count] # rubocop:disable Style/FormatString
          exec_query_with_custom_random_page_cost(query).each do |row|
            results << transform_repeated_commands_row_to_values(row)
          end

          results
        rescue PG::UndefinedTable, PG::UndefinedColumn
          []
        end

        def allowed_repeated_commands_count
          @allowed_repeated_commands_count ||= 2
        end

        def transform_repeated_commands_row_to_values(row)
          values = { namespace: row["namespace_id"].to_s,
                     project: row["project_id"].to_s,
                     shared_runners: row["shared_runners_enabled"] == "t" ? "yes" : "no",
                     status: row["status"].to_s,
                     value: row["count"].to_i }

          include_has_minutes_field(values, row)
        end

363 364 365 366 367 368 369 370 371 372 373 374 375 376 377
        def unarchived_traces
          time = Time.now - (unarchived_traces_offset_minutes * 60)
          query = UNARCHIVED_TRACES_QUERY % [time.strftime("%F %T")] # rubocop:disable Style/FormatString

          with_connection_pool do |conn|
            conn.exec(query)[0]["count"].to_i
          end
        rescue PG::UndefinedTable, PG::UndefinedColumn
          0
        end

        def unarchived_traces_offset_minutes
          @unarchived_traces_offset_minutes ||= DEFAULT_UNARCHIVED_TRACES_OFFSET_MINUTES
        end

378 379 380
        def include_ee_fields(values, row)
          values.merge!(include_bool_if_row_defined(row, :mirror))
          values.merge!(include_bool_if_row_defined(row, :mirror_trigger_builds))
381 382 383 384
          include_has_minutes_field(values, row)
        end

        def include_has_minutes_field(values, row)
385
          values.merge!(include_bool_if_row_defined(row, :has_minutes))
Tomasz Maczukin's avatar
Tomasz Maczukin committed
386
          values
Zeger-Jan van de Weg's avatar
Zeger-Jan van de Weg committed
387 388
        end

389 390 391 392 393 394
        def include_bool_if_row_defined(row, field)
          return {} unless row[field.to_s]
          { field => row[field.to_s] == "t" ? "yes" : "no" }
        end

        def exec_query_with_custom_random_page_cost(query)
395
          with_connection_pool do |conn|
396 397 398 399
            conn.transaction do |trans|
              trans.exec(SET_RANDOM_PAGE_COST)
              trans.exec(query)
            end
400 401 402
          end
        end

403 404 405
        def mirror_column?
          @mirror_column ||=
            begin
406
              with_connection_pool do |conn|
407 408
                conn.exec(MIRROR_COLUMN_QUERY)[0]["exists"] == "t"
              end
409 410 411 412
            rescue PG::UndefinedColumn
              false
            end
        end
413 414 415 416 417 418
      end

      # The prober which is called when gathering metrics
      class CiBuildsProber
        def initialize(opts, metrics: PrometheusMetrics.new)
          @metrics = metrics
419 420

          collector_opts = { connection_string: opts[:connection_string],
421
                             allowed_repeated_commands_count: opts[:allowed_repeated_commands_count],
422 423
                             created_builds_counting_disabled: opts[:created_builds_counting_disabled],
                             unarchived_traces_offset_minutes: opts[:unarchived_traces_offset_minutes] }
424
          @collector = CiBuildsCollector.new(collector_opts)
425 426 427
        end

        def probe_db
428
          @results = @collector.run
429

430
          ci_builds_metrics(@results[:created_builds], "ci_created_builds") if @results[:created_builds]
Ben Kochie's avatar
Ben Kochie committed
431 432
          ci_builds_metrics(@results[:pending_builds], "ci_pending_builds")
          ci_stale_builds_metrics
433
          metrics_per_runner
434
          repeated_commands_metrics
435
          unarchived_traces_metrics
436

437 438
          self
        rescue PG::ConnectionBad
439 440 441 442 443 444
          self
        end

        def write_to(target)
          target.write(@metrics.to_s)
        end
445 446 447

        private

Ben Kochie's avatar
Ben Kochie committed
448
        def ci_builds_metrics(results_list, metric_name)
Tomasz Maczukin's avatar
Tomasz Maczukin committed
449
          other_values = {}
Zeger-Jan van de Weg's avatar
Zeger-Jan van de Weg committed
450

Ben Kochie's avatar
Ben Kochie committed
451
          results_list.each do |metric|
Ben Kochie's avatar
Ben Kochie committed
452
            # If we have a low value, put the value into an "other" bucket.
Tomasz Maczukin's avatar
Tomasz Maczukin committed
453 454
            if metric[:value] < 10
              key = { shared_runners: metric[:shared_runners] }
455 456
              key[:has_minutes] = metric[:has_minutes] if metric[:has_minutes]

Tomasz Maczukin's avatar
Tomasz Maczukin committed
457 458
              other_values[key] ||= 0
              other_values[key] += metric[:value]
Ben Kochie's avatar
Ben Kochie committed
459
            else
Tomasz Maczukin's avatar
Tomasz Maczukin committed
460
              add_ci_created_pending_builds(metric_name, metric[:value], metric)
Ben Kochie's avatar
Ben Kochie committed
461 462
            end
          end
Zeger-Jan van de Weg's avatar
Zeger-Jan van de Weg committed
463

Ben Kochie's avatar
Ben Kochie committed
464
          # Add metrics for the "other" bucket.
Tomasz Maczukin's avatar
Tomasz Maczukin committed
465
          other_values.each { |key, value| add_ci_created_pending_builds(metric_name, value, key) }
Tomasz Maczukin's avatar
Tomasz Maczukin committed
466 467 468
        end

        def add_ci_created_pending_builds(metric_name, value, labels)
Tomasz Maczukin's avatar
Tomasz Maczukin committed
469
          add_metric_with_namespace_label(metric_name,
470
                                          [:namespace, :shared_runners, :has_minutes],
Tomasz Maczukin's avatar
Tomasz Maczukin committed
471 472
                                          value,
                                          labels)
Ben Kochie's avatar
Ben Kochie committed
473
        end
474

Ben Kochie's avatar
Ben Kochie committed
475
        def ci_stale_builds_metrics
Ben Kochie's avatar
Ben Kochie committed
476
          @metrics.add("ci_stale_builds", @results[:stale_builds])
477 478 479
        end

        def metrics_per_runner
Tomasz Maczukin's avatar
Tomasz Maczukin committed
480 481
          other_values = {}

Ben Kochie's avatar
Ben Kochie committed
482
          @results[:per_runner].each do |metric|
Tomasz Maczukin's avatar
Tomasz Maczukin committed
483 484
            # If we have a low value, put the value into an "other" bucket.
            if metric[:value] < 10
Tomasz Maczukin's avatar
Tomasz Maczukin committed
485 486 487 488
              key = { runner: metric[:runner], shared_runner: metric[:shared_runner],
                      scheduled: metric[:scheduled], triggered: metric[:triggered] }
              key[:mirror] = metric[:mirror] if metric[:mirror]
              key[:mirror_trigger_builds] = metric[:mirror_trigger_builds] if metric[:mirror_trigger_builds]
489
              key[:has_minutes] = metric[:has_minutes] if metric[:has_minutes]
Tomasz Maczukin's avatar
Tomasz Maczukin committed
490

Tomasz Maczukin's avatar
Tomasz Maczukin committed
491 492 493 494 495 496 497 498
              other_values[key] ||= 0
              other_values[key] += metric[:value]
            else
              add_ci_running_builds(metric[:value], metric)
            end
          end

          # Add metrics for the "other" bucket.
Tomasz Maczukin's avatar
Tomasz Maczukin committed
499
          other_values.each { |key, value| add_ci_running_builds(value, key) }
500
        end
Tomasz Maczukin's avatar
Tomasz Maczukin committed
501 502

        def add_ci_running_builds(value, labels)
Tomasz Maczukin's avatar
Tomasz Maczukin committed
503 504
          add_metric_with_namespace_label(
            "ci_running_builds",
505 506
            [:runner, :namespace, :shared_runner, :scheduled,
             :triggered, :mirror, :mirror_trigger_builds, :has_minutes],
Tomasz Maczukin's avatar
Tomasz Maczukin committed
507 508 509
            value,
            labels
          )
Tomasz Maczukin's avatar
Tomasz Maczukin committed
510 511 512 513
        end

        def add_metric_with_namespace_label(metric_name, allowed_labels, value, labels)
          labels[:namespace] = "" unless labels[:namespace]
Tomasz Maczukin's avatar
Tomasz Maczukin committed
514 515 516

          selected_labels = labels.select { |k, _| allowed_labels.include?(k) }.sort.to_h
          @metrics.add(metric_name, value, selected_labels)
Tomasz Maczukin's avatar
Tomasz Maczukin committed
517
        end
518 519 520 521 522 523 524 525

        def repeated_commands_metrics
          @results[:repeated_commands].each do |metric|
            value = metric.delete(:value)

            @metrics.add("ci_repeated_commands_builds", value, metric)
          end
        end
526 527 528 529

        def unarchived_traces_metrics
          @metrics.add("ci_unarchived_traces", @results[:unarchived_traces])
        end
530 531 532 533
      end
    end
  end
end