Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

100% CPU for pressure testing database #622

Open
daiagou opened this issue Oct 25, 2023 · 4 comments
Open

100% CPU for pressure testing database #622

daiagou opened this issue Oct 25, 2023 · 4 comments

Comments

@daiagou
Copy link

daiagou commented Oct 25, 2023

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
Copy link
Member

jonesde commented Oct 25, 2023

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
Copy link
Author

daiagou commented Nov 17, 2023

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
Copy link
Member

jonesde commented Nov 21, 2023

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
Copy link
Author

daiagou commented Nov 22, 2023

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Development

No branches or pull requests

2 participants