Ma77Ball commented on code in PR #4851:
URL: https://github.com/apache/texera/pull/4851#discussion_r3180102536
##########
common/dao/src/main/scala/org/apache/texera/dao/SqlServer.scala:
##########
@@ -19,39 +19,59 @@
package org.apache.texera.dao
+import com.zaxxer.hikari.{HikariConfig, HikariDataSource}
import org.jooq.impl.DSL
import org.jooq.{DSLContext, SQLDialect}
-import org.postgresql.ds.PGSimpleDataSource
/**
* SqlServer class that manages a connection to a PostgreSQL database using
jOOQ.
*
+ * Uses a HikariCP connection pool so that every jOOQ query borrows a
pre-authenticated
+ * connection from the pool rather than opening a new TCP + SCRAM handshake
each time.
+ *
* WARNING: Do not cache the DSLContext returned by `createDSLContext()` in a
val or lazy val.
* During testing, `MockTexeraDB` replaces the SqlServer instance between
test classes.
* A cached DSLContext will hold a stale reference to a dead database
connection from a previous test class,
* causing "Connection refused" errors when tests run together.
* Use `def` to ensure the connection is looked up each time.
*
- * @param url The database connection URL.
- * @param user The username for authenticating with the database.
+ * @param url The JDBC connection URL.
+ * @param user The username for authenticating with the database.
* @param password The password for authenticating with the database.
*/
class SqlServer private (url: String, user: String, password: String) {
val SQL_DIALECT: SQLDialect = SQLDialect.POSTGRES
- private val dataSource: PGSimpleDataSource = new PGSimpleDataSource()
- var context: DSLContext = {
- dataSource.setUrl(url)
- dataSource.setUser(user)
- dataSource.setPassword(password)
- dataSource.setConnectTimeout(5)
- DSL.using(dataSource, SQL_DIALECT)
+
+ private val hikariConfig: HikariConfig = {
+ val cfg = new HikariConfig()
+ cfg.setJdbcUrl(url)
+ cfg.setUsername(user)
+ cfg.setPassword(password)
+ cfg.setPoolName("texera-hikari")
+ cfg.setMaximumPoolSize(10)
+ cfg.setMinimumIdle(2)
+ // How long a caller waits for a connection before throwing (ms)
+ cfg.setConnectionTimeout(30000)
+ // How long an idle connection stays in the pool before being retired (ms)
+ cfg.setIdleTimeout(600000)
+ // Maximum lifetime of any connection in the pool (ms); must be <
PostgreSQL's idle timeout
+ cfg.setMaxLifetime(1800000)
Review Comment:
PostgreSQL has two idle timeouts: idle_in_transaction_session_timeout and
idle_session_timeout. Both are disabled by default (docs), so the 30-min
maxLifetime is safe on stock PG. If either is enabled, set maxLifetime should
be set to a value below the smaller one so HikariCP retires connections before
PG does. [link to PG
docs](https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-IDLE-IN-TRANSACTION-SESSION-TIMEOUT)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
