Skip to content

pubnative/mysqldriver-go

Repository files navigation

mysqldriver-go

Build Status GoDoc

Table of contents

Motivation

There are many MySQL drivers that implement the database/sql interface. However, using this generic interface, especially in the Scan method, requires the storage of many objects in the heap.

Reading a massive number of records from a DB can significantly increase the Garbage Collection (GC) pause-time that can be very sensitive for high-throughput, low-latency applications.

Because of the above and the need for a GC-friendly MySQL driver, we've decided not to follow the database/sql interface and write this driver.

The following Benchmark was run on a MacBook Pro (Retina, 13-inch, Late 2013), 2.8 GHz Intel Core i7, 16 GB 1600 MHz DDR3 using Go 1.5.2:

comparison

➜  benchmarks git:(master) ✗ go run main.go 
mysqldriver: records read 100  HEAP 129  time 722.293µs
go-sql-driver: records read 100  HEAP 335  time 716.416µs
mysqldriver: records read 1000  HEAP 1015  time 633.537µs
go-sql-driver: records read 1000  HEAP 3010  time 798.109µs
mysqldriver: records read 10000  HEAP 10092  time 3.137886ms
go-sql-driver: records read 10000  HEAP 30010  time 3.377241ms

Goal

The main goals of this library are: performance over flexibility, simplicity over complexity. Any new feature shouldn't decrease the performance of the existing code base.

Any improvements to productivity are always welcome. There is no plan to convert this library into an ORM. The plan is to keep it simple and, still keep supporting all of the MySQL features.

Documentation

  1. API Reference
  2. Official MySQL Protocol Documentation

Dependencies

  1. pubnative/mysqlproto-go MySQL protocol implementation

Installation

go get github.com/pubnative/mysqldriver-go

Quick Start

package main

import (
	"fmt"
	"strconv"

	"github.com/pubnative/mysqldriver-go"
)

type Person struct {
	Name    string
	Age     int
	Married bool
}

func main() {
	// initialize DB pool of 10 connections
	db := mysqldriver.NewDB("root@tcp(127.0.0.1:3306)/test", 10)

	// obtain connection from the pool
	conn, err := db.GetConn()
	if err != nil {
		panic(err)
	}

	if _, err := conn.Exec(`CREATE TABLE IF NOT EXISTS people (
        id int NOT NULL AUTO_INCREMENT,
    	name varchar(255),
    	age int,
        married tinyint,
        PRIMARY KEY (id)
    )`); err != nil {
		panic(err)
	}

	for i := 0; i < 10; i++ {
		num := strconv.Itoa(i)
		_, err := conn.Exec(`
            INSERT INTO people(name,age,married) 
            VALUES("name` + num + `",` + num + `,` + strconv.Itoa(i%2) + `)
        `)
		if err != nil {
			panic(err)
		}
	}

	rows, err := conn.Query("SELECT name,age,married FROM people")
	if err != nil {
		panic(err)
	}

	for rows.Next() { // switch cursor to the next unread row
		person := Person{
			Name:    rows.String(),
			Age:     rows.Int(),
			Married: rows.Bool(),
		}
		fmt.Printf("%#v\n", person)
	}

	// always should be checked if there is an error during reading rows
	if err := rows.LastError(); err != nil {
		panic(err)
	}

	// return connection to the pool for further reuse
	if err := db.PutConn(conn); err != nil {
		panic(err)
	}

	if errors := db.Close(); errors != nil { // close the pool and all connections in it
	    for _, err := range errors {
	        _ = err // handle error
        }
	}
}