1

mysql connections

I connect to the database in the init function of a controller, like:

db, err = sql.Open("mysql", "user:pass@tcp(<ip>:3306)/<db>")
if err != nil {
    log.Fatal(err)
}

err = db.Ping()
if err != nil {
    log.Fatal(err)
}

Then I prepare some statements (db.Prepare) and finally execute them somewhere in the code, without creating new db connections or anything weird. Just letting go handle the connection pool.

But as you can see in the image, I'm getting a lot of connections and aborted connections which make the server run slow and even crash.

Why is it happening? Also I have around 2000 simultaneous online users which result in about 20 queries per second. I don't think it's much, just in case it mattered.

EDIT: Here's how I run the prepared statements. I have 2 selects, 1 update and 1 insert. Selects are being run like:

err = getStatement.QueryRow(apiKey).Scan(&keyId)
if err != nil {
    res, _ := json.Marshal(RespError{"Statement Error"})
    w.Write(res)
    return
}

Inserts and updates:

insertStatement.Exec(a,b,c)
Ivan
  • 1,801
  • 2
  • 23
  • 40
  • Show the querying code. Are you sure that you always close `*sql.Rows` returned by `Query` and commit/rollback transactions? – Ainar-G Sep 12 '16 at 14:23
  • 3
    You must close your statements and rows when you're done with them by calling [`Stmt.Close()`](https://golang.org/pkg/database/sql/#Stmt.Close) and [`Rows.Close()`](https://golang.org/pkg/database/sql/#Rows.Close). See related questions: [Go sql - prepared statement scope](http://stackoverflow.com/questions/38390999/go-sql-prepared-statement-scope) and [Goroutines blocked connection pool](http://stackoverflow.com/questions/38474071/goroutines-blocked-connection-pool). – icza Sep 12 '16 at 14:23
  • 1
    Added how I call them. I never close any statement, because I keep calling them all the time. So actually I should just have 4 connections, one for each statement right? – Ivan Sep 12 '16 at 14:33
  • @Ivan After some googling, it seems like `Connections` is the total number of connections, and you should actually look at `Threads_connected` which is fairly small. http://stackoverflow.com/a/8080531/1892060 – Ainar-G Sep 12 '16 at 15:29
  • @Ainar-G that's good to know. What about aborted connections and max_used_connections? I even had to increase that value because the MySQL server was blocking connections from the golang server (`Host is blocked because of many connection errors unblock with 'mysqladmin flush-hosts' mysql`) – Ivan Sep 13 '16 at 08:03
  • @Ivan Sorry, I don't know enough about MySQL to answer that. – Ainar-G Sep 13 '16 at 10:28

0 Answers0