Layer7 API Management

Hi folks !

My question of the day : which is the optimal configuration for the MySQL database hosting Gateway's conf & policies ?

I am currently "tuning" our API Gateway environment, databases are one of the important element to consider for enhancing global performances.

So what are your recommendations : MyISAM vs InnoDB, cache size, max connections, concurrent thread number, MySQL log management, ...

Please share your configurations and experiences

Thank you

Hi Nicolas_Afonso,

Just out of curiosity, is this question pertaining to a separate MySQL server that a software-only API Gateway connects to, or is this for API Gateway appliances that we provide with everything built-in? The reason I ask is only to provide a note of caution around editing the my.cnf file in an appliance, as it is already quite optimized to how our API Gateway would interact with it on the same node.

Other than that, I look forward to seeing how everyone has tuned their external MySQL servers as I'm always interested in how everyone squeezes out better performance for their own environments. I'm sure there are lots of lessons to be learned from it. It's a great question, Nicolas! :-)

A few tidbits of my own from our appliances are below, which can be referenced for external MySQL servers as well if it is only there to serve the API Gateway. If it serves more than just the API Gateway, then of course it should be expanded as appropriate.

• We use InnoDB in our appliances
• By default, max_connections equals 2625.
• By default, MySQL is set to keep replication log files for 10 days. However a handful of customers definitely run scripts to purge log files every day or so if the files grow to be too large too fast for their environment.
• By default, query_cache_size equals 32M, and query_cache_limit equals 8M.

I should re-iterate that the above notes are just some tidbits from our appliances, and these numbers likely need to be adjusted accordingly for external MySQL servers for software-only versions of the API Gateway. I just wanted to add a bit of information for everyone's reference. Hopefully others can definitely expand on that with their own lessons learned from their own environments. I look forward to reading everyone's advice on performance optimizations for MySQL.

Sincerely,

Dustin Dauncey

Support Engineer, Global Customer Success

Email: API-Support@ca.com

Phone: +1 800 225 5224

Outside of North America - ca.com/us/worldwide.aspx

CA API Management Community: ca.com/talkapi

Dear DustinDauncey,

Many thanks for your kind response.

We are currently using software API Gateway version, and our MySQL instances are located along the Gateways, on the same VMs. We do not use audit capabilities, so our databases are not too solicitated in terms of write operations ().

For such a case, is it not recommended in terms of performances to prefer MyISAM DB engine ?

Looking forward to read your experienced my.cnf confs Community members !

Have a nice day

Hi Nicolas_Afonso,

I am unfortunately not too familiar with MyISAM myself to say if it is better for performance. However, I cannot officially recommend it only because we support InnoDB. Having said that, I suppose it's possible that MyISAM would work and may even work with better performance too if that is the case, and it would really just come down to testing it. We find that although we do not officially support certain third-party applications or certain versions of various SQL servers, etc. it still ends up working a lot of the time. The official supported configurations state that it requires InnoDB. The list of that are noted on this page for the latest version of API Gateway: Software Gateway Specifications - CA API Gateway - 8.4 - CA Technologies Documentation

What I would suggest doing is filing an Idea in the CA API Management Community here to support MyISAM or other SQL engines.

I hope that helps a little bit.

Sincerely,

Dustin Dauncey

Support Engineer, Global Customer Success

Email: API-Support@ca.com

Phone: +1 800 225 5224

Outside of North America - ca.com/us/worldwide.aspx

CA API Management Community: ca.com/talkapi

Hi DustinDauncey,

Thank you for your reply, very informative.

I note all these points and I will see with our future MySQL expert, coming soon in my team.

Then I get back here with my personal experience.

Just a final question, about MySQL version support. In the Gateway Software 8.3 requirement, I can see that MySQL 5.5.42 version is supported. However in our RHEL 6.5 OS, we are currently using 5.1.xx version (5.5.xx versions are not available via official RH repo). Could it become an issue for future support ?

Have a nice day

PS : I let this thread open a few days, hoping that other advanced users share their experiences

Hi Nicolas_Afonso,

Installing MySQL 5.5 is recommended for performance and security reasons, along with supportability of course. In general, we find that many more versions work with our products than what we specify in our documentation. The reason certain versions are listed in the documentation is because it is the one we officially test against during our QA processes and so we know that it works well and meets all expectations and requirements. For example, it could be possible that using 5.1 will be fine but if a certain feature comes out in the API Gateway or is enabled as a future requirement by your organization, it could conceivably require a feature only present in MySQL 5.5 and that is where things may start to become an issue. I don't have one on the top of my head, but it is entirely possible.

For all those reasons (security, performance, supportability), we definitely encourage using versions of servers / applications per the documentation.

I hope that helps clarify that a bit for you. But please let me know if I can answer anything else or if you need any further clarification and I'll be happy to help.

Sincerely,

Dustin Dauncey

Support Engineer, Global Customer Success

Email: API-Support@ca.com

Phone: +1 800 225 5224

Outside of North America - ca.com/us/worldwide.aspx

CA API Management Community: ca.com/talkapi

We run virtual appliance with 8gb RAM, and we have increase these two settings on my.cnf

innodb_additional_mem_pool_size=16M

innodb_buffer_pool_size=512M

You can read up on them and see if that makes sense to you.

We also are a little paranoid with DB connections, so we also tweak the MySQL configuration and the create slave scripts to make use to SSL certificates.

[mysqld]

ssl

ssl-ca=/opt/SecureSpan/mysql-ssl/cacert.pem

ssl-cert=/opt/SecureSpan/mysql-ssl/dc01db.pem

ssl-key=/opt/SecureSpan/mysql-ssl/dc01db.key

ssl-cipher=AES128-SHA:DHE-RSA-AES256-SHA

[client]

ssl

ssl-ca=/opt/SecureSpan/mysql-ssl/cacert.pem

ssl-cert=/opt/SecureSpan/mysql-ssl/dc01db.pem

ssl-key=/opt/SecureSpan/mysql-ssl/dc01db.key

ssl-cipher=AES128-SHA:DHE-RSA-AES256-SHA

And similarly on the JDBC connections we added useSSL=true

Thank you alvaro.reguly for your feedback.

Your message also made me think about RAM quantity to allocate for Gateway process. Currently I have start ssg service with 4GB.

Could you give me some info about your infra ? Do you expose a lot of services, with an intense use of caches ? These points could help me tuning my environments.

Have a nice day