DB Tuning issues

Products

• STARTER PACK-7
• CA Rapid App Security
• CA API Gateway

Issue/Introduction

The customer has identified some IO issues with the Prod gateways which COULD impact the underlying VMWare host (thus affecting other node/s.
Some waits for commits in the slow query log and an innodb buffer pool with a very small size.

Environment

Env: Gateway 9.3
MySQL 5.7
 

Resolution

For MySQL 5.7, we advised the users to add these settings for improved performance: 

- need to edit /etc/my.cnf file 

innodb_buffer_pool_size=(70% of available memory)M 
max_connections=10000 

For example, if you have 10GB (10000M) of available memory, set: 
innodb_buffer_pool_size=7000M 

Regarding the innodb_log_buffer_size, I think 1M is too low, the default value is 8M for mysql 5.5 or above. 16M for mysql 5.7. 

I have also recommended the below changes for starters Innodb_log_buffer_size=1M change to Innodb_log_buffer_size=10M // Effects throughput and checkpoints meaning that larger transactions won’t need to write the log to disk before a transaction commits.
Innodb_buffer_pool_size=64M change to Innodb_buffer_pool_size=1G // I realise this is an appliance and that the layer 7 java process takes precedence but general recommendation for this is 70-80% of available RAM on the server so 1G seems reasonable as there’s about 12G on the server usually 6G free. The ratio of “pages made young” to “pages made old” suggests that more pages might be found in memory if more could be stored in the cache.