B4J Question Best configuration for high traffic mysql/pool

[Douglas Farias]

Hi all.
I would like to know what is the best configuration to make b4j/pool/mysql ready to receive many simultaneous accesses/querys (around 500k to 2m day).
I need know the best configuration for this 2 servers.

Server 1
10 x COREIntel Xeon E5 (10 x 2.20 GHz)
256 GB RAM

Server 2
10 x COREIntel Xeon E5 (10 x 2.20 GHz)
60 GB RAM

this is the configuration I am currently using for servers with low traffic.

pool.Initialize("com.mysql.cj.jdbc.Driver", "jdbc:mysql://localhost/website?characterEncoding=utf8&autoReconnect=true&useTimezone=true&serverTimezone=UTC", user, pass)
jo = pool
jo.RunMethod("setMinPoolSize", Array(0))
jo.RunMethod("setMaxPoolSize", Array(5000))
jo.RunMethod("setMaxIdleTime", Array As Object(60))
jo.RunMethod("setMaxStatements", Array As Object(1))
jo.RunMethod("setNumHelperThreads", Array As Object(10))
jo.RunMethod("setCheckoutTimeout", Array As Object(60000))
jo.RunMethod("setMaxIdleTimeExcessConnections", Array As Object(60))

I am closing all the used resultset and connections. (Example)

Private l As List : l.Initialize
Dim con As SQL = Main.pool.GetConnection
Dim rs As ResultSet = con.ExecQuery2("SELECT * ..........", Array As Object(req.GetParameter("....")))
Do While rs.NextRow
l.Add(rs.GetString("...."))
Loop
rs.Close 'HERE
con.Close 'HERE

but even so, doing it right I would have many simultaneous hits and queries.

I've also set up mysql.

SET GLOBAL max_connections = 100000;

I need to have as much stability as possible.
there are no other systems / sites on the vps, so using as much ram would be no problem. the question is how to configure the pool / b4j to take better advantage of the 256 available ram.

thx all.

 

[OliverA]

Since you are using Connector/J, set the test query in 3CP0 to "/* ping */ SELECT 1". According to MySQL's documentation, this will create a light weight query and response. 3CP0 also recommends using the DB's preferred query for such tasks.
Sources:
MySQL documentation: https://dev.mysql.com/doc/connector...genotes-j2ee-concepts-connection-pooling.html
3CP0 documentation: https://www.mchange.com/projects/c3p0/#preferredTestQuery

You may also increase your initial pool size (since memory is not an issue). Since memory resources are not an issue, you could remove setMaxIdleTime (https://www.mchange.com/projects/c3p0/#maxIdleTime). This way connections do not expire and during peak traffic your pool does not have to worry about establishing new connections.

As to actual pool sizing, you may want to watch the video and read the article here: https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing

With so much RAM you may also consider allocating it properly to MySQL. Some ideas:

Best Practices for Configuring Optimal MySQL Memory Usage

Percona's Peter Zaitsev discusses some of the best practices for configuring optimal MySQL memory usage.

www.percona.com

http://mysql.rjweb.org/doc.php/memory

In the end, it's all down to testing, profiling, adjusting, testing, profiling, adjusting, etc....

 

[Diceman]

In case this was not a typo, you should consider NOT using "Select * " and retrieve only the fewest columns necessary from each row.
2 million queries per day is roughly 23 queries per second, so it is doable if the number of rows and columns returned are kept to a minimum.
If this database is primarily reading data from the server then you will get better performance using MyISAM compared to InnoDb.

When I used MySQL almost 20 years ago, I subscribed to the MySQL email list that had many power users that are willing to help. Some of them were running gargantuan servers and know how to tweak the MySQL configuration to achieve the best results. I was fortunate to talk to Monty a couple of times when he was still involved with MySQL along with Heikki Turin (sic) who invented InnoDb. I believe the email lists are gone now, but here are some of the community links that might help.
lists.mysql.com