MySQL RDS Parameters Configuration

This article explains configuring Amazon RDS MySQL parameters to improve performance, manage binary logs, and optimize memory allocation.

Prerequisites

• Ensure edit access to the Amazon RDS Console.
• Sufficient permissions are required to modify parameter groups before proceeding.

Steps to Modify RDS MySQL Parameters

Step 1: Stop Tomcat Services

• Log in to the application server and stop Tomcat services.
• For Multi-Tomcat and Multi-VM environments, stop all Tomcats:
  • VM1: tomcatui and tomcatjob
  • VM2: tomcatui and tomcatjob

• Log in to the AWS Management Console.
• Navigate to the RDS Console and select the MySQL instance linked to the OvalEdge application.
  
• Click on OvalEdge to view its configuration.
  

Step 3: Identify the Parameter Group

• Check the configured parameter group for RDS MySQL.
• If the default parameter group is in use, create a new parameter group before proceeding. 
  

Step 4: Modify Parameter Values

• Select the parameter group and click Edit.
  
• In the search bar, find 
  • max_allowed_packet and set the value to 1073741824.
    

Note: 

• The max_allowed_packet parameter defines the maximum size (in bytes) for a single packet or query that MySQL can process.
• Increasing this value allows MySQL to handle larger queries without hitting size limits.
  • Group_concat_max_len and set the value to 50000
    

Note: 

• Determines the maximum length (in bytes) for results returned by the GROUP_CONCAT() function.
• Setting it to 50000 allows the output to store up to 50,000 bytes (≈50 KB) of concatenated data.
• Innodb_buffer_pool_size and set the value to 20615843020 (≈19.2 GB)
  
  • Caches frequently accessed data and indexes for improved database performance.
  • For general MySQL RDS instances, allocate 50-70% of total RAM.
    

Adjust this based on your RDS MySQL RAM size:

1. 16 GB RAM: Set up to 9 GB (9663676416 bytes).
2. 32 GB RAM: Set up to 19 GB (20615843020 bytes).

Step 5: Save Changes and Restart MySQL

• Click Save Changes.
  

• MySQL will restart automatically. If not, restart it manually.
  

Note: 

• If a new parameter group was created, attach it to MySQL and restart.

Step 6: Verify and Restart Tomcat

• Wait until RDS MySQL is available.
• Connect to MySQL and verify the applied configurations.
  

Note: 

Start Tomcat service

• For Multi-Tomcat, start tomcatui first, wait 3 minutes, then start tomcatjob.
• Repeat for VM2 after VM1 services are up and running.

Conclusion

Following these steps ensures optimized MySQL performance and smooth OvalEdge application functionality.