MySQL docker uses too much space

[ 1 Notes and considerations ] [ 2 Truncate slow_log.CSV file procedure ] [ 3 Disable slow_log and truncate output file procedure ] [ 4 Disable slow_log roll-back procedure ]

This document describes how to deactivate slow_log and/or truncate output mysqldata/mysql/slow_log.CSV file.

This option is activated by default when the mysql component is installed by devo-ea-deployer as an internal service (all in one host mode). Endpoint Agent 1.1.0 automatically fixes this issue.

Under some circumstances, this log can grow if there are a considerable number of agents enrolled and the system is running for a long period of time without purging any service. It is necessary to change this configuration and recreate the docker container to completely get rid of the issue.

You can check how much space the docker is using by running the following command:

sudo docker system df -v | grep -Pe "(VOLUME NAME)|(deam-internal-services_mysqldata)"

Check how much space the MySQL docker volume is using:

To free space in the MySQL docker overlay after filling up, read on below. This procedure can be executed periodically if needed while the configuration is not completely disabled.

To disable that configuration see the related section below. Since this procedure requires devo-ea-manager services to be stopped, execute it at your convenience.

This configuration will be included in newer versions of EAM.

Notes and considerations

Bear in mind that disabling slow_log procedure implies that all devo-ea-manager services must be stopped during procedure implementation.

sudo: docker-compose: command not found error message. If you see this message when calling docker-compose command, usually it is because the /usr/local/bin path is not set in your PATH environment variable. You can temporarily fix it with the following command:

sudo ln -s /usr/local/bin/docker-compose /usr/bin/

Truncate slow_log.CSV file procedure

If you only need free filesystem space you can truncate the main slow_log file (mysqldata/mysql/slow_log.CSV) without stopping any service.

Run the next command based on a temporary docker container (assuming that the name of docker volume is the default configured by devo-ea-deployer):

sudo docker run --rm -it --entrypoint /bin/bash -v deam-internal-services_mysqldata:/out-data mysql:5.7 -c "echo -n '' > /out-data/mysql/slow_log.CSV"

Disable slow_log and truncate output file procedure

Stop all devo-ea-manager services (only one service is installed in pure all in one host):

sudo systemctl stop devo-ea-manager

Stop mysql service (host marked as service):

cd /srv/deam-internal-services

sudo docker-compose stop mysql

Truncate current slow_log.CSV file (assuming that the name of docker volume is the default configured by devo-ea-deployer):

sudo docker run --rm -it --entrypoint /bin/bash -v deam-internal-services_mysqldata:/out-data mysql:5.7 -c "echo -n '' > /out-data/mysql/slow_log.CSV; echo -n '' > /out-data/mysql/slow_log.CSM"

Make a backup file and deactivate slow_log in mysql service deployed of deam-internal-services (sed version):

cd /srv/deam-internal-services

sudo cp docker-compose.yaml "docker-compose.yaml.$(date --iso=h)"

sudo sed -i 's/--slow_query_log=1/--slow_query_log=0/' docker-compose.yaml

Recreate the mysql container and start mysql service:

cd /srv/deam-internal-services

sudo docker-compose rm -f mysql

sudo docker-compose up -d mysql

Check mysql service status and logs of devo-ea-manager service (press Ctrl+C to stop last command):

cd /srv/deam-internal-services

sudo docker-compose ps

sleep 5

sudo docker-compose logs -f mysql

Start devo-ea-manager service:

sudo systemctl start devo-ea-manager

Check devo-ea-manager service status and logs of devo-ea-manager service (press Ctrl+C to stop last command):

sudo systemctl status devo-ea-manager | cat

sleep 5

sudo journalctl -fu devo-ea-manager

Check in EAM Web UI that hosts are still online and events are ingested in your Devo domain:

To check that slow_log is not growing anymore you can run the next command several times, waiting a few minutes between executions (assuming that the name of docker volume is the default configured by devo-ea-deployer):

cd /srv/deam-internal-services

sudo docker run --rm -it --entrypoint /bin/bash -v deam-internal-services_mysqldata:/out-data mysql:5.7 -c "ls -lh /out-data/mysql/slow_log.CSV /out-data/mysql/slow_log.CSM"

Disable slow_log roll-back procedure

In the unlikely event that something goes wrong, follow the rollback procedure to recover the previous version.

Stop all devo-ea-manager services (if there are any up and running):

sudo systemctl stop devo-ea-manager

Stop mysql service (if it is up and running):

cd /srv/deam-internal-services

sudo docker-compose stop mysql

Recover docker-compose file from the backup file (note that backup file was created with the timestamp precise to the name and hour, you might need to adapt the next command to use the right backup file name):

cd /srv/deam-internal-services

sudo mv "docker-compose.yaml.$(date --iso=h)" docker-compose.yaml

Recreate the mysql container and start mysql service:

cd /srv/deam-internal-services

sudo docker-compose rm -f mysql

sudo docker-compose up -d mysql

Check mysql service status:

cd /srv/deam-internal-services

sudo docker-compose ps

Check that nothing unusual shows in the logs of the MySQL service (press Ctrl+C to stop the command):

cd /srv/deam-internal-services

sudo docker-compose logs -f mysql

Start devo-ea-manager service:

sudo systemctl start devo-ea-manager

Check devo-ea-manager service status and logs of devo-ea-manager service:

sudo systemctl status devo-ea-manager

Check the devo-ea-manager logs and see that nothing unusual is showing up (control+C to stop the command):

sudo journalctl -fu devo-ea-manager