Layer7 API Management

Expand all | Collapse all

Optimize OTK MySQL database

Jump to Best Answer
  • 1.  Optimize OTK MySQL database

    Posted 08-31-2018 10:45 AM

    Single external MySQL database being used for two Gateway Nodes(Node1, Node2). Observed different database query during load testing. So, is there any monitoring tools available to observe MySQL database transactions in a clustering environment.

    How to optimize and analysis database transactions?


    is there any CA provides monitoring performance tools?


    e.g. insert token is taking 625 ms while load testing.

    query :

    INSERT INTO oauth_token (otk_token_id, token, expiration, client_key, resource_owner, status, created, client_name, scope, custom, client_ident)                                 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) 

    Stack trace error in New Relic


    com.mysql.jdbc.PreparedStatement.executeUpdate (
    …e.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate (
    …amework.jdbc.core.JdbcTemplate$2.doInPreparedStatement (
    …amework.jdbc.core.JdbcTemplate$2.doInPreparedStatement (

    org.springframework.jdbc.core.JdbcTemplate.execute (

    org.springframework.jdbc.core.JdbcTemplate.update (

    org.springframework.jdbc.core.JdbcTemplate.update (
    org.springframework.jdbc.core.JdbcTemplate.update (

    …h.server.jdbc.JdbcQueryingManagerImpl.performJdbcQuery (Unknown Source)

    …h.server.jdbc.JdbcQueryingManagerImpl.performJdbcQuery (Unknown Source)

    …h.server.jdbc.JdbcQueryingManagerImpl.performJdbcQuery (Unknown Source)

    …assertions.jdbcquery.server.ServerJdbcQueryAssertion.a (Unknown Source)

    …jdbcquery.server.ServerJdbcQueryAssertion.checkRequest (Unknown Source)

    …ion.composite.ServerCompositeAssertion.iterateChildren (Unknown Source)

    …cy.assertion.composite.ServerAllAssertion.checkRequest (Unknown Source)

    …ion.composite.ServerCompositeAssertion.iterateChildren (Unknown Source)

    …ertion.composite.ServerOneOrMoreAssertion.checkRequest (Unknown Source)

    …ion.composite.ServerCompositeAssertion.iterateChildren (Unknown Source)

    …cy.assertion.composite.ServerAllAssertion.checkRequest (Unknown Source)

    …ion.composite.ServerCompositeAssertion.iterateChildren (Unknown Source)

    Skipping 60 lines....(:0)
    com.l7tech.server.tomcat.ResponseKillerValve.invoke (Unknown Source)
    com.l7tech.server.tomcat.ConnectionIdValve.invoke (Unknown Source)

    org.apache.catalina.core.StandardEngineValve.invoke (

    org.apache.catalina.connector.CoyoteAdapter.service (

    org.apache.coyote.http11.Http11Processor.process (

    ….http11.Http11Protocol$Http11ConnectionHandler.process (

    …$ (

    java.util.concurrent.ThreadPoolExecutor.runWorker (

    java.util.concurrent.ThreadPoolExecutor$ ( (

  • 2.  Re: Optimize OTK MySQL database
    Best Answer

    Posted 08-31-2018 05:53 PM



    CA provides several load testing and monitoring tools. Specifically, I'd like to call out the following two which I believe will be the best tools for testing APIs specifically:



    The above are not specific to MySQL though. If you're looking to just test and analyze the performance in MySQL, I'm certain there are other tools available to achieve that, but as MySQL is a third-party product my support and familiarity with that will be quite limited.


    Can I ask though, what do you mean by "observed different database query during load testing"? 

  • 3.  Re: Optimize OTK MySQL database

    Posted 11-15-2018 07:17 AM

    Hi Dustin, 


    Support case closed (case number :  01221069 - OTK MySQL db - Best practice for high availability).


    Here following some advices on how to tune up OTK mySQL database. 



    In order to improve the performance, you should have an external dedicated OTK database which is hosted outside of  the gateway server. Once you have implemented OTK MySQL database on the external server, then you can use the document from MySQL forum to scale up your external database.


    Here are 3 MySQL performance tuning settings that you should always look at.  If you do not, you are very likely to run into problems very quickly.


    innodb_buffer_pool_size: this is the #1 setting to look at for any installation using InnoDB. The buffer pool is where data and indexes are cached: having it as large as possible will ensure you use memory and not disks for most read operations. Typical values are 5-6GB (8GB RAM), 20-25GB (32GB RAM), 100-120GB (128GB RAM).


    innodb_log_file_size: this is the size of the redo logs. The redo logs are used to make sure writes are fast and durable and also during crash recovery. Up to MySQL 5.1, it was hard to adjust, as you wanted both large redo logs for good performance and small redo logs for fast crash recovery. Fortunately crash recovery performance has improved a lot since MySQL 5.5 so you can now have good write performance and fast crash recovery. Until MySQL 5.5 the total redo log size was limited to 4GB (the default is to have 2 log files). This has been lifted in MySQL 5.6.


    Starting with innodb_log_file_size = 512M (giving 1GB of redo logs) should give you plenty of room for writes. If you know your application is write-intensive and you are using MySQL 5.6, you can start with innodb_log_file_size = 4G.


    max_connections: if you are often facing the ‘Too many connections’ error, max_connections is too low. It is very frequent that because the application does not close connections to the database correctly, you need much more than the default 151 connections. The main drawback of high values for max_connections (like 1000 or more) is that the server will become unresponsive if for any reason it has to run 1000 or more active transactions. Using a connection pool at the application level or a thread pool at the MySQL level can help here.


    I will discuss with the customer's DBA.


    regards, Roberto

  • 4.  Re: Optimize OTK MySQL database

    Posted 10-26-2018 12:21 PM



    I just open ticket to CA to get some feedback.



    regards, Roberto

  • 5.  Re: Optimize OTK MySQL database

    Posted 09-04-2018 10:10 AM

    Thank you Dauncey for valuable inputs,


    Can I ask though, what do you mean by "observed different database query during load testing"? Answer: As you can see below screen shots

    e.g. MySQL jdbc_connection select: This is related to second consumed database query

  • 6.  Re: Optimize OTK MySQL database

    Posted 10-26-2018 08:14 PM

    Thank you for the update, Roberto, and my apologies for not responding sooner. I would suggest we come back to this after the support case is closed, so that viewers of this thread in the future can find out what the solution was to your questions for your use-case. I'd like to ask the support case number, so that we can make a link for it and I can be sure to update this thread once the support case is closed. If not though, then I encourage you to post an update once it's been resolved to your satisfaction.

  • 7.  Re: Optimize OTK MySQL database

    Posted 10-15-2018 05:06 AM



    I need to size the hardware for a oAuth db (mySQL server).


    The process, as I understand is the following one:


    1) Every time that a new oAuth token is forged a new row is inserted in the oAuth database.

    2) After the token is created, it is retrieved in a  subsequent sql query and stored in the CA API gateway cache.

    In this way the db is not stresssed anymore for token management.


    It seems obviously that insert troughput is very important to achieve high performance on forging new oAuth tokens.


    Qyuestion: Anyone has a basic sizing guide to dimension the hardware and mysqldb ?

    • CA API gateway : Connection pool setup to oAUth db + RAM for the cache
    • MySql server : RAM/CPU sizing ? Database and disk size ? MyISAM vs InnoDB

    best regards, Roberto