ci_builds.rb 19.2 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 30 31 32 33 34 35 36 37 38
              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
39 40 41 42 43 44
          <<~SQL.freeze
            SELECT
              projects.namespace_id,
              ci_builds.status,
              projects.shared_runners_enabled,
              COUNT(*) AS count
45 46 47 48
            FROM ci_builds
            JOIN projects
              ON projects.id = ci_builds.project_id
            WHERE ci_builds.type = 'Ci::Build'
49
              AND ci_builds.status = '%s'
50 51 52 53 54
              AND projects.pending_delete = 'f'
            GROUP BY
              projects.namespace_id,
              ci_builds.status,
              projects.shared_runners_enabled
Tomasz Maczukin's avatar
Tomasz Maczukin committed
55
          SQL
56

Ben Kochie's avatar
Ben Kochie committed
57
        STALE_BUILDS_QUERY =
Tomasz Maczukin's avatar
Tomasz Maczukin committed
58 59 60
          <<~SQL.freeze
            SELECT
              COUNT(*) AS count
61 62 63 64 65 66 67
            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
68
          SQL
69

70
        PER_RUNNER_QUERY_EE =
Zeger-Jan van de Weg's avatar
Zeger-Jan van de Weg committed
71 72 73 74
          <<~SQL.freeze
            SELECT
              ci_builds.runner_id,
              ci_runners.is_shared,
Tomasz Maczukin's avatar
Tomasz Maczukin committed
75
              projects.namespace_id,
76 77 78 79
              projects.mirror,
              projects.mirror_trigger_builds,
              ci_pipelines.pipeline_schedule_id,
              ci_builds.trigger_request_id,
80 81
              (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,
82 83 84 85 86 87 88 89
              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
90 91 92 93 94 95
            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
96 97
            WHERE ci_builds.type = 'Ci::Build'
              AND ci_builds.status = 'running'
Tomasz Maczukin's avatar
Tomasz Maczukin committed
98
              AND projects.pending_delete = 'f'
99 100 101
            GROUP BY
              ci_builds.runner_id,
              ci_runners.is_shared,
102 103 104
              projects.namespace_id,
              projects.mirror,
              projects.mirror_trigger_builds,
105
              ci_pipelines.pipeline_schedule_id,
Zeger-Jan van de Weg's avatar
Zeger-Jan van de Weg committed
106
              ci_builds.trigger_request_id,
107 108 109
              namespaces.shared_runners_minutes_limit,
              namespace_statistics.shared_runners_seconds,
              application_settings.shared_runners_minutes
110 111 112 113 114 115 116
          SQL

        PER_RUNNER_QUERY_CE =
          <<~SQL.freeze
            SELECT
              ci_builds.runner_id,
              ci_runners.is_shared,
Tomasz Maczukin's avatar
Tomasz Maczukin committed
117
              projects.namespace_id,
Zeger-Jan van de Weg's avatar
Zeger-Jan van de Weg committed
118 119 120 121 122 123 124 125 126 127 128 129
              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
130
              AND projects.pending_delete = 'f'
Zeger-Jan van de Weg's avatar
Zeger-Jan van de Weg committed
131 132 133
            GROUP BY
              ci_builds.runner_id,
              ci_runners.is_shared,
Tomasz Maczukin's avatar
Tomasz Maczukin committed
134
              projects.namespace_id,
Zeger-Jan van de Weg's avatar
Zeger-Jan van de Weg committed
135 136 137
              ci_pipelines.pipeline_schedule_id,
              ci_builds.trigger_request_id
          SQL
138

Tomasz Maczukin's avatar
Tomasz Maczukin committed
139 140 141
        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
142
          SQL
Tomasz Maczukin's avatar
Tomasz Maczukin committed
143

144 145 146
        REPEATED_COMMANDS_QUERY_EE =
          <<~SQL.freeze
            SELECT
147 148 149 150 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 178 179 180 181 182 183 184 185
              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')
              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
186
            GROUP BY
187 188 189 190 191 192
              subquery.namespace_id,
              subquery.shared_runners_enabled,
              subquery.project_id,
              subquery.commit_id,
              subquery.status,
              subquery.has_minutes
193 194 195 196 197
          SQL

        REPEATED_COMMANDS_QUERY_CE =
          <<~SQL.freeze
            SELECT
198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226
              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
227
            GROUP BY
228 229 230 231 232
              subquery.namespace_id,
              subquery.shared_runners_enabled,
              subquery.project_id,
              subquery.commit_id,
              subquery.status
233 234
          SQL

235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250
        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

251 252 253
        STATUS_CREATED = "created".freeze
        STATUS_PENDING = "pending".freeze

254 255
        DEFAULT_UNARCHIVED_TRACES_OFFSET_MINUTES = 1440

256 257 258 259
        def initialize(opts)
          super(opts)

          @allowed_repeated_commands_count = opts[:allowed_repeated_commands_count]
260
          @created_builds_counting_disabled = opts[:created_builds_counting_disabled]
261
          @unarchived_traces_offset_minutes = opts[:unarchived_traces_offset_minutes]
262 263
        end

264 265
        def run
          results = {}
266
          results[:created_builds] = builds(STATUS_CREATED) unless @created_builds_counting_disabled
267
          results[:pending_builds] = builds(STATUS_PENDING)
268 269
          results[:stale_builds] = stale_builds
          results[:per_runner] = per_runner_builds
270
          results[:repeated_commands] = repeated_commands
271
          results[:unarchived_traces] = unarchived_traces
272 273 274 275 276
          results
        end

        private

277 278
        def builds(status)
          results = []
Tomasz Maczukin's avatar
Tomasz Maczukin committed
279

280
          query = mirror_column? ? BUILDS_QUERY_EE : BUILDS_QUERY_CE
281
          query = query % [status] # rubocop:disable Style/FormatString
282
          exec_query_with_custom_random_page_cost(query).each do |row|
283
            results << transform_builds_row_to_values(row)
Tomasz Maczukin's avatar
Tomasz Maczukin committed
284 285 286 287 288 289 290
          end

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

291 292 293 294 295 296 297 298
        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
299
          with_connection_pool do |conn|
300 301
            conn.exec(STALE_BUILDS_QUERY)[0]["count"].to_i
          end
Tomasz Maczukin's avatar
Tomasz Maczukin committed
302 303 304 305
        rescue PG::UndefinedTable, PG::UndefinedColumn
          0
        end

306
        def per_runner_builds
Ben Kochie's avatar
Ben Kochie committed
307
          results = []
308

309 310 311
          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)
312 313 314
          end

          results
Ben Kochie's avatar
Ben Kochie committed
315 316
        rescue PG::UndefinedTable, PG::UndefinedColumn
          []
317 318
        end

319
        def transform_per_runners_builds_row_to_values(row)
Tomasz Maczukin's avatar
Tomasz Maczukin committed
320 321 322 323 324 325
          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 }
326
          include_ee_fields(values, row)
Tomasz Maczukin's avatar
Tomasz Maczukin committed
327 328
        end

329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356
        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

357 358 359 360 361 362 363 364 365 366 367 368 369 370 371
        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

372 373 374
        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))
375 376 377 378
          include_has_minutes_field(values, row)
        end

        def include_has_minutes_field(values, row)
379
          values.merge!(include_bool_if_row_defined(row, :has_minutes))
Tomasz Maczukin's avatar
Tomasz Maczukin committed
380
          values
Zeger-Jan van de Weg's avatar
Zeger-Jan van de Weg committed
381 382
        end

383 384 385 386 387 388
        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)
389
          with_connection_pool do |conn|
390 391 392 393
            conn.transaction do |trans|
              trans.exec(SET_RANDOM_PAGE_COST)
              trans.exec(query)
            end
394 395 396
          end
        end

397 398 399
        def mirror_column?
          @mirror_column ||=
            begin
400
              with_connection_pool do |conn|
401 402
                conn.exec(MIRROR_COLUMN_QUERY)[0]["exists"] == "t"
              end
403 404 405 406
            rescue PG::UndefinedColumn
              false
            end
        end
407 408 409 410 411 412
      end

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

          collector_opts = { connection_string: opts[:connection_string],
415
                             allowed_repeated_commands_count: opts[:allowed_repeated_commands_count],
416 417
                             created_builds_counting_disabled: opts[:created_builds_counting_disabled],
                             unarchived_traces_offset_minutes: opts[:unarchived_traces_offset_minutes] }
418
          @collector = CiBuildsCollector.new(collector_opts)
419 420 421
        end

        def probe_db
422
          @results = @collector.run
423

424
          ci_builds_metrics(@results[:created_builds], "ci_created_builds") if @results[:created_builds]
Ben Kochie's avatar
Ben Kochie committed
425 426
          ci_builds_metrics(@results[:pending_builds], "ci_pending_builds")
          ci_stale_builds_metrics
427
          metrics_per_runner
428
          repeated_commands_metrics
429
          unarchived_traces_metrics
430

431 432
          self
        rescue PG::ConnectionBad
433 434 435 436 437 438
          self
        end

        def write_to(target)
          target.write(@metrics.to_s)
        end
439 440 441

        private

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

Ben Kochie's avatar
Ben Kochie committed
445
          results_list.each do |metric|
Ben Kochie's avatar
Ben Kochie committed
446
            # If we have a low value, put the value into an "other" bucket.
Tomasz Maczukin's avatar
Tomasz Maczukin committed
447 448
            if metric[:value] < 10
              key = { shared_runners: metric[:shared_runners] }
449 450
              key[:has_minutes] = metric[:has_minutes] if metric[:has_minutes]

Tomasz Maczukin's avatar
Tomasz Maczukin committed
451 452
              other_values[key] ||= 0
              other_values[key] += metric[:value]
Ben Kochie's avatar
Ben Kochie committed
453
            else
Tomasz Maczukin's avatar
Tomasz Maczukin committed
454
              add_ci_created_pending_builds(metric_name, metric[:value], metric)
Ben Kochie's avatar
Ben Kochie committed
455 456
            end
          end
Zeger-Jan van de Weg's avatar
Zeger-Jan van de Weg committed
457

Ben Kochie's avatar
Ben Kochie committed
458
          # Add metrics for the "other" bucket.
Tomasz Maczukin's avatar
Tomasz Maczukin committed
459
          other_values.each { |key, value| add_ci_created_pending_builds(metric_name, value, key) }
Tomasz Maczukin's avatar
Tomasz Maczukin committed
460 461 462
        end

        def add_ci_created_pending_builds(metric_name, value, labels)
Tomasz Maczukin's avatar
Tomasz Maczukin committed
463
          add_metric_with_namespace_label(metric_name,
464
                                          [:namespace, :shared_runners, :has_minutes],
Tomasz Maczukin's avatar
Tomasz Maczukin committed
465 466
                                          value,
                                          labels)
Ben Kochie's avatar
Ben Kochie committed
467
        end
468

Ben Kochie's avatar
Ben Kochie committed
469
        def ci_stale_builds_metrics
Ben Kochie's avatar
Ben Kochie committed
470
          @metrics.add("ci_stale_builds", @results[:stale_builds])
471 472 473
        end

        def metrics_per_runner
Tomasz Maczukin's avatar
Tomasz Maczukin committed
474 475
          other_values = {}

Ben Kochie's avatar
Ben Kochie committed
476
          @results[:per_runner].each do |metric|
Tomasz Maczukin's avatar
Tomasz Maczukin committed
477 478
            # If we have a low value, put the value into an "other" bucket.
            if metric[:value] < 10
Tomasz Maczukin's avatar
Tomasz Maczukin committed
479 480 481 482
              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]
483
              key[:has_minutes] = metric[:has_minutes] if metric[:has_minutes]
Tomasz Maczukin's avatar
Tomasz Maczukin committed
484

Tomasz Maczukin's avatar
Tomasz Maczukin committed
485 486 487 488 489 490 491 492
              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
493
          other_values.each { |key, value| add_ci_running_builds(value, key) }
494
        end
Tomasz Maczukin's avatar
Tomasz Maczukin committed
495 496

        def add_ci_running_builds(value, labels)
Tomasz Maczukin's avatar
Tomasz Maczukin committed
497 498
          add_metric_with_namespace_label(
            "ci_running_builds",
499 500
            [:runner, :namespace, :shared_runner, :scheduled,
             :triggered, :mirror, :mirror_trigger_builds, :has_minutes],
Tomasz Maczukin's avatar
Tomasz Maczukin committed
501 502 503
            value,
            labels
          )
Tomasz Maczukin's avatar
Tomasz Maczukin committed
504 505 506 507
        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
508 509 510

          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
511
        end
512 513 514 515 516 517 518 519

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

            @metrics.add("ci_repeated_commands_builds", value, metric)
          end
        end
520 521 522 523

        def unarchived_traces_metrics
          @metrics.add("ci_unarchived_traces", @results[:unarchived_traces])
        end
524 525 526 527
      end
    end
  end
end