docker-compose for grafana dashboard 12630

Hi guys,

Hope you are all well !

I do need urgently to install the following dashboard https://grafana.com/grafana/dashboards/12630 and tried to setup a docker-compose file for testing it but with no luck… ^^

I think that I missing the part for creating a database into clickhouse-server for storing mysql log data.

Is there any example available / github repository for making this dashboard to run properly ?

Here is the error message I have for now:

Thanks for any insights or inputs on that.

Cheers,

Luc

Hi Luc,

The dashboard is designed for PMM2 server. Some elements work with metrics provided by exporters but some of them work directly with ClickHouse DB. ClickHouse instance is a part of PMM2 server.

Could you provide more details of your installation?


version: “3.8”

services:

mariadb:

image: mariadb:alpine-${MYSQL_VERSION}

container_name: ${NAMESPACE}-mariadb

build:

  context: .docker/bunkerized-mariadb

  dockerfile: Dockerfile

  args:

    MARIABD_VERSION: ${MYSQL_VERSION}

environment:

  ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}

  USER_DATABASE: ${MYSQL_DATABASE}

  USER_NAME: ${MYSQL_USER}

  USER_PASSWORD: ${MYSQL_PASSWORD}

  USER_PRIVILEGES: ALTER, CREATE, DELETE, DROP, INDEX, INSERT, REFERENCES, SELECT, UPDATE

  SKIP_SHOW_DATABASE: "OFF" # If set to ON, will deny the SHOW DATABASES statement to regular users.

  PASSWORD_LENGTH: ${MYSQL_PASSWORD_LENGTH}

  LOGROTATE_MINSIZE: 10M

  LOGROTATE_MAXAGE: 7

  ROOT_HOST: "%"

ports:

- 3307:3306

# - 80:80

networks:

- internal

- web  

volumes:

- mysql-data:/var/lib/mysql

- mysql-logs:/var/log/mysql

- certs-data:/etc/letsencrypt

- ./shared/config/mysql:/custom.cnf.d

command:

- --slow_query_log=ON

- --slow_query_log_file=/var/log/mysql/slowlog.log

- --long_query_time=1

- --log_slow_verbosity=full    

- --query_response_time_stats=ON

- --performance-schema=ON 

- --innodb_monitor_enable=all

grafana used for graphing mysql data

grafana:

image: grafana/grafana

container_name: ${NAMESPACE}-grafana

ports:

- '3000:3000'

networks:

- internal

- web

depends_on:

- mariadb

- mysql_exporter

- prometheus

- clickhouse

volumes:

- grafana-data:/var/lib/grafana

- ./shared/config/grafana/provisioning/:/etc/grafana/provisioning/

- ./shared/config/grafana/dashboards/:/var/lib/grafana/dashboards/

environment:

  GF_INSTALL_PLUGINS: "percona-percona-app,grafana-piechart-panel,vertamedia-clickhouse-datasource,isaozler-paretochart-panel,digiapulssi-breadcrumb-panel"

  GF_PLUGINS_ALLOW_LOADING_UNSIGNED_PLUGINS: "grafana-piechart-panel,vertamedia-clickhouse-datasource,digiapulssi-breadcrumb-panel"

  # GF_SECURITY_ADMIN_USER: "admin"

  # GF_SECURITY_ADMIN_PASSWORD: "admin"

restart: unless-stopped

clickhouse:

image: yandex/clickhouse-server

container_name: ${NAMESPACE}-clickhouse

networks:

- internal

# ports:

# - 8123:8123

# - "9000:9000"

# - "9009:9009"

ulimits:

  nofile:

    soft: 262144

    hard: 262144

volumes:

- clickhouse-data:/var/lib/clickhouse

- ./shared/data/clickhouse:/dump

- ./shared/config/clickhouse/config.xml:/etc/clickhouse-server/config.xml:rw

# - ./shared/config/clickhouse/initdb:/docker-entrypoint-initdb.d

restart: unless-stopped

tool to connect to mysql instance, and allow prometheus to collect mysql stats

mysql_exporter:

image: prom/mysqld-exporter

container_name: ${NAMESPACE}-mysqld-exporter

environment:

  DATA_SOURCE_NAME: root:${MYSQL_ROOT_PASSWORD}@(mariadb:3306)/${MYSQL_DATABASE}?

# ports:

# - '9104:9104'

networks:

- internal

command: 

- --collect.auto_increment.columns 

- --collect.binlog_size 

- --collect.info_schema.processlist 

- --collect.info_schema.query_response_time

- --collect.engine_innodb_status

- --collect.engine_tokudb_status

- --collect.global_status

restart: unless-stopped  

prometheus stores and handles timeseries data for statistics around mysql

prometheus:

image: prom/prometheus

container_name: ${NAMESPACE}-prometheus

networks:

- internal

# ports:

# - 9090:9090

volumes:

- ./shared/config/prometheus:/etc/prometheus

restart: unless-stopped

# Percona monitoring tools

pmm-data:

image: percona/pmm-server:2

container_name: ${NAMESPACE}-pmm-data

hostname: pmm-data

volumes:

- pmm-data:/srv

networks:

- internal

entrypoint: /bin/true

restart: unless-stopped

pmm-server:

image: percona/pmm-server:2

hostname: pmm-server

container_name: ${NAMESPACE}-pmm-server

logging:

  driver: json-file

  options:

    max-size: "10m"

    max-file: "5"

expose:

- "443"

# can't connect to the server trough proxy

ports:

- "4443:443"

networks:

- internal

volumes_from:

- pmm-data

restart: unless-stopped

pmm-client:

image: perconalab/pmm-client:2

hostname: pmm-client-zabbix

container_name: pmm-client

logging:

  driver: json-file

  options:

    max-size: "10m"

    max-file: "5"

depends_on:

- mariadb

- pmm-server

environment:

- PMM_AGENT_CONFIG_FILE=/usr/local/percona/pmm2/pmm-agent.yaml

networks:

- internal

volumes:

- ./shared/config/pmm2/pmm-agent.yaml:/usr/local/percona/pmm2/pmm-agent.yaml

restart: unless-stopped

telegraf:

image: telegraf:1.16-alpine

container_name: ${NAMESPACE}-telegraf

depends_on:

- influxdb

# ports:

# - '8125:8125/udp'

volumes:

- ./shared/config/telegraf/telegraf.conf:/etc/telegraf/telegraf.conf:ro

networks:

- internal

restart: unless-stopped

influxdb:

image: influxdb:1.8-alpine

container_name: ${NAMESPACE}-influxdb

env_file: .env

# ports:

# - '8086:8086'

networks:

- internal

volumes:

- ./shared/data/influxdb/imports:/imports

- influxdb-data:/var/lib/influxdb

restart: unless-stopped

networks:

internal:

driver: bridge

web:

external: true

volumes:

mysql-data:

clickhouse-data:

grafana-data:

influxdb-data:

pmm-data:

mysql-logs:

driver_opts:

  type: none

  o: bind

  device: ${PWD}/shared/logs/mysql

That’s my docker-compose, do you have demo wrapped in a compose file ? That would be really useful for the community ^^

Okay :slight_smile:

I got the trick, you embedded clickhouse into the pmm-server dockerfile image.

But, how do I forward mariadb logs to pmm-server ? with the pmm-agent ?

Do you have an example ?

Thanks any insights or inputs on these questions.

Luc

At first pmm agent has to be connected to a pmm-server instance.

sudo pmm-admin config --server-insecure-tls --server-url=https://admin:admin@<IP Address>:443

Then your mysql service can be added for monitoring.

pmm-admin add mysql --username=“root” --password=

or

pmm-admin add mysql --socket=/var/lib/mysql/mysql.sock

Please use –help flag for getting other available parameters.

Hi @adivinho,

Hope you are all well !

I tried to dockerize it

FROM golang:1-alpine AS builder
MAINTAINER Luc Michalski <lmichalski@evolutive-business.com>


ENV GOOS=linux \
    GO111MODULE=on


WORKDIR /go/src/github.com/percona/pmm-admin
COPY . /go/src/github.com/percona/pmm-admin


# gcc/g++ are required to build SASS libraries for extended version
RUN apk update && \
    apk add --no-cache git ca-certificates make gcc musl-dev glib-dev


RUN make release-gomod


ENV GO111MODULE=off \ 
    CGO_ENABLED=1


RUN git clone --depth=1 https://github.com/lucmichalski/pmm-agent /go/src/github.com/percona/pmm-agent && \
	cd /go/src/github.com/percona/pmm-agent && \
	make release


FROM alpine:3.12 AS runtime 


# Build arguments
ARG TINI_VERSION=${TINI_VERSION:-"v0.19.0"}


# Install tini to /usr/local/sbin
ADD https://github.com/krallin/tini/releases/download/${TINI_VERSION}/tini-muslc-amd64 /usr/local/sbin/tini


# Install runtime dependencies & create runtime user
RUN apk --no-cache --no-progress add ca-certificates musl \
    && chmod +x /usr/local/sbin/tini \
    && mkdir -p /opt \
    && adduser -D percona -h /opt/pmm -s /bin/sh \
    && su percona -c 'cd /opt/pmm; mkdir -p bin data config'


# Switch to user context
USER percona
WORKDIR /opt/pmm


# Copy pmm-admin binary to /opt/pmm/bin
COPY --from=builder /go/src/github.com/percona/pmm-agent/bin/pmm-agent /opt/pmm/bin/pmm-agent
COPY --from=builder /go/src/github.com/percona/pmm-admin/bin/pmm-admin /opt/pmm/bin/pmm-admin
ENV PATH $PATH:/opt/pmm/bin


# Container configuration
VOLUME ["/opt/pmm/data", "/opt/pmm/config"]
ENTRYPOINT ["tini", "-g", "--"]
CMD ["pmm-agent"]


and to create a service in my docker-compose like
  pmm-server:
    image: percona/pmm-server:2
    hostname: pmm-server
    container_name: ${NAMESPACE}-pmm-server
    logging:
      driver: json-file
      options:
        max-size: "10m"
        max-file: "5"
    expose:
    - "443"
    - "8080"
    # can't connect to the server trough proxy
    ports:
    - "8080:80"
    - "4443:443"
    networks:
    - internal
    depends_on:
    - mariadb
    volumes_from:
    - pmm-data
    restart: unless-stopped

  pmm-admin:
    image: percona/pmm-admin:2
    container_name: ${NAMESPACE}-pmm-admin
    build:
      context: .docker/pmm-admin/
      dockerfile: Dockerfile
    networks:
    - internal
    restart: unless-stopped
    command: pmm-admin config --server-insecure-tls --server-url=https://admin:admin@pmm-server:443
It triggers
eg-para-pmm-admin   | Checking local pmm-agent status...
eg-para-pmm-admin   | pmm-agent is not running. Please re-run `pmm-agent setup` with --config-file flag.

Is there a way to get this working in docker all together ?

I absolutely need to fix that for debugging a huge mysql database with lots of slow queries ^^

Thanks for any insights or inputs on that :slight_smile:

Cheers,

Luc

Hi Luc,

It’s possible to monitor a service without an external pmm agent.

You may add your mysql service as a remote instance.

Thanks,

I managed to add the MariaDB instance :slight_smile:

But, I still have a problem with the list of queries, there is no data to show.

Is there any parameter I should add when launching the mariadb instance or pmm-server ?

Because that’s what I wanna see, the slow and unoptimized sql queries.

Thanks for any precious insights about that.

Cheers,

Luc

It’s required for a service to have the enabled performance schema.

https://www.percona.com/doc/percona-monitoring-and-management/2.x/setting-up/client/mysql.html

Hi @adivinho ,

Made it work :slight_smile: but would be interesting to extract clickhouse from the pmm-server docker image, isn’t it ?

I have a last question for you, how can I get the EXPLAIN Section filled with values ?

What argument should I had when starting my mariadb instance ?

Cheers,

Luc

Hi Luc,

It looks like a bug. Which version of MariaDB do you use?

Hi @adivinho ,

I am using this dockerfile: https://github.com/lucmichalski/bunkerized-mariadb/blob/master/Dockerfile

MARIABD_VERSION=“10.5.6-r0

Cheers,

Luc

I have created a ticket refer the issue.

https://jira.percona.com/browse/PMM-7105

Thanks awesome :slight_smile:

Do you think that you will unpack the pmm-server v2 dockerfile into a docker-compose to get clickhouse or other datastores outstide the image ?

Cheers,

Luc

Hi Luc.

There are no such plans, as far as I know.

What’s a benefit of such scenario? I will pass your idea to PO.

Hi @adivinho ,

Hope you are all well !

For scalability and stability of services like clickhouse, postgresql,… I mean much more in the idea of decoupling internal datastores.

In my case, I have 30 websites to monitor and even more in a soonish future, so the container can start to be big and I am afraid to loose my logging data.

Would be nice to have a docker-compose example showing how pmm-server can use external datasource for internal use.

Cheers,

Luc Michalski

Hi guys,

Hope you are all well !

Do you have any update about the status of the ticket https://jira.percona.com/browse/PMM-7105 ?

I need to take a decision about deploying PMM but this bug is quiet annoying for taking a straight decision.

Can we sort it quickly ? Or at least comment the status of this bug in JIRA with a reasonable deadline ?

Cheers,

Luc Michalski

Hi Luc,

The ticket was discussed today. The issue has been confirmed and the ticket will processed by developers.