Learn how our engineering team cut through noisy CI crash reports with a trace similarity system built on ClickHouse.

large_trace-blog-banner.jpg

medium_trace-blog-banner.jpg

small_trace-blog-banner.jpg

thumbnail_trace-blog-banner.jpg

trace-blog-banner.jpg

Trace similarity systems on top of ClickHouse to analyze crash stack traces from our CI

In our CI/CD system at ClickHouse we use [lambda functions](https://docs.aws.amazon.com/lambda/) a lot. AWS provides two ways to deploy a lambda function: Docker images and ZIP archives.

The latter is much lighter, so we have used it exclusively since [November 2022](https://github.com/ClickHouse/ClickHouse/pull/43769). Besides the artifact size, it was easier to automate it in a simple [script](https://github.com/ClickHouse/ClickHouse/blob/1443e490ea5287175f031eaee567fe5024cd7306/tests/ci/team_keys_lambda/build_and_deploy_archive.sh).

In late 2023, we started using Terraform/OpenTofu to manage the CI/CD infrastructure. This raised the question of "how should we manage artifacts to deploy?"

The question contains two different points:

- The artifact should not change the content after each rebuild; otherwise, it would be redeployed on each execution of the `tofu apply` command.

- Ideally, it should be built on the fly, without any additional infrastructure on top, like pushing it to S3 or so

## Building metadata-independent ZIP archive on linux

![zip-archive-blog-1.png](/uploads/zip_archive_blog_1_7a0fb214e9.png)

### Challenge one: the order matters

There are many factors affecting the ZIP archive content. One of the unexpected (for me) things is the order of archived files. Here's the example:

```
# create files 1 2 3 4 5
$ touch {1..5}

# archive them in sorted and reserved order
$ ls [1-5] | sort    | zip -q -0 --names-stdin sorted.zip
$ ls [1-5] | sort -r | zip -q -0 --names-stdin reversed.zip

# compare two files byte-by-byte
$ cmp -l sort.zip rev.zip
 31  61  65
 62  62  64
124  64  62
155  65  61
202  61  65
249  62  64
343  64  62
390  65  61
```

And here's the very first issue to solve. The order of files must be the same.

Besides that, we should omit passing the directories to the zip command. Otherwise, it could cause inconsistency. The argument `-D` helps here.

So, here's the very first solved issue.

```
export LC_ALL=c
# zip uses random files order by default, so we sort the files alphabetically
find . ! -type d -print0 | sort -z | tr '\0' '\n' | zip -XD -0 ../"$PACKAGE".zip --names-stdin
```

The found files are null-divided and sorted, and then null is replaced by a new line and passed to the ZIP.

### Challenge two: age matters

This is the part of the man zip about one of the arguments above:

```
   -X
   --no-extra
           Do not save extra file attributes (Extended Attributes on OS/2,
           uid/gid and file times on Unix).  The  zip  format  uses  extra
           fields  to include additional information for each entry.  Some
           extra fields are specific to particular  systems  while  others
           are applicable to all systems.  Normally when zip reads entries
           from  an  existing archive, it reads the extra fields it knows,
           strips the rest, and adds the extra fields applicable  to  that
           system.   With  -X, zip strips all old fields and only includes
           the Unicode and Zip64 extra fields (currently these  two  extra
           fields cannot be disabled).
```

So, it should ignore timestamps.. But!

```
$ touch -t 201212121212 {1..5}
$ zip -XD0q older.zip {1..5}
$ touch -t 201212121213 {1..5}
$ zip -XD0q newer.zip {1..5}
$ cmp -l older.zip newer.zip
 11 200 240
 42 200 240
 73 200 240
104 200 240
135 200 240
168 200 240
215 200 240
262 200 240
309 200 240
356 200 240
```

Somebody lies. So, before archiving the files, we need to change the last modification time.

`find "$PACKAGE" ! -type d -exec touch -t 201212121212 {} +`

### Challenge three: Python byte-code

According to the [recommendations](https://docs.aws.amazon.com/lambda/latest/dg/python-package.html#python-package-pycache), including `.pyc` files in the lambda archive speeds-up the cold start of the lambdas, but can cause issues with the byte-code version. The byte-code version depends on the Python version and the system architecture.

After some experiments, we found that the best way to skip the `.pyc` files is to use the `-x,--exclude` option of the zip command.

### Final challenge: the OS matters

The last challenge is the OS. The ZIP archive format depends on the OS on which it was created. MacOS and Linux produce different ZIP archives, even if the content is the same. At this point we were already too tired of finding out what was going on. It was clear that any difference in `zip` binary could cause a bit-difference in the archives. 

The best way we came up with is using Python to create the ZIP archive. Python's `zipfile` module creates a consistent ZIP archive, regardless of the OS. But to be completely sure, we use Python from the `public.ecr.aws/lambda/python` image, which is the same as the one used by AWS Lambda.

```
docker_cmd=(
  docker run -i --net=host --rm --user="${UID}" -e HOME=/tmp --entrypoint=/bin/bash
  --volume="${WORKDIR}/..:/ci" --workdir="/ci/${DIR_NAME}" "${DOCKER_IMAGE}"
)

"${docker_cmd[@]}" -ex <<EOF
cd '$PACKAGE'
find ! -type d -exec touch -t 201212121212 {} +

python <<'EOP'
import zipfile
import os
files_path = []
for root, _, files in os.walk('.'):
    files_path.extend(os.path.join(root, file) for file in files)
# persistent file order
files_path.sort()
with zipfile.ZipFile('../$PACKAGE.zip', 'w') as zf:
    for file in files_path:
        zf.write(file)
EOP
EOF
```

## Building the virtual environment for the lambda

The code to address this challenge is quite simple. The only thing to remember is that the virtual environment should be created with the same Python version as the one used by AWS Lambda.

```
docker_cmd=(
  docker run -i --net=host --rm --user="${UID}" -e HOME=/tmp --entrypoint=/bin/bash
  --volume="${WORKDIR}/..:/ci" --workdir="/ci/${DIR_NAME}" "${DOCKER_IMAGE}"
)
rm -rf "$PACKAGE" "$PACKAGE".zip
mkdir "$PACKAGE"
cp app.py "$PACKAGE"
if [ -f requirements.txt ]; then
  VENV=lambda-venv
  rm -rf "$VENV"
  "${docker_cmd[@]}" -ex <<EOF
    '$PY_EXEC' -m venv '$VENV' &&
    source '$VENV/bin/activate' &&
    pip install -r requirements.txt &&
    # To have consistent pyc files
    find '$VENV/lib' -name '*.pyc' -delete
    cp -rT '$VENV/lib/$PY_EXEC/site-packages/' '$PACKAGE'
    rm -r '$PACKAGE'/{pip,pip-*,setuptools,setuptools-*}
    chmod 0777 -R '$PACKAGE'
EOF
fi
```

Here we check if the lambda function has a `requirements.txt` file. If it does, we create a virtual environment with the same Python version as the one used by AWS Lambda, install the dependencies, and copy the site-packages to the lambda package directory. To remove the unnecessary files, we delete the `pip` and `setuptools` directories as well as Python byte-code files.

## Conclusion

The full script doing the job is available [here](https://github.com/ClickHouse/ClickHouse/blob/1443e490ea5287175f031eaee567fe5024cd7306/tests/ci/team_keys_lambda/build_and_deploy_archive.sh). Later it was moved into the private repository, but it is still used in Terraform/OpenTofu configuration to deploy the lambda functions.


Lessons from making reproducible ZIPs for Python Lambdas in CI/CD with Terraform and OpenTofu.

large_zip-archive-blog-banner.png

medium_zip-archive-blog-banner.png

small_zip-archive-blog-banner.png

thumbnail_zip-archive-blog-banner.png

zip-archive-blog-banner.png

How to create reproducible ZIPs for AWS Lambda

In a [previous post](https://clickhouse.com/blog/monitor-github-action-workflow-job-queues) I introduced how you can build a table in ClickHouse containing all of the data from your GitHub Actions using GitHub Webhooks. We do this for the ClickHouse organization, to provide essential metrics on the internal queue of workflow jobs.

Just having this and doing nothing with it would be a huge waste of gemstone data, so a couple of months ago the data was used to implement a lambda to quickly inflate and deflate our workers on demand. This has improved the responsiveness of our job launching, reacting to an increase in scheduled tests, as well as saving us compute when the workload is lower. Let's discuss how we achieved this!

## The Idea 

We have all events in our database for when a workflow job was created, started or finished. Consider this query from the previous post, which returns the current ClickHouse queue size for self-managed GitHub Runners:

<pre style='background-color: #222222; border-radius: 8px; padding: 24px; color: #FFFFFF; display: flex; position: relative; white-space: pre-wrap;'>
<div style='color: #FFFFFF; margin-top: -24px; max-width: 98%;'>
SELECT
    last_status,
    count() AS queue,
    labels
FROM
(
    SELECT
        argMax(status, updated_at) AS last_status,
        labels,
        id,
        html_url
    FROM default.workflow_jobs
    WHERE has(labels, 'self-hosted') AND (started_at > (now() - toIntervalHour(3)))
    GROUP BY ALL
    HAVING last_status != 'completed'
)
GROUP BY ALL
ORDER BY
    labels ASC,
    last_status ASC
</div> 
</pre>
</p>

![query-result.png](/uploads/query_result_1ebbf3568f.png)

Knowing this allows us proactively to scale-up the runners to cover the demand, or scale-down to free unnecessary resources.

The new desired capacity is calculated based on the current number of runners and jobs in progress and queue. If there is a deficit, we increase the capacity proportional to it (usually, deficit / 5). And for the unnecessary reserve, we quickly deflate the group (reserve / 2).

## An old system

The officially recommended approach in AWS to trigger scaling up/down is assigning CloudWatch alarms to Auto-Scaling Groups (ASG.) So, whenever an alarm is in an alarm state, the ASG adds or removes some instances.

We used to monitor how many instances of each ASG were in a busy state using the GitHub runners API. If there were more than 97% busy runners for 5 minutes, another one runner was added. For scaling out the rule was less than 70%.

The system used to be very reactive and inert. To warm up groups with 50+ runners it required a few hours.

## Improvements and statistics

After switching to our new system, we both achieved faster jobs launching and a saving of compute time after the job was done. You can see how it affected the runners on the graphs:

The old system:

<a href="/uploads/old_system_8e1bd87afb.png" target="_blank"><img src="/uploads/old_system_8e1bd87afb.png"/></a>

The new system:

<a href="/uploads/new_system_232c534dda.png" target="_blank"><img src="/uploads/new_system_232c534dda.png"/></a>

We can see how the Busy and Active Runners are more tightly correlated in the new system, indicating that provisioned workers are more heavily utilized. The code used here is all available in [our repository](https://github.com/ClickHouse/ClickHouse/blob/2ab313e6b4272f5888bc7d3b54533b3f8ba86fba/tests/ci/autoscale_runners_lambda/app.py). Hopefully others can make similar improvements and savings!


Learn how we utilize ClickHouse and AWS Lambda to improve our Github workflow job queue scheduling

large_github workflow.png

medium_github workflow.png

small_github workflow.png

thumbnail_github workflow.png

github workflow.png

Building an Auto-Scaling Lambda based on Github's Workflow Job Queue

Hello and Happy New Year from the ClickHouse development team! Anyone who uses GitHub Actions intensively faces the lack of some metrics at some point.  One of the essential metrics is the internal queue of workflow jobs. How many are currently running? How many are in the queue? How many of them are already finished?

Luckily, there is a way to get the info and it involves ClickHouse!

In this post, we will show how you can collect all of the data from your GitHub Actions using GitHub Webhooks and store this data in ClickHouse for analysis.

## Necessary tools

### GitHub Webhooks

We need the status for each existing workflow job. To achieve this, we'll use GitHub webhooks. It can be a repository or organizational-scoped webhook.

An organization-level webhook works much better because we receive information about all repositories. It allows us to have a full picture.

We need to receive [workflow_job](https://docs.github.com/en/developers/webhooks-and-events/webhooks/webhook-events-and-payloads#workflow_job) events in JSON format:

![webhook.png](/uploads/webhook_3f771bf096.png)

An example of the hook's body with necessary objects:

```json
{
  "action": "completed",
  "workflow_job": {
    "id": 10232656529,
    "run_id": 3749960853,
    "workflow_name": "DocsCheck",
    "head_branch": "from-final",
    "run_url": "https://api.github.com/repos/ClickHouse/ClickHouse/actions/runs/3749960853",
    "run_attempt": 1,
    "node_id": "CR_kwDOA5dJV88AAAACYenykQ",
    "head_sha": "e791e7bbfef2a56cd7d22779046c24086140b325",
    "url": "https://api.github.com/repos/ClickHouse/ClickHouse/actions/jobs/10232656529",
    "html_url": "https://github.com/ClickHouse/ClickHouse/actions/runs/3749960853/jobs/6369064705",
    "status": "completed",
    "conclusion": "failure",
    "started_at": "2022-12-21T14:04:05Z",
    "completed_at": "2022-12-21T14:04:22Z",
    "name": "CheckLabels",
    "steps": [
      {..},
      ..
    ],
    "check_run_url": "https://api.github.com/repos/ClickHouse/ClickHouse/check-runs/10232656529",
    "labels": [
      "self-hosted",
      "style-checker"
    ],
    "runner_id": 14977597,
    "runner_name": "i-04aca26d696a1b457",
    "runner_group_id": 1,
    "runner_group_name": "Default"
  },
  "repository": {
    ..,
    "full_name": "ClickHouse/ClickHouse",
    "private": false,
    ..,
  },
  ..
}
```

### AWS lambda

The simplest way-to-go is a lambda in AWS that receives and inserts data into a storage layer, such as ClickHouse! The code was developed under [PR 44520](https://github.com/ClickHouse/ClickHouse/pull/44520) and is available in the [ClickHouse repository](https://github.com/ClickHouse/ClickHouse/blob/master/tests/ci/workflow_jobs_lambda/app.py) for reuse. It just reads the values from the event, optionally anonymizes sensitive information for private repositories, and inserts the data in our public playground ClickHouse instance.

If you need a ClickHouse cluster, a development instance in [ClickHouse Cloud](https://clickhouse.cloud/signUp) is more than enough for this use case. Spin up a cluster on a free trial in minutes, let us deal with the infrastructure and get querying!

### ClickHouse

Finally, in ClickHouse, you need to create a table to receive the events:

<pre class='code-with-play'>
<div class='code'>
CREATE TABLE default.workflow_jobs
(
    `id` UInt64,
    `run_id` UInt64,
    `workflow_name` LowCardinality(String),
    `head_branch` LowCardinality(String),
    `run_url` String,
    `run_attempt` UInt16,
    `node_id` String,
    `head_sha` String,
    `url` String,
    `html_url` String,
    `status` Enum8('queued' = 1, 'in_progress' = 2, 'completed' = 3, 'waiting' = 4),
    `conclusion` LowCardinality(String),
    `started_at` DateTime,
    `completed_at` DateTime,
    `name` LowCardinality(String),
    `steps` UInt16,
    `check_run_url` String,
    `labels` Array(LowCardinality(String)),
    `runner_id` UInt64,
    `runner_name` String,
    `runner_group_id` UInt64,
    `runner_group_name` LowCardinality(String),
    `repository` LowCardinality(String),
    `updated_at` DateTime DEFAULT now()
)
ENGINE = MergeTree
PARTITION BY toStartOfMonth(started_at)
ORDER BY (id, updated_at)
</div>
</pre>
</p>

### The result

After some events are received, getting as much info as we need is trivial. Suppose want to see the number of jobs queued or waiting over the last 10 days. If using ClickHouse Cloud you can even visualize the results of this query with SQL console:

<pre class='code-with-play'>
<div class='code'>
SELECT
    uniq(id) as num_jobs,
    toStartOfHour(started_at) as hour
FROM default.workflow_jobs
WHERE has(labels, 'self-hosted') AND status IN ('in_progress', 'waiting')
    AND started_at > now() - INTERVAL 10 DAY
GROUP BY hour
ORDER BY hour ASC
</div>
</pre>
</p>

<a href="/uploads/jobs_queued_or_waiting_744bce346a.png" target="_blank">
<img src="/uploads/jobs_queued_or_waiting_744bce346a.png" alt="jobs_queued_or_waiting.png">
</a>

A more interesting query might be to show the total time jobs spend in the queue by the time of day. Here we include the average, max and 90th percentile:

<pre class='code-with-play'>
<div class='code'>
SELECT max(time_spent) as max_time_spent, avg(time_spent) as avg_time_spent, quantile(0.9)(time_spent) as 90th_percentile, toStartOfHour(start_time) as hour FROM (
    SELECT
        max(workflow_jobs.completed_at) - min(workflow_jobs.started_at) as time_spent,
        argMax(status,updated_at) as last_event,
        id,
        min(started_at) as start_time
    FROM default.workflow_jobs
    WHERE has(labels, 'self-hosted')
        AND started_at > now() - INTERVAL 10 DAY
    GROUP BY id HAVING last_event = 'completed'
) GROUP BY hour ORDER BY hour ASC
</div>
</pre>
</p>

<a href="/uploads/time_in_queue_6571bb664d.png" target="_blank">
<img src="/uploads/time_in_queue_6571bb664d.png" alt="time_in_queue.png">
</a>

Finally, with a little more complexity we can see the current ClickHouse queue size for self-managed GitHub Runners:

<pre class='code-with-play'>
<div class='code'>
SELECT
    last_status,
    count() AS queue,
    labels
FROM
(
    SELECT
        argMax(status, updated_at) AS last_status,
        labels,
        id,
        html_url
    FROM default.workflow_jobs
    WHERE has(labels, 'self-hosted') AND (started_at > (now() - toIntervalDay(2)))
    GROUP BY ALL
    HAVING last_status != 'completed'
)
GROUP BY ALL
ORDER BY
    labels ASC,
    last_status ASC
</div>
</pre>
</p>

The result will look like the following:

![play.png](/uploads/play_ea08eee183.png)

## Summary

In this post, we've shown how you can use ClickHouse and GitHub Webhooks to monitor the queues for the jobs of your GitHub Actions.

Misha Shiryaev

Blog posts by Misha Shiryaev

Trace similarity systems on top of ClickHouse to analyze crash stack traces from our CI

How to create reproducible ZIPs for AWS Lambda

Building an Auto-Scaling Lambda based on Github's Workflow Job Queue

Using ClickHouse to Monitor Job Queues in GitHub Actions