MySQL docker uses too much space

This document describes how to deactivate slow_log and 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 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 needed 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 of the MySQL docker overlay being filled up, you can just follow the section below. This procedure can be executed periodically if needed while the configuration is not completely disabled.

To disable that configuration follow the procedure in Disable slow_log and truncate output file procedure. 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 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 next 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 on 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 timestamp in name and hour precision, maybe you 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

Check that everything is normal in the web UI of the EA Manager.