Overview

The customer is experiencing an issue with the memory usage of the Log Processor (LGP) continuously increasing. This issue was supposed to be resolved by updating MySQL to versions 5.6.41 or later. However, the problem persists in environments with MySQL version 5.6.51. The customer is concerned about the memory usage exceeding the recommended 80% of the total memory, and is seeking advice on whether to add more physical memory or lower the value of innodb_buffer_pool_size. The customer also wants to understand the rationale behind the recommended settings for MySQL, specifically the innodb_buffer_pool_size, read_buffer_size, and sort_buffer_size.

Solution

1. Confirm that the issue is related to the MySQL version. If the issue persists in environments with MySQL version 5.6.51, further investigation is needed.
2. Provide the requested information for review by the support team. This includes server reboot time, tp_status and service mysql status, output of various OS and MySQL based commands, host and common config files, tp_walkall, the mysql error file, and any observed behaviour of the increase.
3. Consider reducing the innodb_buffer_pool_size to address the issue. The recommended setting for innodb_buffer_pool_size is 80% of physical memory. However, if the MySQL buffer pool is used up to 55G, the LGP server's memory usage will be about 90%. Therefore, consider lowering the value of innodb_buffer_pool_size so that the LGP server's memory usage stays at 80%.
4. The following changes in MySQL config values are recommended: innodb_buffer_pool_size [55G = 40G], read_buffer_size [20M = 8M], sort_buffer_size [20M = 8M].
5. Understand that these changes should not affect the service or degrade performance. Increasing the buffer value does not necessarily improve performance and may actually decrease it. Lowering this value should not affect performance.
6. If further detailed study on these MySQL Configurations is needed, consider initiating a separate project with the Professional Service team to review and adjust the MySQL Configurations for your traffic and capacity requirements.

Summary

To address the issue of excessive memory usage in theLGP, it is recommended to confirm the MySQL version, provide necessary information for review, consider reducing the innodb_buffer_pool_size, and adjust other MySQL config values. These changes should not affect the service or degrade performance. If further detailed study is needed, consider initiating a separate project with the Professional Service team.

FAQ

1. What is the recommended setting for innodb_buffer_pool_size?
   The recommended setting for innodb_buffer_pool_size is 80% of physical memory.
2. Will reducing the innodb_buffer_pool_size affect the service or degrade performance?
   No, reducing the innodb_buffer_pool_size should not affect the service or degrade performance.
3. What should I do if the issue persists after making the recommended changes?
   If the issue persists, consider initiating a separate project with the Professional Service team to review and adjust the MySQL Configurations for your traffic and capacity requirements.