It started out with me trying to see how much of a difference there was between prepared statements and non prepared statements. I ended up sitting for 5 hours trying to figure out what was going on. And what I found out makes no sense.
I am using CentOS 7 with supervisord to run Go 1.4.2 in the background as a deamon. I am using MariaDB as the sql database.
The program I am benchmarking is really simple.
I tried to do this in 3 different ways.
1 (the most uncorrect way which for some reason works best):
package main
import (
"database/sql"
"fmt"
"log"
"net/http"
_ "github.com/go-sql-driver/mysql"
"github.com/julienschmidt/httprouter"
)
type User struct {
Id int64
Email string
Username string
}
var DB *sql.DB
func getUser(w http.ResponseWriter, r *http.Request, _ httprouter.Params) {
query := `SELECT id, email, username FROM user WHERE id=3 LIMIT 1;`
rows, _ := DB.Query(query)
var users []*User
for rows.Next() {
u := new(User)
rows.Scan(&u.Id, &u.Email, &u.Username)
users = append(users, u)
}
fmt.Fprint(w, users[0])
}
func main() {
// runtime.GOMAXPROCS(4)
var err error
DB, err = sql.Open("mysql", "root:pass@unix(/var/lib/mysql/mysql.sock)/dbname?charset=utf8")
if err != nil {
log.Fatal(err)
}
if err = DB.Ping(); err != nil {
log.Fatal(err)
}
router := httprouter.New()
router.GET("/api/user", getUser)
log.Fatal(http.ListenAndServe(":80", router))
}
I did not check for any errors here. I did not close any rows and so on.
This method gives great results with wrk:
Running 5s test @ http://127.0.0.1:80/api/user
1 threads and 1000 connections
Thread Stats Avg Stdev Max +/- Stdev
Latency 18.85ms 17.73ms 238.81ms 91.57%
Req/Sec 7.19k 1.58k 8.45k 96.00%
35776 requests in 5.04s, 5.02MB read
Requests/sec: 7096.50
Transfer/sec: 0.99MB
I seem to get a r/s over 7000 consistently. Great result when I am getting stuff from the MySQL database.
In the second method I'll just replace the content inside the getUser function like this:
func getUser(w http.ResponseWriter, r *http.Request, _ httprouter.Params) {
query := `SELECT id, email, username FROM user WHERE id=3 LIMIT 1;`
rows, err := DB.Query(query)
if err != nil {
log.Fatal(err)
}
defer rows.Close()
var users []*User
for rows.Next() {
u := new(User)
err := rows.Scan(&u.Id, &u.Email, &u.Username)
if err != nil {
log.Fatal(err)
}
users = append(users, u)
}
if err = rows.Err(); err != nil {
log.Fatal(err)
}
fmt.Fprint(w, users[0])
}
This is the recommended way of doing a query as I understood: http://go-database-sql.org/retrieving.html
This gives really weird and awful results consistently!
Running 5s test @ http://127.0.0.1:80/api/user
1 threads and 1000 connections
Thread Stats Avg Stdev Max +/- Stdev
Latency 11.80ms 3.68ms 14.40ms 100.00%
Req/Sec 0.00 0.00 0.00 100.00%
2 requests in 5.01s, 294.00B read
Socket errors: connect 0, read 1490, write 159932, timeout 0
Requests/sec: 0.40
Transfer/sec: 58.64B
And only when I decrease the connections amount do I seem to get better results, but not consistently, the results sometimes go all the way down to around 1500 r/s, it makes no sense to me.
The third way I tried is this: (Just doing a simple 1 row query)
func getUser(w http.ResponseWriter, r *http.Request, _ httprouter.Params) {
query := `SELECT id, email, username FROM user WHERE id=3 LIMIT 1;`
user := new(User)
err := DB.QueryRow(query).Scan(&user.Id, &user.Email, &user.Username)
if err != nil {
if err != sql.ErrNoRows {
log.Fatal(err)
}
http.Error(w, http.StatusText(http.StatusNotFound), http.StatusNotFound)
return
}
fmt.Fprint(w, user)
}
Here I get the same awful results, less than 1 request per second.
What is going on? This has been bugging me for hours now. Hope someone has an explanation and a fix to this..
EDIT:
Now I removed all the lines with log.Fatal(err) and replaced them with log.Println(err)
And set
runtime.GOMAXPROCS(4)
And the single query one gives me results like these consistently:
[root@centos7 main]# wrk -t1 -c1000 -d5s http://127.0.0.1:80/api/user
Running 5s test @ http://127.0.0.1:80/api/user
1 threads and 1000 connections
Thread Stats Avg Stdev Max +/- Stdev
Latency 22.24ms 12.11ms 176.58ms 79.83%
Req/Sec 38.01k 4.62k 48.75k 66.67%
188080 requests in 5.04s, 24.09MB read
Non-2xx or 3xx responses: 183505
Requests/sec: 37350.28
Transfer/sec: 4.78MB
But is this not too good? Must be something wrong here?
Another thing, are you not supposed to be using log.Fatal(err) in a production server but only when developing? Since all the examples on how to use the MySQL driver shows examples with log.Fatal(err) after each err when getting results.