Continuing on my journey of building my first serv...

# squarelibraries

[Colton Idle]

Continuing on my journey of building my first server app with ktor + SQLDelight I finally have a simple table (currently empty) but I have a sqldelight query written to query for results and output the result (if found) though a ktor endpoint. So far so good, but i noticed that network calls take like ~800ms to my paid server (i only mention paid because it should not be slow). I started digging and it looks like 650 ms of my network call is due to a query

val (dbResponse: Config?, duration: Duration) = measureTimedValue {
    database.playerQueries.selectConfig(call.parameters["api"].orEmpty()).executeAsOneOrNull()

}

which is defined as

selectConfig:
SELECT *
FROM config
WHERE api = ?;

My two inclinations were 1) I'm using the wrong db url (my server has internal/external urls) but the hosting service support team told me I'm indeed using the correct url for the db
2) maybe I'm not sharing my db connection? i checked that though. and it is shared. i can also make back to back network calls and they take the same long amount of time.

It's a stretch but not sure what to do from here. my entire server code is essentially just 10 lines at the moment. empty postgres table. server support team says everything looks good but indeed the db query looks slow. Is there any chance there's some SQLDelight/postgres gotcha that queries are just real slow?

[eygraber]

I haven't used SqlDelight with Postgres (yet) but I'd be very surprised if this was caused by something intrinsic to SqlDelight.

When you say the network call is 800ms does that mean RTT, or is that from the time the request hits your endpoint to the time the response is sent out? If the latter, 150ms seems kind of high (especially since it doesn't include the time spent in the db).

[Colton Idle]

The 800ms was RTT. Which led me to investigating my ktor code. Which is dead simple. but the duration al

get("/config/{api}") {
    val (dbResponse: Config?, dur: Duration) = measureTimedValue {
        database.playerQueries.selectConfig(call.parameters["api"].orEmpty()).executeAsOneOrNull()

    }

    println("DURATION: $dur") // This is like 600ms+ every time

    if (dbResponse != null) {
        call.respondText(
            """
        {"apiKey":"found!"}
    """.trimIndent()
        )
    } //else default json
}

[Colton Idle]

So the duration is coming up 600ms for a single query in an empty db. which led me to a whole bunch of other testing. but now im at wits end 😅

maybe theres something else obvious here im missing but this is essentailly a ktor hello world project with SQLdelight added in.

[eygraber]

What kind of hardware is this running on?

[Colton Idle]

CleanShot 2024-07-26 at [e-mail]

[eygraber]

That should be fine. Have you tried running it locally?

[Colton Idle]

running locally and hitting the remote db. or just running the server locally and a db instance locally?

[eygraber]

Server and db locally

[eygraber]

A barebones ktor project for me locally has a RTT of 5ms

fun main() {
  embeddedServer(Netty, port = 8080, host = "0.0.0.0", module = Application::module)
    .start(wait = true)
}

fun Application.module() {
  configureRouting()
}

private fun Application.configureRouting() {
  routing {
    get("/") {
      call.respondText("Hello World!")
    }
  }
}

[Colton Idle]

cool. will try to get a postgres instance running locally!

[eygraber]

I'd suggest using docker or something similar. Makes it much simpler

[Colton Idle]

yeah... never used docker. so a lot of this is trying to learn on the fly.

[eygraber]

Try this. Here's my ktor code

import app.cash.sqldelight.db.QueryResult
import app.cash.sqldelight.db.SqlDriver
import app.cash.sqldelight.driver.jdbc.asJdbcDriver
import com.eygraber.db.EsigDatabase
import com.zaxxer.hikari.HikariConfig
import com.zaxxer.hikari.HikariDataSource
import io.ktor.server.application.Application
import io.ktor.server.engine.embeddedServer
import io.ktor.server.netty.Netty
import io.ktor.server.response.respondText
import io.ktor.server.routing.get
import io.ktor.server.routing.routing

private const val DATABASE_NAME = "esig"

fun main() {
  embeddedServer(Netty, port = 8080, host = "0.0.0.0", module = Application::module)
    .start(wait = true)
}

fun Application.module() {
  configureRouting()
}

private fun Application.configureRouting() {
  routing {
    get("/") {
      db.testQueries.select("").executeAsOneOrNull()
      call.respondText("Hello World!")
    }
  }
}

private val db: EsigDatabase by lazy {
  val driver = hikari().asJdbcDriver().apply {
    init()
  }

  EsigDatabase(driver)
}

private fun hikari(): HikariDataSource {
  val config = HikariConfig().apply {
    jdbcUrl = "jdbc:<postgresql://localhost:5432/$DATABASE_NAME>"
    username = "postgres"
    password = ""
    maximumPoolSize = 3
    isAutoCommit = true
    addDataSourceProperty("cachePrepStmts", "true")
    addDataSourceProperty("prepStmtCacheSize", "250")
    addDataSourceProperty("prepStmtCacheSqlLimit", "2048")
    validate()
  }
  return HikariDataSource(config)
}

private fun SqlDriver.init() {
  fun SqlDriver.getVersion() = executeQuery(
    identifier = null,
    sql = """
      SELECT description
      FROM pg_shdescription 
      JOIN pg_database ON objoid = pg_database.oid
      WHERE datname = '$DATABASE_NAME';
    """.trimIndent(),
    mapper = { cursor ->
      QueryResult.Value(
        if(cursor.next().value) {
          cursor.getString(0)?.removePrefix("version:")?.toLongOrNull() ?: 0L
        }
        else {
          0L
        }
      )
    },
    parameters = 0,
    binders = null
  ).value

  fun SqlDriver.setVersion(version: Long) {
    execute(null, "COMMENT ON DATABASE $DATABASE_NAME IS 'version:$version';", 0, null)
  }

  val schema = EsigDatabase.Schema

  try {
    val databaseFileVersion = getVersion()
    if(databaseFileVersion == 0L) {
      schema.create(this)
      setVersion(1)
      println("Created database")
    }
    else {
      if(schema.version > databaseFileVersion) {
        schema.migrate(this, databaseFileVersion, schema.version)
        setVersion(schema.version)
        println("Migrated database ($databaseFileVersion -> ${schema.version})")
      }
      else {
        println("Opened database (v${schema.version})")
      }
    }
  }
  catch(t: Throwable) {
    throw t
  }
}

then create a file called docker-compose.yml with this content

services:
  db:
    image: postgres
    restart: always
    environment:
      POSTGRES_DB: esig
      POSTGRES_USER: postgres
      POSTGRES_HOST_AUTH_METHOD: trust
    volumes:
      - pgdata:/var/lib/postgresql/data
    ports:
      - "5432:5432"

volumes:
  pgdata:

and run sudo docker compose up in the same directory as that file

[eygraber]

Here's my SqlDelight config

sqldelight {
  databases {
    create("EsigDatabase") {
      packageName = "com.eygraber.db"
      dialect(libs.sqldelight.postgresDialect)
    }
  }
}

[eygraber]

First request takes 150ms (presumably to create the db, etc...) and after that it takes 5ms

[Colton Idle]

😍

Will give this a shot after work. thanks!

[Colton Idle]

Yep. Getting about 6ms when done locally. So I guess it's indeed on my server side. Thank you so much Elizier. Appreciate you teaching me something new!

I'll let you know when I get to the bottom of it, but I'm going to assume its a public vs private db url issue misconfiguartion somewhere

[Colton Idle]

Figured it out! I accidentally had the db created in one region, and the ktor service running in another. so even though i wasn't using my hosting providers recommended way of connecting (via internal only URLS). but the db and service were somehow created in different ones. THANK YOU @eygraber for your help!