100% CPU for pressure testing database

[daiagou]

The same component code exhibited different database CPU usage before and after a framework upgrade. Both scenarios involved load testing with 50 threads. Prior to the upgrade (git revision number '90a2a82a' by David E Jones [email protected] on 2021/2/3 at 19:07), the database's 2 CPU cores were at less than 50% utilization. However, after the upgrade (git revision number 'df5e4cc0' by Jens Hardings [email protected] on 2023/9/13 at 05:17), the database's 4 CPU cores were still able to reach 100% utilization.

After investigation, we made modifications to 'framework/src/main/groovy/org/moqui/impl/context/UserFacadeImpl.groovy.' In two places related to userAccount queries, we changed 'useCache(false)' to 'useCache(true),' which resulted in a decrease in CPU usage.

EntityCondition usernameCond = eci.entityFacade.getConditionFactory()
                        .makeCondition("username", EntityCondition.ComparisonOperator.EQUALS, username)
                userAccount = eci.getEntity().find("moqui.security.UserAccount")
                        .condition(usernameCond).useCache(true).disableAuthz().one()

EntityCondition usernameCond = ufi.eci.entityFacade.getConditionFactory()
                        .makeCondition("username", EntityCondition.ComparisonOperator.EQUALS, username)
                ua = (EntityValueBase) ufi.eci.getEntity().find("moqui.security.UserAccount")
                        .condition(usernameCond).useCache(true).disableAuthz().one()

[jonesde]

In general caching UserAccount data is an anti-pattern because of the general rule for caching where only records that will be read MANY times more than they are written, and are of general use across users and transactions, should be cached. If your real-world use case is to have hundreds of thousands of logins for the same user in a short amount of time, and there are not very many users, then caching UserAccount makes sense... otherwise it does not!

In other words, the question is how much does your test case represent the real workload on your system? It is easy to over-optimize a single artificial test case that either has no benefit for real world workloads, or that actually uses more memory or slows things down.

[daiagou]

Thank you very much for your response and excellent suggestions. However, I still have some questions that I need your help in addressing. Let's consider the server-to-server mode. We are using Moqui, providing an API where the client's server calls our server's interface and needs to provide an API key for identity verification.

In this scenario, the client's server may make concurrent calls to our interfaces multiple times within a minute, such as querying, placing orders, canceling orders, and so on. This leads to repeated identity verification, and since user caching is not enabled, the CPU usage of the database spikes.

Do you have any good suggestions for dealing with this situation of repeated identity verification in a server-to-server context?

[jonesde]

One more thought about database load in this case (also included in an email reply to you and some others you work with):

On the performance side of things with a login for every request: usually the logic being run will be substantial enough that the login processing, especially a single lookup of a record by an indexed field, will require very little work compared to the rest of the request. If the column for the username in the database table for UserAccount is not indexed then you'll have a problem, that would cause it to do a table scan for each find query. If your database server is seeing high load from something as simple as this then I would definitely recommend checking to see if the USERNAME column on the USER_ACCOUNT table has an index on it (it should have a unique index on it, that is how it is setup OOTB).

[daiagou]

Thank you for your response. I have checked the user_account table, and indeed, the username field in this table is a unique index. This is also evident in the code:

framework/entity/SecurityEntities.xml:

<index name="USERACCT_USERNAME" unique="true"><index-field name="username"/></index>

So I think it may not be caused by this problem.