title: Golang RESTful API load testing causing too many database connections
body: <p>I think I am having serious issue managing database connection pool in Golang. I built an RESTful API using Gorilla web toolkit which works great when only few requests are being sent over to the server. But now I started performing load testing using loader.io site. I apologize for the long post, but I wanted to give you the full picture.</p>
  
  <p>Before going further, here are some info on the server running the API and MySQL:
  Dedicated Hosting Linux
  8GB RAM
  Go version 1.1.1
  Database connectivity using go-sql-driver
  MySQL 5.1</p>
  
  <p>Using loader.io I can send 1000 GET requests/15 seconds without problems. But when I send 1000 POST requests/15 seconds I get lots of errors all of which are ERROR 1040 too many database connections. Many people have reported similar issues online. Note that I am only testing on one specific POST request for now. For this post request I ensured the following (which was also suggested by many others online)</p>
  
  <ol>
  <li><p>I made sure I use not Open and Close *sql.DB for short lived functions. So I created only global variable for the connection pool as you see in the code below, although I am open for suggestion here because I do not like to use global variables.</p></li>
  <li><p>I made sure to use db.Exec when possible and only use db.Query and db.QueryRow when results are expected.</p></li>
  </ol>
  
  <p>Since the above did not solve my problem, I tried to set db.SetMaxIdleConns(1000), which solved the problem for 1000 POST requests/15 seconds. Meaning no more 1040 errors. Then I increased the load to 2000 POST requests/15 seconds and I started getting ERROR 1040 again. I tried to increase the value in db.SetMaxIdleConns() but that did not make a difference.</p>
  
  <p>Here some connection statistics I get from the MySQL database on the number of connections by running SHOW STATUS WHERE <code>variable_name</code> = 'Threads_connected';</p>
  
  <p>For 1000 POST requests/15 seconds: observed #threads_connected ~= 100
  For 2000 POST requests/15 seconds: observed #threads_connected ~= 600</p>
  
  <p>I also increased the maximum connections for MySQL in my.cnf but that did not make a difference. What do you suggest? Does the code look fine? If yes, then it is probably the connections are just limited.</p>
  
  <p>You will find a simplified version of the code below.</p>
  
  <pre><code>var db *sql.DB
  
  func main() {
      db = DbConnect()
      db.SetMaxIdleConns(1000)
  
      http.Handle("/", r)
      err := http.ListenAndServe(fmt.Sprintf("%s:%s", API_HOST, API_PORT), nil)
  
      if err != nil {
         fmt.Println(err)
      }
  }
  
  func DbConnect() *sql.DB {
      db, err := sql.Open("mysql", connectionString)
      if err != nil {
          fmt.Printf("Connection error: %s\n", err.Error())
          return nil
      }
      return db
  }
  
  func PostBounce(w http.ResponseWriter, r *http.Request) {
      userId, err := AuthRequest(r)
  
      //error checking
      //ready requesy body and use json.Unmarshal
  
      bounceId, err := CreateBounce(userId, b)
  
      //return HTTP status code here
  }
  
  func AuthRequest(r *http.Request) (id int, err error) {
      //parse header and get username and password
  
      query := "SELECT Id FROM Users WHERE Username=? AND Password=PASSWORD(?)"
      err = db.QueryRow(query, username, password).Scan(&amp;id)
  
      //error checking and return
  }
  
  func CreateBounce(userId int, bounce NewBounce) (bounceId int64, err error) {
      //initialize some variables
      query := "INSERT INTO Bounces (.....) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
      result, err := db.Exec(query, ......)
  
      //error checking
  
      bounceId,_ = result.LastInsertId()
  
      //return 
  }
  </code></pre>