Increasing pool size in Grails taking no effect

Question

I am load testing a Grails 4.0.10 app using JMeter. The app is connected to a MySQL 8 database. The JDK I am using is JDK 8.

I am testing registration feature simulating 2000 users trying to register at once.

Registration process consist of these steps:

I am first testing with this database and pool size configuration:

production:
    dataSource:
        #            dialect: org.hibernate.dialect.MySQL5InnoDBDialect
        dbCreate: none
        url: jdbc:mysql://localhost:3306/dev2?useUnicode=yes&characterEncoding=UTF-8
        username: root
        password: password


        properties:
            minimumIdle: 5
            maximumPoolSize: 10
            poolName: main-db
            cachePrepStmts: true
            prepStmtCacheSize: 250
            prepStmtCacheSqlLimit: 2048
            useServerPrepStmts: true
            useLocalSessionState: true
            rewriteBatchedStatements: true
            cacheResultSetMetadata: true
            cacheServerConfiguration: true
            elideSetAutoCommits: true
            maintainTimeStats: false

Using this db configuration the number of successful registrations I am getting is 1167.

After that I change the pool size to 10 times:

production:
    dataSource:
        #            dialect: org.hibernate.dialect.MySQL5InnoDBDialect
        dbCreate: none
        url: jdbc:mysql://localhost:3306/dev2?useUnicode=yes&characterEncoding=UTF-8
        username: root
        password: password


        properties:
            minimumIdle: 50
            maximumPoolSize: 100
            poolName: main-db
            cachePrepStmts: true
            prepStmtCacheSize: 250
            prepStmtCacheSqlLimit: 2048
            useServerPrepStmts: true
            useLocalSessionState: true
            rewriteBatchedStatements: true
            cacheResultSetMetadata: true
            cacheServerConfiguration: true
            elideSetAutoCommits: true
            maintainTimeStats: false

With this configuration, the total successful registration I am getting is 1169.

The error shown for the missing registration is this:

Why is the db configuration change not taking effect? Am I doing something wrong?

Update

I am running the Grails app as standalone. I am using the following command on my server:

nohup java -Dgrails.env=prod -Duser.timezone=US/Mountain -jar RoadRace4-0.1.jar &

It looks like your database server has killed some connections due to inactivity, you may need to play with timeout/wait properties in mysql and/or your grails connection pool config. — Mike W, Mar 01 '23 at 05:42
@mike W thanks for the comment. ill check that. could it be the case that the db configuration in application.yml is not taking effect because the db user the app is using doesnt have enough privileges? today i manually set max connections to 300 in mysql and now registrations are all passing. the default max connections i found in my db was 151. — kofhearts, Mar 01 '23 at 09:01
"trying to register at once" -- Since that is "impossible", how long does it take to handle 2000? Note that max_connections=300 implies that it would take an average of 7 connections per slot. — Rick James, Mar 01 '23 at 17:35
And how many "human" steps are there in doing a registration? If you allow 20 seconds per form to fill out, and N forms, the elapsed time is 20*N per registration, divided by the number (300?) that you can handle in _parallel_. — Rick James, Mar 01 '23 at 17:37
A "pool" entry becomes available only after the user exits. So, even 300 may not be enough. Another issue: If each "form" is on a separate page, (separate HTTP request), then each form is another "connection". You need to think through and present the entire dataflow -- then we can discuss whether `max_connections` or pool size needs increasing. — Rick James, Mar 01 '23 at 17:41

Increasing pool size in Grails taking no effect

Update

0 Answers0