Docker odk mysql optimize db settings

Your Docker Compose file looks mostly correct, but there are a few minor adjustments and suggestions I can offer:

1. The version is correct ('3').

2. For the web service:

   • The configuration looks good.
   • You might want to uncomment the ./conf:/usr/local/tomcat/conf volume if you need to customize Tomcat configuration.

3. For the db service:

   • The command section is using some very large values for memory settings. Make sure your host system has enough RAM to support these settings.
   • The mem_limit and mem_reservation settings are quite high (32G and 24G respectively). Ensure your system can handle this much memory allocation.
   • Consider adding MYSQL_USER and MYSQL_PASSWORD environment variables if you want to create a specific user for the database.

4. The networks section is correctly defined.

Here's a slightly modified version with some suggestions:

version: '3'
services:
  web:
    container_name: odkaggr_tomcat
    build:
      context: ./.docker
      dockerfile: Dockerfile
    ports:
      - "8081:8080"
    volumes:
      - ./webapps:/usr/local/tomcat/webapps
      # - ./conf:/usr/local/tomcat/conf
      - ./logs/tomcat:/usr/local/tomcat/logs
    restart: on-failure
    depends_on:
      - db
    networks:
      - odk_aggregate_network

  db:
    image: "mysql:5.7.20"
    command: >
      --max_allowed_packet=1G
      --innodb_buffer_pool_size=4G
      --innodb_buffer_pool_instances=4
      --innodb_flush_log_at_trx_commit=2
      --innodb_flush_method=O_DIRECT
      --innodb_file_per_table=1
      --lower_case_table_names=1
      --sql_mode=""
      --general_log=1
      --general_log_file=/var/log/mysql/general.log
    container_name: odkaggr_mysql
    environment:
      - MYSQL_ROOT_PASSWORD=
      - MYSQL_DATABASE=omm_odk_v2
      - MYSQL_USER=odk_user
      - MYSQL_PASSWORD=odk_password
    volumes:
      - ./data:/var/lib/mysql
      - ./logs/mysql:/var/log/mysql
    restart: always
    ports:
      - 3307:3306
    networks:
      - odk_aggregate_network
    mem_limit: 8G
    mem_reservation: 6G

networks:
  odk_aggregate_network:
    driver: bridge
    name: odk_aggregate_network

Certainly! I'll keep the mem_limit and mem_reservation as they were in your original configuration, and adjust the InnoDB buffer pool settings to better align with these memory limits. Here's the updated Docker Compose file:

version: '3'
services:
  web:
    container_name: odkaggr_tomcat
    build:
      context: ./.docker
      dockerfile: Dockerfile
    ports:
      - "8081:8080"
    volumes:
      - ./webapps:/usr/local/tomcat/webapps
      # - ./conf:/usr/local/tomcat/conf
      - ./logs/tomcat:/usr/local/tomcat/logs
    restart: on-failure
    depends_on:
      - db
    networks:
      - odk_aggregate_network

  db:
    image: "mysql:5.7.20"
    command: >
      --max_allowed_packet=1G
      --innodb_buffer_pool_size=24G
      --innodb_buffer_pool_chunk_size=2G
      --innodb_buffer_pool_instances=12
      --innodb_flush_log_at_trx_commit=2
      --innodb_flush_method=O_DIRECT
      --innodb_file_per_table=1
      --lower_case_table_names=1
      --sql_mode=""
      --general_log=1
      --general_log_file=/var/log/mysql/general.log
    container_name: odkaggr_mysql
    environment:
      - MYSQL_ROOT_PASSWORD=
      - MYSQL_DATABASE=omm_odk_v2
    volumes:
      - ./data:/var/lib/mysql
      - ./logs/mysql:/var/log/mysql
    restart: always
    ports:
      - 3307:3306
    networks:
      - odk_aggregate_network
    mem_limit: 32G
    mem_reservation: 24G

networks:
  odk_aggregate_network:
    driver: bridge
    name: odk_aggregate_network

To scale this setup for 300 concurrent users with high-frequency data submissions and report generation, you'll need to make several adjustments to your architecture and configuration. Here's a comprehensive approach:

1. Load Balancing:

   • Implement a load balancer (e.g., Nginx, HAProxy) in front of multiple Tomcat instances.
   • Update the Docker Compose file to include a load balancer service.

2. Scaling Web Tier:

   • Use Docker Swarm or Kubernetes for orchestration to easily scale the web tier.
   • Modify the web service to allow multiple replicas.

3. Database Optimization:

   • Consider splitting read and write operations using MySQL replication.
   • Implement a caching layer (e.g., Redis) for frequently accessed data.

4. Monitoring and Logging:

   • Add services for monitoring (e.g., Prometheus) and log aggregation (e.g., ELK stack).

Here's an updated Docker Compose file incorporating these changes:

version: '3.8'

services:
  load_balancer:
    image: nginx:latest
    ports:
      - "80:80"
    volumes:
      - ./nginx.conf:/etc/nginx/nginx.conf:ro
    depends_on:
      - web
    deploy:
      replicas: 1

  web:
    build:
      context: ./.docker
      dockerfile: Dockerfile
    volumes:
      - ./webapps:/usr/local/tomcat/webapps
      - ./logs/tomcat:/usr/local/tomcat/logs
    deploy:
      replicas: 5
      update_config:
        parallelism: 2
        order: rolling-update
    depends_on:
      - db_master
      - db_slave
      - redis
    environment:
      - JAVA_OPTS=-Xmx4G -Xms4G

  db_master:
    image: "mysql:5.7.20"
    command: >
      --max_allowed_packet=1G
      --innodb_buffer_pool_size=24G
      --innodb_buffer_pool_chunk_size=2G
      --innodb_buffer_pool_instances=12
      --innodb_flush_log_at_trx_commit=2
      --innodb_flush_method=O_DIRECT
      --innodb_file_per_table=1
      --lower_case_table_names=1
      --sql_mode=""
      --server-id=1
      --log-bin=mysql-bin
    environment:
      - MYSQL_ROOT_PASSWORD=
      - MYSQL_DATABASE=omm_odk_v2
    volumes:
      - ./data_master:/var/lib/mysql
      - ./logs/mysql_master:/var/log/mysql
    deploy:
      replicas: 1
      placement:
        constraints: [node.role == manager]

  db_slave:
    image: "mysql:5.7.20"
    command: >
      --max_allowed_packet=1G
      --innodb_buffer_pool_size=24G
      --innodb_buffer_pool_chunk_size=2G
      --innodb_buffer_pool_instances=12
      --innodb_flush_log_at_trx_commit=2
      --innodb_flush_method=O_DIRECT
      --innodb_file_per_table=1
      --lower_case_table_names=1
      --sql_mode=""
      --server-id=2
      --relay-log=mysql-relay-bin
    environment:
      - MYSQL_ROOT_PASSWORD=
      - MYSQL_DATABASE=omm_odk_v2
    volumes:
      - ./data_slave:/var/lib/mysql
      - ./logs/mysql_slave:/var/log/mysql
    deploy:
      replicas: 1

  redis:
    image: "redis:alpine"
    command: redis-server --appendonly yes
    volumes:
      - ./redis_data:/data
    deploy:
      replicas: 1

  prometheus:
    image: prom/prometheus:latest
    volumes:
      - ./prometheus