Railway•4w ago

Database too many clients crashes

Given the nature of my software, whenever a user uses it, they must make dozens of requests to the database to incrementally decrease a number (the amount of funds in their account). The result of this is that the database just crashes. I am not super familiar with databases in general btw, just enough to do CRUD Thanks!

15 Replies

Percy•4w ago

Project ID: 7ca80dd7-b76f-4860-8cd5-add39d3a162c

JohnLOP•4w ago

7ca80dd7-b76f-4860-8cd5-add39d3a162c

Brody•4w ago

your app should be using a database pool, not opening a new connection for every query you make to the database

JohnLOP•4w ago

Oh okay thanks. I will look into that. In the meantime, how can I uncrash my database?

Brody•4w ago

well its not crashed, just not accepting any more connections, you can redeploy it to close the connections

JohnLOP•4w ago

@Brody Wait, I'm actually not opening a new connection each time... I don't think. Becuase I open it once and then make it a global variable called db.

package main

import (
    "database/sql"
    //"os"
    "fmt"

    _ "github.com/lib/pq"
)

var db *sql.DB

func InitDB( /*dbname string, password string, host string, port string, user string*/ ) {

    var err error

    //    "dbname="+dbname+" user="+user+" password="+password+" host="+host+" port="+port+" sslmode=disable"

    db, err = sql.Open("postgres", "postgresql://postgres:[email protected]:47665/railway")

    if err != nil {
        fmt.Println("InitDB err")
        panic(err)
    } else {
        fmt.Println("Connected to DB")
    }

}

package main

import (
    "database/sql"
    //"os"
    "fmt"

    _ "github.com/lib/pq"
)

var db *sql.DB

func InitDB( /*dbname string, password string, host string, port string, user string*/ ) {

    var err error

    //    "dbname="+dbname+" user="+user+" password="+password+" host="+host+" port="+port+" sslmode=disable"

    db, err = sql.Open("postgres", "postgresql://postgres:[email protected]:47665/railway")

    if err != nil {
        fmt.Println("InitDB err")
        panic(err)
    } else {
        fmt.Println("Connected to DB")
    }

}

I then use the global db object throughout code

Brody•4w ago

is there anything else connecting to yoour database?

JohnLOP•4w ago

No, only one app ChatGPT said to add

db.SetMaxOpenConns(25) // Maximum number of open connections to the database
    db.SetMaxIdleConns(25) // Maximum number of idle connections in the pool
    db.SetConnMaxLifetime(5 * 60)

db.SetMaxOpenConns(25) // Maximum number of open connections to the database
    db.SetMaxIdleConns(25) // Maximum number of idle connections in the pool
    db.SetConnMaxLifetime(5 * 60)

Do you think this is the solution? Or should have pooling alone fixed the problem?

Brody•4w ago

is that even valid code?

JohnLOP•4w ago

Project code or postgres code? I put ... inside the postgres code, I assume I shouldn't make it public

Brody•4w ago

code for github.com/lib/pq

JohnLOP•4w ago

Oh Good question let me check https://pkg.go.dev/database/sql#DB.SetMaxIdleConns

Brody•4w ago

then cant hurt to try

JohnLOP•4w ago

Okay I'll try Also, is it the constant reading from the database that's the issue or the constant changing of a value? Or it doesn't matter?

Brody•4w ago

it would be too many connections being open to the database, but i honestly dont know why that would happen

Gaming

Programming

Database too many clients crashes