# PXC-Operator

## Overview

The source code and default configuration of the Building Block is available in our [code.syseleven.de](<https://code.syseleven.de/syseleven/building-blocks/helmfiles/pxc-operator>).

### PXC Operator

The PXC Operator (Percona XtraDB Cluster) is an open source enterprise MySQL solution.

Infos on release notes and new features please follow [Release notes pxc-operator](<https://code.syseleven.de/syseleven/building-blocks/helmfiles/pxc-operator/-/blob/main/RELEASE-NOTES.md>)

This building block will deploy a Percona XtraDB cluster with HAProxy as a load balancing frontend.

Using the supplied configuration possibilities, you can configure **all settings for the cluster itself** as well as **manage databases and users** inside the cluster.

> [!NOTE]
>
> **Info**
>
> In version 1.1.0 of this building block we changed that the volumes of the database will be retained even if the `perconaxtradbclusters.pxc.percona.com` resource is deleted

### Prerequisites On PXC-Operator

Proceed with the following prerequisite description to use the Building Block out of the box.

#### Recommended Setup

A recommended resource overview is listed in the table below.

| CPU / vCPU | Memory |
| --- | --- |
| 6.7 | 9728MiB |

No further activities need to be carried out in advance.

## Adding The Building Block

Add the directory `syseleven-pxc-operator` to your control repository. Add a `.gitlab-ci.yml` to the directory with the following content:

```yaml
include:
  - project: syseleven/building-blocks/helmfiles/pxc-operator
    file: JobDevelopment.yaml
    ref: { { site.building_blocks.pxc_operator.version|e } }
  - project: syseleven/building-blocks/helmfiles/pxc-operator
    file: JobStaging.yaml
    ref: { { site.building_blocks.pxc_operator.version|e } }
  - project: syseleven/building-blocks/helmfiles/pxc-operator
    file: JobProduction.yaml
    ref: { { site.building_blocks.pxc_operator.version|e } }
```

Remove environments you are not using by removing their include.

### Configuring The Building Block

The building block consists of multiple helm charts that can be configure individually. Look at the [helmfile.yaml](<https://code.syseleven.de/syseleven/building-blocks/helmfiles/pxc-operator/-/blob/main/helmfile.yaml>) in the `releases.[].values` section. The configuration of each helm chart can be overriden in those files in the specified hierarchy.

There are **some** default values that the building block will set for the upstream helm chart.

### Required Configuration

Set the following sections in one of those files:

- `values-pxc-db.yaml`
- `values-pxc-db-$ENVIRONMENT.yaml`

### Configuring Backups

You have to set

```yaml
backup:
  storages:
    s3:
      s3:
        credentialsAccessKey: ...
        credentialsSecretKey: ...
```

If you can't, please disable the backup schedule with

```yaml
backup:
  schedule: []
```

### Credentials

You have to set the following variables otherwise they will be changed on every run.

```yaml
secrets:
  passwords:
    root: ...
    xtrabackup: ...
    monitor: ...
    clustercheck: ...
    proxyadmin: ...
    pmmserver: ...
    operator: ...
    replication: ...
```

### Users And Database Management (Optional)

Specify your values in the following helm values files:

- `values-mysql-config.yaml`
- `values-mysql-config-${ENVIRONMENT}.yaml`

### Parameters

The following table lists the configurable parameters **for the management** of the mysql-config chart and their default values.

| Parameter | Description | Default |
| :---: | :---: | :---: |
| `schedule` | The cron schedule for the job that creates the users and databases | `*/15 * * * *` |
| `target.host` | The MySQL server on which to manage databases and users | `pxc-db-haproxy` |
| `target.user` | The user with which users and databases are created | `root` |
| `target.existingSecret` | The password for `target.user` | `"pxc-db"` |
| `target.existingSecretKey` | The key in `target.existingSecret` where the password for `target.user` is stored | `"root"` |
| `databases` | The databases to manage. See [configuration](<#configuration>) for details | `[]` |
| `users` | The users to manage. See [configuration](<#configuration>) for details | `[]` |

Default parameters like for example the `image` and `tolerations` are available, too. See the [values.yaml](<https://code.syseleven.de/syseleven/building-blocks/helmfiles/pxc-operator/-/blob/main/mysql-config/values.yaml>) file for those parameters.

### Configuration

The user and database creation is done with Ansible, so you can specify any parameters that the Ansible modules support. See

- databases: [community.mysql.mysql\_db](<https://docs.ansible.com/ansible/latest/collections/community/mysql/mysql_db_module.html>)
- users: [community.mysql.mysql\_user](<https://docs.ansible.com/ansible/latest/collections/community/mysql/mysql_user_module.html>)

Example:

```yaml
databases:
  - name: forms
    state: present
  - name: web
    encoding: utf8mb4
    state: present

users:
  - name: "web"
    host: "%"
    # We recommend using already hashed passwords. Please check the notes in the Ansible module for the supported hash modules
    password: "*94BDCEBE19083CINVALIDEFD02F964C7AF3CFC29"
    priv: "web.*:ALL"
    state: present
  - name: "old_user"
    host: "%"
    password: "*94BDCEBE19083CINVALIDEFD02F964CFOOO21319"
    priv: "web.*:ALL"
    state: absent
```

### Backup Cleanup

This Building Blocks ships a cronjob that **deletes** s3 backup files older than a certain age (default `14d`).

By default, PXC-operator does not cleanup backup files stored in s3 ([see](<https://jira.percona.com/browse/K8SPXC-442>)). This might lead to saturation of your S3 quota and other applications using this quota might break.

You can **disable** this cronjob by setting `backupcleanup.enabled=false` in `pxc-db-*.yaml`. See [pxc-operator-extension/values.yaml](<https://code.syseleven.de/syseleven/building-blocks/helmfiles/pxc-operator/-/blob/main/pxc-operator-extension/values.yaml>) for further configuration options and default values.

### Local Storage (Optional, But Strongly Recommended For Performance)

You have to deploy a local storage class provisioner or change `pxc.persistence.storageClass`. A possible local storage class provisioner is [local-path-provisioner](<https://code.syseleven.de/syseleven/building-blocks/helmfiles/local-path-provisioner>).

See [how to use pxc-operator with local storage](<https://documentation.syseleven.de/en/prior-docs/metakube-accelerator/building-blocks/database-cache/pxc-operator/how-to-use-local-storage/index.md>) for more information

### Known Issues

- In our experience PXC is more suitable to run on Kubernetes than a traditional Primary/Secondary-MySQL setup, but PXC comes with [a few limitations](<https://www.percona.com/doc/percona-xtradb-cluster/LATEST/limitation.html>) that you should be aware of.
- The performance of a database often depends on the performance of the underlying storage and PXC is no exception. While our distributed storage provides high availability, the added network latency can lead to a measurable performance impact on the database. As a solution we recommend to use [our local storage nodes](<https://documentation.syseleven.de/en/prior-docs/metakube-accelerator/building-blocks/database-cache/pxc-operator/how-to-use-local-storage/index.md>) instead. This way you get the best performance and the high availability is still provided by the database cluster itself.
- As the upstream Helm chart of pxc-operator [hasn't implemented the support](<https://github.com/percona/percona-helm-charts/pull/7#issuecomment-715310184>) of cluster wide operation, the operator needs to be deployed to each namespace which should have a database cluster.
- Since Upstream does not support adding sidecars to the database pods, we currently do not provide metrics or alerts for MySQL databases within this Building Block. Since Percona is about to add sidecar support soon we will add collecting metrics and provide alerts in a future release. Until then, we recommend to use the community helm chart [prometheus-mysql-exporter](<https://github.com/prometheus-community/helm-charts/tree/main/charts/prometheus-mysql-exporter>) or [PMM](<https://www.percona.com/doc/kubernetes-operator-for-pxc/monitoring.html>). We decided against PMM since we want to integrate the monitoring with the Building Block kube-prometheus-stack to provide a seamless experience.

#### Version 3.40.0 - wrong nomenclature when creating clusterSecretName

Version 3.40.0 has a mismatch on the pxc.SecretName, which is used to distribute the credentials on the cluster deployment. This is **fixed on version 3.40.1**. As a workaround on version 3.40.0, you can specify the common name via the `values-pxc-db.yaml` or corresponding environment-files by adding the following values:

```yaml
pxc:
  clusterSecretName: pxc-db
```

### FAQ

#### Backup fails with 'AccessDenied' error

Please check if this bucket already exists. Object Storage buckets need to be unique within a region.

### Monitoring

#### Additional Alert-Rules

- None

#### Additional Grafana dashboards

- None

### Scaling Setup

This building block consists of multiple components. Each of the components can and must be scaled individually.

### PXC-Operator

- Scaling replicas is not supported
- Requests/limits for CPU/memory can be adjusted

### PXC Cluster

- Replicas should always be set to a 2N+1
- Requests/limits for CPU/memory can be adjusted
- When setting CPU/memory limits, also adjust your my.cnf accordingly (See [upstream](<https://www.percona.com/doc/kubernetes-operator-for-pxc/options.html>) documentation)

### Release-Notes

Please find more infos on release notes and new features [release notes PXC Operator](<https://code.syseleven.de/syseleven/building-blocks/helmfiles/pxc-operator/-/blob/main/RELEASE-NOTES.md>)
