How can we run queries concurrently, using go routines?

user_18864864 0
Viewed 278

I am using gorm v1 (ORM), go version 1.14 DB connection is created at the start of my app and that DB is being passed throughout the app.

I have a complex & long functionality. Let's say I have 10 sets of queries to run and the order doesn't matter. So, what I did was

go queryset1(DB)
go queryset2(DB)
...
go queryset10(DB)

// here I have a wait, maybe via channel or WaitGroup.

Inside queryset1:

 func queryset1(db *gorm.DB, /*wg or errChannel*/){
      db.Count() // basic count query
      wg.Done() or errChannel <- nil
    }

Now, the problem is I encounter the error :1040 "too many connections" - Mysql.
Why is this happening? Does every go routine create a new connection? If so, is there a way to check this & "live connections" in mysql (Not the show status variables like connection)

How can I concurrently query the DB?

Edit: This guy has the same problem

go-gorm go mysql
1 Answers

The error is not directly related to go-gorm, but to the underlying MySQL configuration and your initial connection configuration. In your code, you can manage the following parameters during your initial connection to the database.

  • maximum open connections (SetMaxOpenConns function)
  • maximum idle connections (SetMaxIdleConns function)
  • maximum timeout for idle connections (SetConnMaxLifetime function)

For more details, check the official docs or this article how to get the maximum performance from your connection configuration.

If you want to prevent a situation where each goroutine uses a separate connection, you can do something like this:

// restrict goroutines to be executed 5 at a time
connCh := make(chan bool, 5)

go queryset1(DB, &wg, connCh)
go queryset2(DB, &wg, connCh)
...
go queryset10(DB, &wg, connCh)

wg.Wait()
close(connCh)

Inside your queryset functions:

func queryset1(db *gorm.DB, wg *sync.WaitGroup, connCh chan bool){
    connCh <- true  
    db.Count() // basic count query
    <-connCh  
    wg.Done() 
}

The connCh will allow the first 5 goroutines to write in it and block the execution of the rest of the goroutines until one of the first 5 goroutines takes the value from the connCh channel. This will prevent the situations where each goroutine will start it's own connection. Some of the connections should be reused, but that also depends on the initial connection configuration.

user_75675790
Related